show the excel function for the solution
Chapter 10 - Master It! For this Master it! assignment, refer to the Goodweek Tires, Inc. case at the end of Chapter 6. For your convenience, we have entered the relevant values in the case such as the price, variable cost, etc. on the next page. For this project, answer the following questions: What is the profitability index of the project? What is the IRR of the project? c. What is the NPV of the project? 12 d. At what OEM price would Goodweek Tires be indifferent to accepting the project? Assume the replacement market price is constant At what level of variable costs per unit would Goodweek Tires be indifferent to accepting the project? 2 E F G Master It! Solution $ $ Research and development Test marketing cost Initial equipment cost Equipment salvage value Year 1 depreciation Year 2 depreciation Year 3 depreciation Year 4 depreciation 10,000,000 5,000,000 140,000,000 54,000,000 14.30% 24.50% 17.50% 12.50% OEM market: Price Variable cost Automobile production Growth rate Market share 5,600,000 2.50% 11.00% Replacement market: Chapter 6 Section 6.2 Section 6.3 Section 6.5 Minimum price Master It! Solution 0 30 -50 Formatting Styles Table Styles Clipboard Font Alignment Number D38 Replacement market: Price Variable cost Market sales Growth rate Market share 14,000,000 2.00% 8.00% $ Price increase above inflation VC increase above inflation Marketing and general costs Tax rate Inflation rate Required return Initial NWC NWC percentage of sales 26,000,000 40.00% 3.25% 15.90% 9,000,000 $ 15 36 Nominal price increase Nominal VC Increase Chapter 6 Section 6.2 Section 6.3 Section 6.5 Minimum price Master it Solution Ready OEM: Automobiles sold Tires for automobiles sold SuperTread tires sold Price Replacement market: Total tires sold in market SuperTread tires sold Price Revenue: OEM market Replacement market Total Variable costs: OEM market Chapter 6 Section 6.2 Section 6.3 Section 6.5 Minimum price Master Solution on x Revenue: OEM market Replacement market Total Variable costs: OEM market Replacement market Total Revenue Variable costs Marketing and general costs Depreciation EBT Tax Net Income OCE 70 711 New working capital: Section 6.2 Chapter 6 Section 63 Section 6.3 Minimum price Solution Master Ready Tvbe here to search Filip Formulas Data Review View Home Insert Draw Page Layout -12 - AA BIUS A- Help General 2 Weap Text E2 D 2 B Merge Center - 9 Insert Delete Format Conditional Formats Cell Formatting Table Styles 094 F G Book value of equipment Aftertax salvage value: Market value Taxes Total Yegro Year 2 Year 4 Operating cash flow Capital spending Net working capital Total cash flows NPV IRR Profitability Index Chapter 6 Section 6.2 Section 6.3 Section 6.5 Minimum price Master i Solution Chapter 10 - Master It! For this Master it! assignment, refer to the Goodweek Tires, Inc. case at the end of Chapter 6. For your convenience, we have entered the relevant values in the case such as the price, variable cost, etc. on the next page. For this project, answer the following questions: What is the profitability index of the project? What is the IRR of the project? c. What is the NPV of the project? 12 d. At what OEM price would Goodweek Tires be indifferent to accepting the project? Assume the replacement market price is constant At what level of variable costs per unit would Goodweek Tires be indifferent to accepting the project? 2 E F G Master It! Solution $ $ Research and development Test marketing cost Initial equipment cost Equipment salvage value Year 1 depreciation Year 2 depreciation Year 3 depreciation Year 4 depreciation 10,000,000 5,000,000 140,000,000 54,000,000 14.30% 24.50% 17.50% 12.50% OEM market: Price Variable cost Automobile production Growth rate Market share 5,600,000 2.50% 11.00% Replacement market: Chapter 6 Section 6.2 Section 6.3 Section 6.5 Minimum price Master It! Solution 0 30 -50 Formatting Styles Table Styles Clipboard Font Alignment Number D38 Replacement market: Price Variable cost Market sales Growth rate Market share 14,000,000 2.00% 8.00% $ Price increase above inflation VC increase above inflation Marketing and general costs Tax rate Inflation rate Required return Initial NWC NWC percentage of sales 26,000,000 40.00% 3.25% 15.90% 9,000,000 $ 15 36 Nominal price increase Nominal VC Increase Chapter 6 Section 6.2 Section 6.3 Section 6.5 Minimum price Master it Solution Ready OEM: Automobiles sold Tires for automobiles sold SuperTread tires sold Price Replacement market: Total tires sold in market SuperTread tires sold Price Revenue: OEM market Replacement market Total Variable costs: OEM market Chapter 6 Section 6.2 Section 6.3 Section 6.5 Minimum price Master Solution on x Revenue: OEM market Replacement market Total Variable costs: OEM market Replacement market Total Revenue Variable costs Marketing and general costs Depreciation EBT Tax Net Income OCE 70 711 New working capital: Section 6.2 Chapter 6 Section 63 Section 6.3 Minimum price Solution Master Ready Tvbe here to search Filip Formulas Data Review View Home Insert Draw Page Layout -12 - AA BIUS A- Help General 2 Weap Text E2 D 2 B Merge Center - 9 Insert Delete Format Conditional Formats Cell Formatting Table Styles 094 F G Book value of equipment Aftertax salvage value: Market value Taxes Total Yegro Year 2 Year 4 Operating cash flow Capital spending Net working capital Total cash flows NPV IRR Profitability Index Chapter 6 Section 6.2 Section 6.3 Section 6.5 Minimum price Master i Solution