[go: up one dir, main page]
Scribd
Upload
26 views2 pages

Excel Functions and Charts

The document outlines a practical exam for an intermediate level Microsoft Excel course, focusing on functions and charts. It provides a scenario for a data analyst at GreenTech Supplies to analyze a sales dataset using various Excel tools, including calculations, VLOOKUP, HLOOKUP, and chart creation. The final task is to save the completed workbook as 'Excel_Sales_Analysis.xlsx'.

Uploaded by

sches771
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
26 views2 pages

Excel Functions and Charts

The document outlines a practical exam for an intermediate level Microsoft Excel course, focusing on functions and charts. It provides a scenario for a data analyst at GreenTech Supplies to analyze a sales dataset using various Excel tools, including calculations, VLOOKUP, HLOOKUP, and chart creation. The final task is to save the completed workbook as 'Excel_Sales_Analysis.xlsx'.

Uploaded by

sches771
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 2

Microsoft Excel Practical Exam - Intermediate Level

Topic: Working with Functions and Charts

Scenario:

You work as a data analyst at GreenTech Supplies. You have been given a sales dataset and are

required to use various Excel functions and tools to summarize and analyze the data.

Instructions:

1. Open Microsoft Excel and create a new workbook. Rename the worksheet to "Sales_Analysis".

2. Enter the following data starting from cell A1:

| Product ID | Product Name | Category | Units Sold | Unit Price | Region | Salesperson |

|------------|--------------|-------------|------------|------------|------------|-------------|

| P001 | Mouse | Accessories | 120 | 7.5 | West | Alice |

| P002 | Keyboard | Accessories | 80 | 15.0 | East | Brian |

| P003 | Monitor | Hardware | 45 | 120.0 | North | Carol |

| P004 | Laptop | Hardware | 30 | 750.0 | South | Daniel |

| P005 | USB Drive | Accessories | 150 | 10.0 | West | Eva |

| P006 | Webcam | Accessories | 60 | 40.0 | North | Brian |

| P007 | Printer | Hardware | 25 | 200.0 | East | Alice |

| P008 | Tablet | Hardware | 40 | 350.0 | South | Felix |

3. Calculate the total sales for each product (Units Sold * Unit Price) in a new column titled "Total

Sales". Use the SUM function to calculate the grand total.


4. In a separate cell, calculate the following:

- Average of Units Sold using AVERAGE

- Maximum Unit Price using MAX

- Minimum Unit Price using MIN

5. Use the IF function to create a new column labeled "Performance":

- If Units Sold > 100, display "High" else display "Standard".

6. Create a VLOOKUP table on a new sheet named "Lookup" with the Product ID and Product

Name.

- Use VLOOKUP to return the Product Name in "Sales_Analysis" sheet using the Product ID.

7. Create a similar HLOOKUP on the "Lookup" sheet:

- Display the Category when a Product ID is entered in a separate cell.

8. Add a column labeled "Rank" to rank products by Total Sales (highest to lowest) using the RANK

function.

9. Use COUNTIF to count how many products were sold in the "Accessories" category.

10. Create two charts:

- A bar chart showing Total Sales per Product Name.

- A pie chart showing sales distribution per Region.

11. Save the workbook as "Excel_Sales_Analysis.xlsx".

You might also like