Make your own free website on Tripod.com

Home Resume DP1 DP2 DP3 DP4

  Business Application Skills-part 1

 

PROBLEM:  HOW WILL RAW MATERIALS PRICE CHANGES IMPACT THE TOTAL PRODUCTION COSTS FOR THE MOTO 300 MODEL?

 

REQUIRED BUSINESS SKILLS:  MANUFACTURING BILL OF MATERIALS SENSITIVITY ANALYSIS.

 

REQUIRED SOFTWARE SKILLS:  SPREADSHEET FORMULAS, CREATING A TWO-VARIABLE DATA TABLE.

 

PROJECT NAME:  BILL OF MATERIALS HELPS FIND TOTAL COSTS

            Using a bill of materials can aid a company in discovering the total cost required for production.  Dirt Bike’s management can easily recognize the positive or negative change in overall production prices when looking at the completed spreadsheet.  By raising and lowering the prices of just two components in the entire break system and creating a sensitivity analysis, one can see that several new total prices are created.  Using the spreadsheet allows managers to adjust prices of each part and play with the numbers.  During the price adjustments managers may discover new cheaper ways create the finished product.

 

SOLUTION:  AFTER CREATING A SENSITIVITY ANALYSIS THAT DISPLAYS THE IMPACT OF ADJUSTING BRAKE CALIPERS & BREAK PIPE COSTS, MANAGERS CAN EASILY SEE WHICH ADJUSTMENTS WILL SAVE MONEY.  SAVING  MONEY ON PRODUCTION ULTIMATELY RESULTS IN MAXIMIZING PROFITS FOR THE COMPANY.

 

 

 

 

 
 
Fixed Expenses       Fixed Expenses  
Land  $            44,500     Land  $                44,500
Buildings  $          392,500     Buildings  $              392,500
Manufacturing Machinery  $          572,000     Manufacturing Machinery  $              572,000
Office Equipment  $          212,800     Office Equipment  $              212,800
Utilities  $            30,500     Utilities  $                30,500
Insurance  $            80,700     Insurance  $                80,700
Total Fixed Costs  $        1,333,000     Total Fixed Costs  $           1,333,000
Variable Costs       Variable Costs  
Labor  $                  15     Labor  $                      15
Advertising  $                    1     Advertising  $                        1
Shipping & Receiving  $                    5     Shipping & Receiving  $                        5
Variable Costs Per Unit  $                  21     Variable Costs Per Unit  $                      21
Unit Sales Price  $                 100     Unit Sales Price  $                    125
Contribution Margin  $                  79     Contribution Margin  $                    104
Break Even Point  $            16,873     Break Even Point  $                12,817
         
         
    Insurance Cost    
Labor Cost  $            70,000  $                75,000  $          80,000  $                        85,000  $                90,000
 $                              10                15,742                    15,801              15,861                           15,920                   15,980
 $                              12                16,126                    16,187              16,248                           16,309                   16,370
 $                              14                16,529                    16,591              16,654                           16,716                   16,779
 $                              17                17,173                    17,238              17,303                           17,368                   17,432
 $                              20                17,869                    17,936              18,004                           18,072                   18,139
 

The Formula Sheet

    Fixed Expenses  
=44500     Land =44500
=392500     Buildings =B3
=572000     Manufacturing Machinery =B4
=212800     Office Equipment =B5
=30500     Utilities =30500
=80700     Insurance =80700
=B2+B3+B4+B5+B6+B7     Total Fixed Costs =B8
    Variable Costs  
=15     Labor =15
=1     Advertising =1
=5     Shipping & Receiving =5
=B10+B11+B12     Variable Costs Per Unit =F10+F11+F12
=100     Unit Sales Price =125
=B14-B13     Contribution Margin =F14-F13
=B8/B15     Break Even Point =F8/F15
       
       
    Insurance Cost    
=70000 =75000 =80000 =85000 =90000
=SUM(B2:B6,B20)/(B14-(A21+B11+B12)) =SUM(B2:B6,C20)/(B14-(B11+B12+A21)) =SUM(B2:B6,D20)/(B14-(B11+B12+A21)) =SUM(B2:B6,E20)/(B14-(B11+B12+A21)) =SUM(B2:B6,F20)/(B14-(B11+B12+A21))
=SUM(B2:B6,B20)/(B14-(B11+B12+A22)) =SUM(B2:B6,C20)/(B14-(B11+B12+A22)) =SUM(B2:B6,D20)/(B14-(B11+B12+A22)) =SUM(B2:B6,E20)/(B14-(B11+B12+A22)) =SUM(B2:B6,F20)/(B14-(B11+B12+A22))
=SUM(B2:B6,B20)/(B14-(B11+B12+A23)) =SUM(B2:B6,C20)/(B14-(B11+B12+A23)) =SUM(B2:B6,D20)/(B14-(B11+B12+A23)) =SUM(B2:B6,E20)/(B14-(B11+B12+A23)) =SUM(B2:B6,F20)/(B14-(B11+B12+A23))
=SUM(B2:B6,B20)/(B14-(B11+B12+A24)) =SUM(B2:B6,C20)/(B14-(B11+B12+A24)) =SUM(B2:B6,D20)/(B14-(B11+B12+A24)) =SUM(B2:B6,E20)/(B14-(B11+B12+A24)) =SUM(B2:B6,F20)/(B14-(B11+B12+A24))
=SUM(B2:B6,B20)/(B14-(B11+B12+A25)) =SUM(B2:B6,C20)/(B14-(B11+B12+A25)) =SUM(B2:B6,D20)/(B14-(B11+B12+A25)) =SUM(B2:B6,E20)/(B14-(B11+B12+A25)) =SUM(B2:B6,F20)/(B14-(B11+B12+A25))

 

Part 2

Component Component # Source Unit Cost Quantity Extended Cost
Brake cable M0593 Nissin  $                    27.81 1  $             27.81
Brake pedal M0546 Harrison Billet  $                     6.03 2  $             12.06
Brake pad M3203 Russell  $                    27.05 2  $             54.10
Front brake pump M0959 Brembo  $                    66.05 1  $             66.05
Rear brake pump M4739 Brembo  $                    54.00 1  $             54.00
Front brake caliper M5930 Nissin  $                  105.20 1  $           105.20
Rear brake caliper M7942 Nissin  $                  106.78 1  $           106.78
Front brake disc M3920 Russell  $                  143.80 1  $           143.80
Rear brake disc M0588 Russell  $                    56.42 1  $             56.42
Brake pipe M0943 Harrison Billet  $                    28.52 1  $             28.52
Brake lever cover M1059 Brembo  $                     2.62 1  $              2.62
      Total Materials Cost  $           657.36
         
    Front brake caliper    
Brake pipe  $          103.00  $       104.50  $                  106.00  $                  107.00  
 $                                                                    27.00  $          653.64  $       655.14  $                  656.64  $                  657.64  
 $                                                                    28.50  $          655.14  $       656.64  $                  658.14  $                  659.14  
 $                                                                    29.00  $          655.64  $       657.14  $                  658.64  $                  659.64  
 $                                                                    30.00  $          656.64  $       658.14  $                  659.64  $                  660.64  
         
 Impact of Price Changes on Overall Motercycle Cost           
         
Brake pipe  $          103.00  $       104.50  $                  106.00  $                  107.00  
 $                                                                    27.00  $          653.64  $       655.14  $                  656.64  $                  657.64  
 $                                                                    28.50  $          655.14  $       656.64  $                  658.14  $                  659.14  
 $                                                                    29.00  $          655.64  $       657.14  $                  658.64  $                  659.64  
 $                                                                    30.00  $          656.64  $       658.14  $                  659.64  $                  660.64  
         
Positive Change in Total Materials Cost =          
Negative Change in Total Materials Cost =           

Formula Sheet

Bill of Materials:  Moto 300 Brake System          
         
         
Component Component # Source Unit Cost Quantity Extended Cost
Brake cable M0593 Nissin 27.81 1 =D5*E5
Brake pedal M0546 Harrison Billet 6.03 2 =D6*E6
Brake pad M3203 Russell 27.05 2 =D7*E7
Front brake pump M0959 Brembo 66.05 1 =D8*E8
Rear brake pump M4739 Brembo 54 1 =D9*E9
Front brake caliper M5930 Nissin 105.2 1 =D10*E10
Rear brake caliper M7942 Nissin 106.78 1 =D11*E11
Front brake disc M3920 Russell 143.8 1 =D12*E12
Rear brake disc M0588 Russell 56.42 1 =D13*E13
Brake pipe M0943 Harrison Billet 28.52 1 =D14*E14
Brake lever cover M1059 Brembo 2.62 1 =D15*E15
      Total Materials Cost =SUM(F5:F15)
         
    =A10    
=A14 =103 =104.5 =106 107  
=27 =SUM(F5:F9,B19,F11:F13,A20,F15) =SUM(F5:F9,C19,F11:F13,A20,F15) =SUM(F5:F9,D19,F11:F13,A20,F15) =SUM(F5:F9,E19,F11:F13,A20,F15)  
=28.5 =SUM(F5:F9,B19,F11:F13,A21,F15) =SUM(F5:F9,F11:F13,F15,C19,A21) =SUM(F5:F9,D19,F11:F13,A21,F15) =SUM(F5:F9,E19,F11:F13,A21,F15)  
=29 =SUM(F5:F9,B19,F11:F13,A22,F15) =SUM(F5:F9,F11:F13,F15,C19,A22) =SUM(F5:F9,D19,F11:F13,A22,F15) =SUM(F5:F9,E19,F11:F13,A22,F15)  
=30 =SUM(F5:F9,B19,F11:F13,A23,F15) =SUM(F5:F9,F11:F13,F15,C19,A23) =SUM(F5:F9,D19,F11:F13,A23,F15) =SUM(F5:F9,E19,F11:F13,A23,F15)  
         
=A19 =103 =104.5 =106 107  
=27 =B20 =C20 =D20 =E20  
=28.5 =B21 =C21 =D21 =E21  
=29 =B22 =C22 =D22 =E22  
=30 =B23 =C23 =D23 =E23