Complete the bill of materials by calculating the extended cost of each component and the total materials cost for each brake system. develop a sensitivity analysis to show the impact on total brake system materials costs if the front brake calipers unit cost ranges from $103 to $107 and if the brake pipe unit cost ranges from $27 to $31.

Complete Question:

A hypothetical bill of materials is used in manufacturing and production to show all of the parts and materials required to manufacture a specific item or for the subassembly of a finished product, such as a motorcycle. The information in the bill of materials is useful for determining product costs, coordinating orders, and managing inventory. It can also show how product costs will be affected by price changes in components or raw materials. This project provides you with an opportunity to use spreadsheet software to perform a sensitivity analysis showing the impact of various prices for component parts on the total costs of a dirt bike. The bill of materials for this project has been simplified for instructional purposes.  

Dirt Bikes’s management has asked you to explore the impact of changes in some of its parts components on production costs. Review the spreadsheet file containing bill of materials information for the brake system for Dirt Bikes’s Moto 300 model, which can be found on the Laudon Web site for this chapter. The completed bill of materials contains the description of the component, the identification number of each component, the supplier (source) of the component, the unit cost of each component, the quantity of each component needed to make each finished brake system, the extended cost of each component, and the total materials cost. The extended cost is calculated by multiplying the quantity of each component needed to produce the finished brake system by the unit cost. The prices of components are constantly changing, and you will need to develop a spreadsheet application that can show management the impact of such price changes on the cost to produce each brake system and on total production costs for the Moto 300 model.  

-Complete the bill of materials by calculating the extended cost of each component and the total materials cost for each brake system.  

-Develop a sensitivity analysis to show the impact on total brake system materials costs if the front brake calipers unit cost ranges from $103 to $107 and if the brake pipe unit cost ranges from $27 to $30.  

-The brake system represents 30 percent of the total materials cost for one Moto 300 motorcycle. Use sensitivity analysis again to show the impact of the changes in front brake caliper unit costs and brake pipe unit costs described previously on total materials costs for this motorcycle model.

Answer and explanation:

Check the attached files for well formatted answers

(a)

ComponentComponent No.SourceUnit CostQuantityExtended Cost

Brake cableM0593Nissin27.81127.81

Brake pedalM0546Harrison Billet6.03212.06

Brake padM3203Ressell27.05254.1

Front brake pumpM0959Brembo66.05166.05

Rear brake pumpM4739Brembo54.00154

Front brake caliperM5930Nissin105.201105.2

Rear brake caliperM7942Nissin106.781106.78

Front brake discM3920Ressell143.801143.8

Rear brake discM0588Ressell56.42156.42

Brake pipeM0943Harrison Billet28.52128.52

Brake lever coverM1059Brembo2.6212.62

Total Cost657.36

(b)

We use a two-way data table to show the sensitivity of the total cost with respect to changes in price for the front brake calipers unit and the brake pipe unit.

Brake pipe price

653.64$2727.5$2828.5$2929.5$30

Front brake caliper price$103.0$653.6$654.1$654.6$655.1$655.6$656.1$656.6

$103.5$654.1$654.6$655.1$655.6$656.1$656.6$657.1

$104.0$654.6$655.1$655.6$656.1$656.6$657.1$657.6

$104.5$655.1$655.6$656.1$656.6$657.1$657.6$658.1

$105.0$655.6$656.1$656.6$657.1$657.6$658.1$658.6

$105.5$656.1$656.6$657.1$657.6 $658.1$658.6$659.1

$106.0$656.6$657.1$657.6$658.1$658.6$659.1$659.6

$106.5$657.1$657.6$658.1$658.6$659.1$659.6$660.1

$107.0$657.6$658.1$658.6$659.1$659.6$660.1$660.6

(c)

Just by dividing all elements of the above data table by 030, we will get the sensitivity of the total cost.

Brake pipe price

$27.0$27.5$28.0$28.5$29.0$29.5$30.0

Front brake caliper price$103.0$2,178.80$2,180.47$2,182.13$2,183.80$2,185.47$2,187.13$2,188.80

$103.5$2,180.47$2,182.13$2,183.80$2,185.47$2,187.13$2,188.80$2,190.47

$104.0$2,182.13$2,183.80$2,185.47$2,187.13$2,188.80$2,190.47$2,192.13

$104.5$2,183.80$2,185.47$2,187.13$2,188.80$2,190.47$2,192.13$2,193.80

$105.0$2,185.47$2,187.13$2,188.80$2,190.47$2,192.13$2,193.80$2,195.47

$105.5$2,187.13$2,188.80$2,190.47$2,192.13$2,193.80$2,195.47$2,197.13

$106.0$2,188.80$2,190.47$2,192.13$2,193.80$2,195.47$2,197.13$2,198.80

$106.5$2,190.47$2,192.13$2,193.80$2,195.47$2,197.13$2,198.80$2,200.47

$107.0$2,192.13$2,193.80$2,195.47$2,197.13$2,198.80$2,200.47$2,202.13

answer; no individual buyer or seller has any significant impact on the market price;