Financial Projection
Overview
You are the Assistant Director of Finance in the Accoun2ng department at a subsidiary of the Crazy Eddies Electronic Corporation; Nu?y John’s Appliance World (owned by his brother John).Create a projection to determine the gross margin, operating 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.
Instructions Part 1: To create the worksheet in Figure-1, do the following:
Format the worksheet 2tle in cell A1 to 28-point. Merge and Center across the range: A1:H1. Add a light blue background
Format the worksheet sub2tle in cell A2 to 16-point. Merge and Center across the range: A1:H1. Add a light blue background
Place a thick black border around the range A1:A2
Format the range B3:H3 as follows
Center each month in its cell
Change the font size to 11
Rotate the contents of each cell in the range B3:H3 angle counter clockwise
Enter the following formulas (using absolute cell referencing where appropriate) in column B: (Note: Commission, Marketing, 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.
B5: Calculate the Cost of Goods Sold for December using the formula: Sales x (1 – Margin). Copy the formula across the range C5:G5
B6: Calculate the Gross Margin for December. Copy the formula across the range C6:G6.
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
B10: Calculate the December Commission expense. Copy the formula across the range C10:G10
B11: Calculate the December Marke@ng. Copy the formula across the range C11:G11
B12: Calculate the December Research expenses. Copy the formula across the range C12:G12
B13: Calculate the December Support, General, and Administra@ve. Copy the formula through the range C13:G13
B14: Calculate the Total Expenses for December. Calculate the formula across the range C14:G14
B16: Calculate the Opera@ng Income for December. Copy the formula across the range C16:G16
H4: Calculate the Total Sales for the 6-month period.
H5: Calculate the Total Cost of Goods Sold for the 6-month period.
H6: Calculate the Total Gross Margin for the 6-month period.
H9:H13: Calculate the Total for each Expense Category for the 6-month period.
H14: Calculate the The Total Expenses for the 6-month period.
H16: Calculate the Total Opera@ng Income for the 6-month period.
Ensure that all column widths are wide enough so that ###### is not displaying
B5:H16 format as currency style with no decimal places and with nega2ve numbers enclosed in parentheses.
Add bo?om borders to the ranges B5:H5 and B13:H13.
Name the Sheet1 tab: “6th Month Financial Projec2on”. Change its color to Blue.
Set the worksheet to print in landscape orienta2on, fit to one page.
Add a Header as follows
Leh Header Box: Page #
Middle Header Box: You Name. Apply bold
Right Header: Date and Time
Upload assignment into Brightspace
