Creating Charts in Excel ASSIGNMENT -Modern Music Shops’ Six-Month Financial Projection The needs...

Question

Creating Charts in Excel ASSIGNMENT -Modern Music Shops’ Six-Month Financial Projection The needs are:1. a worksheet like the attachment that shows Modern Music Shops’ projected monthly sales, cost of...

1 answer below »

Creating Charts in Excel

ASSIGNMENT -

Modern Music Shops’ Six-Month Financial Projection

The needs are:

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.

Date Submitted:
November 2nd

Submitted By:
Kristen Marry

Worksheet Title:
Modern Music Shops’ Six-Month Financial Projection

Source of Data:
Use the data file, “Modern Music Shop” (ATTACHED)

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.

Calculations:
The following calculations must be made for each month:

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

Chart Requirements:
The following charts must be made for the data:

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.

archive-h5jf3wug.zip

Answered 2 days After Mar 07, 2023

Bhavani · Accepted Answer

Income  statement
	What-If Assumptions
	Bonus	200000
	Commission	5.75%
	Margin	57.50%
	Marketing	15.00%
	Research and Development	7.50%
	Sales Revenue for Bonus	3500000
	Support, Generl, and Administrative	18.75%
	Modern Music Shops
	Six-Month Financial Projection
		July	August	September	October	November	December	Total
	Revenue	$3,113,612.16	$7,962,235.53	$5,112,268.58	$2,924,627.87	$7,630,534.65	$3,424,270.23	$30,167,549.02
	Cost of Goods Sold	1,323,285.17	3,383,950.10	2,172,714.15	1,242,966.84	3,242,977.23	1,455,314.85	12,821,208.33
	Gross Margin	$1,790,326.99	$4,578,285.43	$2,939,554.43	$1,681,661.03	$4,387,557.42	$1,968,955.38	$17,346,340.69
	Bonus	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
	Commission	179,032.70	457,828.54	293,955.44	168,166.10	438,755.74	196,895.54	1,734,634.07
	Marketing	467,041.82	1,194,335.33	766,840.29	438,694.18	1,144,580.20	513,640.53	4,525,132.35
	Research and Development	233,520.91	597,167.66	383,420.14	219,347.09	572,290.10	256,820.27	2,262,566.18
	Support, General, and Administrative	583,802.28	1,492,919.16	958,550.36	548,367.73	1,430,725.25	642,050.67	5,656,415.44
	Total Expenses	$1,463,397.72	$3,742,250.70	$2,402,766.23	$1,374,575.10	$3,586,351.29	$1,609,407.01	$14,178,748.04
	Operating Income	$326,929.28	$836,034.73	$536,788.20	$307,085.93	$801,206.14	$359,548.37	$3,167,592.65
Revenue	Cost of Goods Sold	Gross Margin	Bonus	Commission	Marketing	Research and Development	Support, General, and Administrative	Total Expenses	Operating Income	3113612.16	1323285.1680000003	1790326.9919999999	0	179032.6992	467041.

Creating Charts in Excel ASSIGNMENT -Modern Music Shops’ Six-Month Financial Projection The needs are:1. a worksheet like the attachment that shows Modern Music Shops’ projected monthly sales, cost of...

Solution

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment