Question

Ski-R-US is a company that would like some help with financial spreadsheet calculations. You have been hired and they have given you the following information: Fixed costs Metal molding machine: $200,000 Milling machine: $150,000 Sander and grinder: $10,000 Presses: $25,000 Silkscreen machine: $50,000 Variable Costs Packaging material: $5.00 Raw material: $100.00 Shipping: $20.00 Price per unit is $400.00 A template has been included with this assignment to help you get started. Perform the analysis requested. You can use the break-even analysis techniques discussed in class (Goal Seek) and any additional concepts you find helpful. A graph showing the fixed costs, total costs, and revenue would be a big bonus for this project. In order to complete the graph, you will need to add another spreadsheet with formulas that calculate the total cost and revenue based on the sales. It is recommended that you use 10 data points on both sides of the break even point to develop the graph. You may work in groups of up to three or four people for this assignment. Make sure you put all group member names on the assignment when it is handed in.

Answer 1

Variable cost per unit
Packaging material	$5
Raw material	$100
Shipping	$20
Total Variable Cost per unit	$125
Contribution = Sales - Variable cost
Sales Price per unit	$400
Variable cost per unit	$125
Contribution per unit	$275
Fixed Costs
Metal molding machine	$200,000
Milling machine	$150,000
Sander and grinder	$10,000
Presses	$25,000
Silkscreen machine	$50,000
Total fixed costs	$435,000
Breakeven point = Fixed costs / Contribution per unit
Fixed costs	$435,000
Contribution per unit	$275
Breakeven point in units	$1,582
Breakeven sales	$632,800
Note
Breakeven point has been rounded off to the nearest unit
Absolute breakeven points = 1,581.82 units

Data for graph showing total revenue, fixed costs and total costs
Units	Revenue (Units *$ 400)	Fixed Costs	Total Costs (Units * $125 + Fixed Costs)
150	$60,000	$435,000	$453,750
300	$120,000	$435,000	$472,500
450	$180,000	$435,000	$491,250
600	$240,000	$435,000	$510,000
750	$300,000	$435,000	$528,750
900	$360,000	$435,000	$547,500
1,050	$420,000	$435,000	$566,250
1,200	$480,000	$435,000	$585,000
1,350	$540,000	$435,000	$603,750
1,500	$600,000	$435,000	$622,500
1,582	$632,800	$435,000	$632,750
1,650	$660,000	$435,000	$641,250
1,800	$720,000	$435,000	$660,000
1,950	$780,000	$435,000	$678,750
2,100	$840,000	$435,000	$697,500
2,250	$900,000	$435,000	$716,250
2,400	$960,000	$435,000	$735,000
2,550	$1,020,000	$435,000	$753,750
2,700	$1,080,000	$435,000	$772,500
2,850	$1,140,000	$435,000	$791,250
3,000	$1,200,000	$435,000	$810,000

BREAKEVEN SALES $1,400,000 $1,200,000 $1,000,000 $800,000 $600,000 $400,000 $200,000 VALUE 500 1,000 1,500 2,500 3,000 3,500 2,000 UNITS SOLD Fixed Costs - -Revenue (Units *$ 400) -Total Costs (Units $125 + Fixed Costs)