1.      a worksheet like the attachment that shows Modern Music Shops’ projected monthly sales, cost of goods sold, gross margin, expenses, and operating income for a six-month period

2.      a 3-D Clustered Column Chart that shows the projected contribution of each month’s operating income.

The data supplied by the Finance department includes projections of the monthly sales and expenses that are based on prior years. These numbers have already been entered into the spreadsheet template.

All remaining numbers in the worksheet are determined from these numbers using formulas.

1.      Cost of Goods Sold= Revenue – (Revenue x Margin Assumption)

2.      Gross Margin = Revenue – Cost of Goods Sold

3.      Bonus Expense = $200,000.00 if the Revenue exceeds the Sales Revenue for Bonus Assumption; otherwise Bonus Expense = 0

4.      Commission Expense = Commission Assumption x Revenue

5.      Marketing Expenses = Marketing Assumption x Revenue

6.      Research and Development = Research and Development Assumption x Revenue

7.      Support, General, and Administrative Expense = Support, General, and Administrative Assumption x Revenue

8.      Total Expenses = Sum of all 6 months’ Expenses

9.      Operating Income = Gross Margin – Total Expenses

1.      10 Line charts with all the chart elements removed. Your charts should show the six-months of data for Revenue and items #1-9 listed in the calculation requirements above. (Resize the charts to fit in Column I & J)

2.      3-D Clustered Column chart on a separate sheet to show the contribution of each month’s operating income. A chart title, legend, axis labels should all appear on the chart.