Welcome to EssayHotline!

We take care of your tight deadline essay for you! Place your order today and enjoy convenience.

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

 

© 2024 EssayHotline.com. All Rights Reserved. | Disclaimer: for assistance purposes only. These custom papers should be used with proper reference.