Case Study

QSO322AcmeBicycleCaseStudyDataSheetStudent.xlsx

Home >Business & Finance homework help >Operations Management homework help >Case Study

Student sheet

NOTICE: HOVER OVER CELLS WITH RED INDICATOR SYMBOLS IN UPPER RIGHT CORNER TO READ IMPORTANT COMMENTS
	Production Costs Richard Horne: Question 3					Current Sales Year				Projected Sales			0.07	Warranty Rate Richard Horne: The stated warrant rates below are based on historic data.
Bicycle types	Raw Materials - Metals	Raw Materials - metals - Oursourced	Raw Materials - Components	Selling Price	Richard Horne: This is the profit calculation of materials before adding the shipping costs. That is, just the selling price less the raw materials profit potential. Materials were NOT OUTSOURCED	Dec	Jan	Feb	Mar	Dec	Jan	Feb	Mar		Estm. Annual Sales - this year Richard Horne: This is calculating the average annual sales based on the average of the 4 months for CURRENT SALES times 10 (not 12 because were are smoothing out the jump in sales for December for this estimate)	Estm. Annual Sales - projected sales Richard Horne: This is the sales increase projections based on the the sales increase % shown in cell N2 and the Estm. Annual Sales for current year
Scorpion	$25	$29	$30	$95		750	250	300	275	803 Richard Horne: For this cell, multiply the Dec sales from current year by the projected sales increase. Do this for all Projects Sales cells.	268	321	294	0.03
Princess Lea	$18	$20	$20	$88		825	275	320	300	883	294	342	321	0.01
Robo-Bike	$22	$24	$28	$120		875	300	325	315	936	321	348	337	0.02
															0	0
									Warranty Liability Richard Horne: Q5 Use Sales price * Projected Sales * Warranty Rate


Shipping Costs
Distributors	Shipping cost/per bicycle
Omaha	$25
Denver	$35
Production Capacity per day (considering equipment and planned labor)
	Dec	Jan	Feb	Mar
Metal Shop	900	300	350	325
Components	900	300	350	325
Final Assembly	900	300	350	325
	Cost to order	Carrying cost (% of Unit cost)
Parts Ordering	$12	0.15
Part	Avg Use/day Richard Horne: For B33, use the value from your calculation in P7 and then divide by 360 for average per day	Reorder time from supplier/days	Unit cost	ROP FOR BRAKE PADS Richard Horne: Q13: For E33, use the below cell to calculate the ROP based on the cell values in B33 and C33 and roundup the value.				EOQ FOR BRAKE PADS Richard Horne: Q14 For I34, calculating EOQ for the brake pads.
	Richard Horne: Question 3	Brake pads(takes 4 pads per bicycle)	0 Richard Horne: Must calculate Estm annual sales for Current year in P7 above FIRST. Use 360 days in this case	5	$1.25	0 Richard Horne: Must calculate Estm annual sales for Current year in P7 above FIRST				0 Richard Horne: Must calculate Estm annual sales for Current year in P7 above FIRST
					Richard Horne: This is the profit calculation of materials before adding the shipping costs. That is, just the selling price less the raw materials profit potential. Materials were NOT OUTSOURCED	Richard Horne: Use this cell and the 2 cells below to calculate the % the Raw Materials - Metals are of the selling price for the 3 types of bicycles	Richard Horne: Use this cell and the 2 cells below to calculate the % the Raw Materials - Metals - Outsourced are of the selling price for the 3 types of bicycles. You will need to compared this with the cell to the left, the in-house cost to produce.								Richard Horne: For this cell, multiply the Dec sales from current year by the projected sales increase. Do this for all Projects Sales cells.		Richard Horne: The stated warrant rates below are based on historic data.	Richard Horne: This is calculating the average annual sales based on the average of the 4 months for CURRENT SALES times 10 (not 12 because were are smoothing out the jump in sales for December for this estimate)	Richard Horne: Q5 Use Sales price * Projected Sales * Warranty Rate	Richard Horne: This is the sales increase projections based on the the sales increase % shown in cell N2 and the Estm. Annual Sales for current year	Richard Horne: For B33, use the value from your calculation in P7 and then divide by 360 for average per day	Richard Horne: Must calculate Estm annual sales for Current year in P7 above FIRST. Use 360 days in this case	Avg. pads per year	0 Richard Horne: Must calculate Estm annual sales for Current year in P7 above FIRST	Richard Horne: Q13: For E33, use the below cell to calculate the ROP based on the cell values in B33 and C33 and roundup the value.	Richard Horne: Must calculate Estm annual sales for Current year in P7 above FIRST	EXAMPLE FORMULA FOR ECONOMIC ORDER QUANTITY
								Where Annual carrying cost = the Carrying costs % * unit cost
Components Sets Data							EXAMPLE FORMULA FOR EPQ
Estm. Annual Demand	13000						Anl Demand	10000
Set-up costs for component sets	$22.00						Setup	100
Annual holding cost per set	$3.00						Holding cost	0.5
Production days per year	250						Daily Demand	60
Productions rate per day	300						Daily Production rate	80
							EPQ	4000	=SQRT(((210000100))/(0.5*(1-60/80)))