Scribd
Upload
42 views1 page

Practical Excel Exercise Question

The document outlines a task to create a spreadsheet for a small business to analyze sales data. It includes steps for setting up the spreadsheet, inputting data, calculating total sales, categorizing sales, creating a summary sheet, generating a pie chart, formatting the spreadsheet, and documenting the use of a nested IF function. The final workbook should be saved as 'Sales_Data_Analysis.xlsx' for submission.

Uploaded by

jerryguo0321
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
42 views1 page

Practical Excel Exercise Question

The document outlines a task to create a spreadsheet for a small business to analyze sales data. It includes steps for setting up the spreadsheet, inputting data, calculating total sales, categorizing sales, creating a summary sheet, generating a pie chart, formatting the spreadsheet, and documenting the use of a nested IF function. The final workbook should be saved as 'Sales_Data_Analysis.xlsx' for submission.

Uploaded by

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

Scenario: You are working for a small business that sells products online.

The business wants


to analyze its sales data to better understand customer purchases and inventory
management. You will create a spreadsheet to manage and analyze this data.

Task:
1. Create a New Spreadsheet (2 marks)
o Open Excel and create a new workbook.
o Name the first sheet "Sales Data".
2. Input Data (6 marks)
o Create the following columns:
 A: Order ID
 B: Customer Name
 C: Product
 D: Quantity Sold
 E: Price per Unit
 F: Total Sales
 G: Sales Category
o Enter at least 10 rows of sample data (1 mark for each column, 1 mark for 10
rows).
3. Calculate Total Sales (2 marks)
o Write a formula in cell F2 to calculate Total Sales (Quantity Sold * Price per
Unit) and drag it down.
4. Determine Sales Category (4 marks)
o Write a nested IF formula in cell G2 to categorize Total Sales into "Low",
"Medium", or "High" and drag it down.
5. Create a Summary (6 marks)
o Create a new sheet named "Summary".
o Summarize total sales for each Sales Category using the SUMIF function (2
marks for each category).
6. Create a Chart (4 marks)
o Create a pie chart on the "Summary" sheet representing the proportion of
total sales from each Sales Category.
7. Formatting (3 marks)
o Ensure the spreadsheet is well-formatted (bold headers, currency formatting,
borders, etc.).
8. Documentation (2 marks)
o Add a comment explaining how you used the nested IF function in the Sales
Category column.
Submission:
 Save your workbook as "Sales_Data_Analysis.xlsx" and submit it according to your
teacher's instructions.

You might also like