Question

Utilize the following format for the income statement: % of sales Revenue $ 607,500 100.0% COGS $ 437,500 72.0% Gross Profit $170,000 28.0% S&GA $ 25,000 4.1% R&D $ 40,000 6.6% Misc Overhead $ 10,000 1.6% Total Opex $ 75,000 12.3% Net Profit $ 95,000 15.6%

Copy resulting income statement in worksheet (2) into a new Microsoft Word document, and label the income statement as “Deliverable 1”. Deliverable 2: Now return to worksheet (1) and reduce the price per unit of all bicycles by 25%. Copy the resulting income statement in worksheet (2) into the same Microsoft Word document, and label the income statement as “Deliverable 2”.

Activity #2 Goal: Produce an Excel spreadsheet that allows your company to model the sales quantity required to break-even, given a specified price per unit and COGS per unit. The break-even point is the sales quantity you must sale in order to have enough revenue to cover all costs and show zero net profit. i.e. you neither lose nor make money. Assume your company produces skateboards.

Contribution Margin = Sales Price per unit – COGS per unit.

Break-Even Point = Total overhead cost / Contribution Margin

1) Create a new Excel worksheet (3) in the same Excel file utilized in activity #1. In this worksheet, create a model that allows you to enter the sales price per unit, COGS per unit and total overhead cost (Opex). Using these values, create formulas that calculate contribution margin and break-even point using the supplied formulas above.

2) Create a new Excel worksheet (4) – In this worksheet, create an income statement, using the same format specified in Activity #1 step 3. Utilize the following:

a. The total sales (revenue) and total COGS should be calculated using the price per unit, the COGS per unit, and break-even point (sales quantity) from worksheet (3).

b. Selling and General Admin (SG&A) expenses of $20,000.

c. Research & Development (R&D) expenses of $10,000.

d. Miscellaneous Overhead expenses of $10,000.

e. Use formulas to calculate Gross Profit, Total Opex and Net Profit and all of the % of sales values.

Deliverable 3: Assume skateboards are sold for $12 per board. The cost for making each board (COGS) is $7. Enter these values in worksheet (3), as well as total overhead cost from item #2 above.

Copy the resulting income statement in worksheet (4) into the same Microsoft Word document used in Activity #1. Label the income statement as “Deliverable 3”.

Deliverable 4: Assume the skateboards continue to be sold at $12 per board. However, the cost of the wheels has increased, and the cost for making each board (COGS) is now $8. Change values in worksheet (3) accordingly.

Copy the resulting income statement in worksheet (4) into the same Microsoft Word document. Label the income statement as “Deliverable 4”.

Remember, if the net profit does not equal zero in the resulting income statements, the break-even point has been calculated incorrectly, or the spreadsheet formulas are incorrect.

Answer 1

		% of sales
Revenue	607500	100%
COGS	437500	72%
Gross Profit	170000	28%
S&GA	25000	4%
R&D	40000	7%
Misc Overhead	10000	2%
Total Opex	75000	12%
Net profit	95000	16%

WorkSheet1		Deliverable 1	Deliverable 1	Deliverable 1	Deliverable 1
			Sales quantity*price per unit		Sales quantity*price per unit		Sales quantity*COGS per unit
	Sales quantity	Price per unit	Total sales price	Price per unit	Total sales price	CoGS	Total COGS
Road	1000	1000	1000000	750	750000	700	700000
Mountain	1500	1250	1875000	937.5	1406250	800	1200000
Tandem	50	2000	100000	1500	75000	900	45000
Electric	750	2500	1875000	1875	1406250	1400	1050000
Hybrid	400	600	240000	450	180000	350	140000
			5090000		3817500		3135000

	Deliverable 1	% sales	Deliverable2	% Sales
Revenue	5090000	100%	3817500	100%
COGS	3135000	62%	3135000	82%
Gross Profit	1955000	38%	682500	18%
S&GA	25000	0%	25000	1%
R&D	40000	1%	40000	1%
Misc Overhead	10000	0%	10000	0%
Total Opex	75000	1%	75000	2%
Net profit	1880000	37%	607500	16%

Deliverable 3
Skateboards
Price per unit	12
COGS	7
Contribution Margin	=Price per unit -COGS
	=12-7
	=5
Breakeven	=total overhead costs/Contributio Margin
	=(S&GA+R&D+Misc Overheads)/contribution Margin
	=(20000+10000+1000)/5
	=40000/5
	8000

		At break-even units	% of sales
Revenue	=12*8000	96000	16%
COGS	=7*8000	56000	9%
Gross Profit		40000	7%
S&GA		20000	3%
R&D		10000	2%
Misc Overhead		10000	2%
Total Opex		40000	7%
Net profit		0	0%

Deliverable 4
Price per unit	12
COGS	8
Contribution Margin	=Price per unit -COGS
	=12-8
	=4
Breakeven	=total overhead costs/Contributio Margin
	=(S&GA+R&D+Misc Overheads)/contribution Margin
	=(20000+10000+1000)/4
	=40000/4
	10000

		At break-even units	% of sales
Revenue	=12*10000	120000	20%
COGS	=8*10000	80000	13%
Gross Profit		40000	7%
S&GA		20000	3%
R&D		10000	2%
Misc Overhead		10000	2%
Total Opex		40000	7%
Net profit		0	0%