Scenario
You are working as a junior accountant for a small company. Your task is to prepare a worksheet that helps your supervisor calculate total costs and profits for different products sold during the month.
⬇ Download assignment (.docx)Data
Enter the following table exactly as shown into a new Excel worksheet.
| Product | Quantity Sold | Cost Price (per item) | Selling Price (per item) | Transport Cost |
|---|---|---|---|---|
| Pens | 45 | 2.5 | 3.75 | 20 |
| Notebooks | 30 | 5 | 7.5 | 15 |
| Markers | 25 | 3 | 4.8 | 10 |
| Pencils | 60 | 1 | 2.2 | 25 |
| Erasers | 40 | 0.75 | 1.8 | 12 |
| Rulers | 35 | 1.5 | 2.6 | 14 |
| Highlighters | 20 | 2.8 | 4.25 | 18 |
| Staplers | 15 | 6 | 9.5 | 22 |
Instructions
- Enter the data exactly as shown above into a new Excel worksheet.
- In a new column, calculate the Total Cost for each product using the formula.
- In another new column, calculate the Total Revenue for each product:
Total Revenue = Quantity Sold * Selling Price - In another new column, calculate the Profit for each product:
Profit = Total Revenue - Total Cost - At the bottom of each newly created column, calculate the overall total for:
- Total Cost
- Total Revenue
- Total Profit
Note: the Total Cost formula is not shown in the original document.