In this assignment, you will create a spreadsheet for a small home business which is selling three vitamin products: CoQ-10, Iron, and Zinc. The owner wants to look at sales by product for the first quarter of the year and calculate monthly profit on the products. Open a spreadsheet and do the following:
- Merge cells B1 through D1 and type Sales & Profit in all caps. Center this in the merged cells.
- In cell B2, type CoQ-10. In cell C2, type Iron. In cell D2, type Zinc. Center these in their cells and make them bold.
- In A3, type Jan. In A4, type Feb. In A5, type Mar. These should all be bold as well.
- Type in some numbers to represent the sales of each vitamin product for each month. Each number should be different. Make sure they all have a dollar sign. You need to do this by formatting the cells as currency with no decimal places.
- In A7, type Sales and make it bold and in italics.
- Using the SUM function, calculate the total sales for each product. These results should be in cells B7, C7, and D7.
- In A8, type Average Sales in bold and italics.
- Using the average function, find the average sales for each product. These results should be in cells B8, C8, and D8.
- In E2, type Profit in bold and italics and centered in the cell.
- Each product has a different profit margin. The CoQ-10 product has a 10% profit margin. This means if $80 is sold, the profit is 0.10*80 or $8. The profit margin for Iron is 15%, and the profit margin for Zinc is 5% (which is a multiplication factor of 0.05). In E3, calculate the total profit for January from the sales of the three products. You will need to type in the formula rather than use a function (like SUM) to do this. Remember that to multiply, you must use the asterisk symbol (hold shift while hitting the 8 key). Do the same to determine the February and March profits.
- In A9, type Total Profit in bold and italics.
- In E9, determine the total profit. Fill this cell with a different color so that it stands out.
- Save your spreadsheet with the filename LastNameWeek6 and upload it in the assignment area.
Please Login or Register
to Submit the Solution for the Request