Calculate the Cost of Goods Sold for December using the formula: Sales x (1 – Margin). Copy the formula across the range C5:G5

Financial Projection

Overview

You are the Assistant Director of Finance in the Accoun2ng department at a subsidiary of the Crazy Eddies Electronic Corpora2on; Nu?y John’s Appliance World (owned by his brother John). You need to create a projec2on to determine the gross margin, opera2ng expenses, and opera2ng income for the six-month period December through April based on projected sales during that 6-month period. The Director of Accoun2ng has provided you with the template as shown in Figure-1.

• In Part 1 you will create the worksheet.
• In Part 2 you will create a 3-D Bar chart and move it to a new sheet from the data.

Instruc@ons Part 1: To create the worksheet in Figure-1, do the following:

1. Format the worksheet 2tle in cell A1 to 28-point. Merge and Center across the range: A1:H1. Add a light blue background
2. Format the worksheet sub2tle in cell A2 to 16-point. Merge and Center across the range: A1:H1. Add a light blue background
3. Place a thick black border around the range A1:A2
4. Format the range B3:H3 as follows
5. Center each month in its cell
6. Change the font size to 11
7. Rotate the contents of each cell in the range B3:H3 angle counter clockwise
8. Enter the following formulas (using absolute cell referencing where appropriate) in column B: (Note: Commission, Marke@ng, Research and Support, General and Administra@ve expenses are calculated as a % of each month’s sales using the values in the What-If-Assump@ons table.
9. B5: Calculate the Cost of Goods Sold for December using the formula: Sales x (1 – Margin). Copy the formula across the range C5:G5
10. B6: Calculate the Gross Margin for December. Copy the formula across the range C6:G6.
11. B9: Insert an IF func2on to calculate a Bonus in any month where sales exceed the sales target for bonus ($23,750,000). Copy the formula across the range C9:G9
12. B10: Calculate the December Commission expense. Copy the formula across the range C10:G10
13. B11: Calculate the December Marke@ng. Copy the formula across the range C11:G11
14. B12: Calculate the December Research expenses. Copy the formula across the range C12:G12
15. B13: Calculate the December Support, General, and Administra@ve. Copy the formula through the range C13:G13
16. B14: Calculate the Total Expenses for December. Calculate the formula across the range C14:G14
17. B16: Calculate the Opera@ng Income for December. Copy the formula across the range C16:G16
18. H4: Calculate the Total Sales for the 6-month period.
19. H5: Calculate the Total Cost of Goods Sold for the 6-month period.
20. H6: Calculate the Total Gross Margin for the 6-month period.
21. H9:H13: Calculate the Total for each Expense Category for the 6-month period.
22. H14: Calculate the The Total Expenses for the 6-month period.
23. H16: Calculate the Total Opera@ng Income for the 6-month period.
24. Ensure that all column widths are wide enough so that ###### is not displaying
25. B5:H16 format as currency style with no decimal places and with nega2ve numbers enclosed in parentheses.
26. Add bo?om borders to the ranges B5:H5 and B13:H13.
27. Name the Sheet1 tab: “6th Month Financial Projec2on”. Change its color to Blue.
28. Set the worksheet to print in landscape orienta2on, fit to one page.
29. Add a Header as follows
30. Leh Header Box: Page #
31. Middle Header Box: You Name. Apply bold
32. Right Header: Date and Time
33. Upload assignment into Brightspace