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
a. Center each month in its cell
b. Change the font size to 11
c. Rotate the contents of each cell in the range B3:H3 angle counter clockwise

5. 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.
a. B5: Calculate the Cost of Goods Sold for December using the formula: Sales x (1 – Margin). Copy the formula across the range C5:G5
b. B6: Calculate the Gross Margin for December. Copy the formula across the range C6:G6.
c. 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
d. B10: Calculate the December Commission expense. Copy the formula across the range C10:G10
e. B11: Calculate the December Marke@ng. Copy the formula across the range C11:G11
f. B12: Calculate the December Research expenses. Copy the formula across the range C12:G12
g. B13: Calculate the December Support, General, and Administra@ve. Copy the formula through the range C13:G13
h. B14: Calculate the Total Expenses for December. Calculate the formula across the range C14:G14
i. B16: Calculate the Opera@ng Income for December. Copy the formula across the range C16:G16
j. H4: Calculate the Total Sales for the 6-month period.
k. H5: Calculate the Total Cost of Goods Sold for the 6-month period.
l. H6: Calculate the Total Gross Margin for the 6-month period.
m. H9:H13: Calculate the Total for each Expense Category for the 6-month period.
n. H14: Calculate the The Total Expenses for the 6-month period.
o. H16: Calculate the Total Opera@ng Income for the 6-month period.
p. Ensure that all column widths are wide enough so that ###### is not displaying

6. B5:H16 format as currency style with no decimal places and with nega2ve numbers enclosed in parentheses.

7. Add bo?om borders to the ranges B5:H5 and B13:H13.

8. Name the Sheet1 tab: “6th Month Financial Projec2on”. Change its color to Blue.

9. Set the worksheet to print in landscape orienta2on, fit to one page.

10. Add a Header as follows
a. Leh Header Box: Page #
b. Middle Header Box: You Name. Apply bold
c. Right Header: Date and Time

11. Upload assignment into Brightspace