QSO322AcmeBicycleCaseStudyDataSheetStudent.xlsx

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(((2*10000*100))/(0.5*(1-60/80)))