Chat with us, powered by LiveChat

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.

by Osman
Words: 570
Pages: 3
Subject: Finance
Topics: APA format, Assignment help, Crazy Eddies Electronic Corpora2on, Homework help

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