GOODWEEK TIRES, INC.

After extensive research and development, Goodweek Tires, Inc., has recently developed a new tire, the SuperTread, and must decide whether to make the investment necessary to produce and market it. The tire would be ideal for drivers doing a large amount of wet weather and off-road driving in addition to normal freeway usage. The research and development costs so far have totaled about $10 million. The SuperTread would be put on the market beginning this year, and Goodweek expects it to stay on the market for a total of four years. Test marketing costing $5 million has shown that there is a significant market for a SuperTread-type tire.

As a financial analyst at Goodweek Tires, you have been asked by your CFO, Adam Smith, to evaluate the SuperTread project and provide a recommendation on whether to go ahead with the investment. Except for the initial investment that will occur immediately, assume all cash flows will occur at year-end.

Goodweek must initially invest $160 million in production equipment to make the SuperTread. This equipment can be sold for $65 million at the end of four years. Goodweek intends to sell the SuperTread to two distinct markets:

1.The original equipment manufacturer (OEM) market:The OEM market consists primarily of the large automobile companies (like General Motors) that buy tires for new cars. In the OEM market, the SuperTread is expected to sell for $41 per tire. The variable cost to produce each tire is $29.

2.The replacement market:The replacement market consists of all tires purchased after the automobile has left the factory. This market allows higher margins; Goodweek expects to sell the SuperTread for $62 per tire there. Variable costs are the same as in the OEM market.

Goodweek Tires intends to raise prices at 1 percent above the inflation rate; variable costs will also increase at 1 percent above the inflation rate. In addition, the SuperTread project will incur $43 million in marketing and general administration costs the first year. This cost is expected to increase at the inflation rate in the subsequent years.

Goodweek?s corporate tax rate is 40 percent. Annual inflation is expected to remain constant at 3.25 percent. The company uses a 13.4 percent discount rate to evaluate new product decisions. Automotive industry analysts expect automobile manufacturers to produce 6.2 million new cars this year and production to grow at 2.5 percent per year thereafter. Each new car needs four tires (the spare tires are undersized and are in a different category). Goodweek Tires expects the SuperTread to capture 11 percent of the OEM market.

Industry analysts estimate that the replacement tire market size will be 32 million tires this year and that it will grow at 2 percent annually. Goodweek expects the SuperTread to capture an 8 percent market share.

The appropriate depreciation schedule for the equipment is the seven-year MACRS depreciation schedule. The immediate initial working capital requirement is $9 million. Thereafter, the net working capital requirements will be 15 percent of sales. What are the NPV, payback period, discounted payback period, IRR, and PI on this project?

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?
What is the NPV of the project?
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?

Ross, Westerfield, Jaffe, and Jordan's Spreadsheet Master Corporate Finance, 11th edition by Brad Jordan and Joe Smolira Version 11.0 Chapter 6 In these spreadsheets, you will learn how to use the following Excel functions: VDB SLN Solver The following conventions are used in these spreadsheets: 1) Given data in blue 2) Calculations in red NOTE: Some functions used in these spreadsheets may require that the "Analysis ToolPak" or "Solver Add-In" be installed in Excel. To install these, click on the File tab then "Excel Options," "Add-Ins" and select "Go." Check "Analysis ToolPak" and "Solver Add-In," then click "OK." Chapter 6 - Section 2 The Baldwin Company: An Example Because capital budgeting requires numerous repetitive cash flows, it is an ideal application for Excel. When doing a capital budgeting problem, as in most Excel uses, you should do few or no calculations on your own, but rather let Excel do the calculations for you. We will begin with the Baldwin Company project. We have the following projections for the project: Units sold per year: Price per unit for Year 1: Price increase per year: Inflation rate: Tax rate: Unit production cost for Year 1: Increase in unit cost per year: NWC to start project: NWC for subsequent years: Depreciation rate: Cost of machine Cost of warehouse: Pretax salvage value: $ $ $ $ $ $ Year 1 5,000 20.00 2% 5% 34% 10.00 10% 10,000 10% 20.0% 100,000 150,000 30,000 Year 2 8,000 Year 3 12,000 Year 4 10,000 Year 5 6,000 32.0% 19.2% 11.5% 11.5% We will start off with some preliminary work, including the depreciation each year, sales price, and unit costs: Depreciation Accumulated depreciation Adjusted basis of machine Price per unit Sales revenue Cost per unit Operating costs $ Year 1 20,000 $ 20,000 80,000 20.00 100,000 10.00 50,000 Year 2 32,000 $ 52,000 48,000 20.40 163,200 11.00 88,000 Year 3 19,200 $ 71,200 28,800 20.81 249,696 12.10 145,200 Year 4 11,500 $ 82,700 17,300 21.22 212,242 13.31 133,100 Year 5 11,500 94,200 5,800 21.65 129,892 14.64 87,846 The change in net working capital for each year is the beginning net working capital for each year minus the net working capital investment at the end of the year. So, the change in net working capital each year is: Net working capital Beginning NWC End of year NWC NWC cash flow $ $ 10,000 $ 10,000 - $ 10,000 $ 16,320 (6,320) $ 16,320 $ 24,970 (8,650) $ 24,970 $ 21,224 3,745 $ 21,224 21,224 The machine will have a salvage value at the end of the project, but we are concerned with the aftertax salvage value, which is: Pretax salvage value Taxes on sale Aftertax salvage value $ $ 30,000 8,228 21,772 Now we can calculate the pro forma income statement for each year (Table 6.1), which will be: Sales revenue Operating costs Depreciation Income before taxes Taxes at 34 percent Net income $ $ $ 100,000 $ 50,000 20,000 30,000 $ 10,200 19,800 $ 163,200 $ 88,000 32,000 43,200 $ 14,688 28,512 $ 249,696 $ 145,200 19,200 85,296 $ 29,001 56,295 $ 212,242 $ 133,100 11,500 67,642 $ 22,998 44,643 $ 129,892 87,846 11,500 30,546 10,386 20,160 With this, the incremental cash flows each year, NPV for different interest rates, and IRR for the project are (Table 6.4): Year 0 Sales revenue Operating costs Taxes Cash flow from operations Bowling ball machine Warehouse Net working capital Total cash flow of project NPV 4% 10% 15% 15.68% 20% $ $ $ $ $ A Note about Depreciation 123,643 51,590 5,473 0 (31,350) $ $ $ $ (100,000) (150,000) (10,000) (260,000) $ Year 1 100,000 $ 50,000 10,200 39,800 $ Year 2 163,200 $ 88,000 14,688 60,512 $ Year 3 249,696 $ 145,200 29,001 75,495 $ Year 4 212,242 $ 133,100 22,998 56,143 $ 39,800 $ (6,320) 54,192 $ (8,650) 66,846 $ 3,745 59,889 $ Year 5 129,892 87,846 10,386 31,660 21,772 150,000 21,224 224,656 There are actually six MACRS schedules: three-, five-, seven-, 10-, 15-, and 20-year schedules. The MACRS schedule is calculated using the depreciation according to the double declining balance method, and switching to straight-line depreciation when it is more advantageous. The three-, five-, seven-, and 10-year schedules use a factor of 2 (200%) when calculating the double declining balance depreciation amount, while the 15- and 20-year schedules use a factor of 1.5 (150%). Excel has a function, VDB, which can be used to construct a MACRS table. Below, we have constructed a MACRS table with all six schedules. Equipment Life (Years) Year 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 3 33.33% 44.44% 14.81% 7.41% 5 20.00% 32.00% 19.20% 11.52% 11.52% 5.76% 7 14.29% 24.49% 17.49% 12.49% 8.92% 8.92% 8.92% 4.46% 10 10.00% 18.00% 14.40% 11.52% 9.22% 7.37% 6.55% 6.55% 6.55% 6.55% 3.28% 15 5.00% 9.50% 8.55% 7.70% 6.93% 6.23% 5.90% 5.90% 6.02% 6.02% 6.02% 6.02% 6.02% 6.02% 6.02% 3.01% 20 3.75% 7.22% 6.68% 6.18% 5.71% 5.28% 4.89% 4.52% 4.46% 4.46% 4.46% 4.54% 4.54% 4.54% 4.54% 4.54% 4.54% 4.54% 4.54% 4.54% 2.27% RWJ Excel Tip To construct the MACRS table, we used the variable declining balance (VDB) function. Constructing the MACRS table is tricky because of the half-year convention. Below you will see what we entered for the second year of the three-year MACRS schedule. Cost is the cost of the equipment. In this case, we entered one in order to get the answers as a percentage rather than a dollar amount. Salvage is the salvage value, which is zero. Life is the life of the asset. Since we have a table here, we entered the column as a floating input and locked the row. This allows us to copy and paste the formula further down the table was well as across. The Start_period is the starting period for which we want to calculate the depreciation. With the half-year convention, we used the year and subtracted 1/2. To calculate the End_period, we used the MIN function. This function will return the lesser of the next year minus one-half, or the life of the asset. In most years we could have taken the next year minus one-half, but this would not work for the last year. Notice that this MIN function will not work for the first year since there is no prior year. So, for the first year, we eliminated the MIN function. Finally, the Factor is not shown on the picture above since Excel scrolls through the inputs in this case. We used a factor of two for the three-, five-, seven-, and 10-year schedules and a factor of 1.5 for the 15- and 20-year schedules. Finally, note that the MACRS schedule we calculated can vary slightly from the table presented in the textbook. The reason is that the IRS publishes a MACRS schedule, which is the schedule we used in the textbook. However, you are allowed to calculate the schedule on your own based on the rules outlined by the IRS. If you do so, you will get the table above, not the table in the textbook (or the table published by the IRS!). In the future, we will use the table in the textbook for our calculations. Chapter 6 - Section 3 Inflation and Capital Budgeting Inflation should always be considered in any long-term project. As long as inflation is correctly handled, the NPV of the project will be the same. For example, consider the projected proposed by Altshuler, Inc. Example 6.10: Real and Nominal NPV Altshuler, Inc. has generated the following forecast for a capital budgeting project. David Altshuler prefers to work in nominal terms, while Stuart Weiss prefers real cash flows. Whose approach is correct? Capital expenditures: Revenues (real terms): Cash expenses (real terms): Depreciation: Inflation rate: Nominal rate: Real rate: Tax rate: $ Year 0 1,210 Year 1 $ Year 2 1,900 $ 950 605 2,000 1,000 605 10.0% 15.5% 5.0% 40.0% RWJ Excel Tip To calculate the depreciation each year for straight-line depreciation, we can divide the initial cost by the life of the equipment, or we can use the built-in Excel function SLN as we have done here. The SLN we used in this case looks like this: The inputs are Cost, which is the initial cost, Salvage, which is the salvage value, and Life, which is the life of the asset. In general, we usually find it easier just to divide the cost by the life of the equipment in the cell rather than use this particular function, but it is available if you prefer. With these projections, we can generate the following nominal cash flows and NPV: Capital expenditures Revenues Expenses Depreciation Taxable income Taxes (40%) Income after taxes Depreciation Cash flow NPV @ 15.5% Nominal Cash Flows Year 0 $ (1,210) Year 1 $ $ $ $ 2,090 1,045 605 440 176 264 605 869 Year 2 $ $ $ $ 2,420 1,210 605 605 242 363 605 968 $268.00 We can also use real cash flows, which will be: Real Cash Flows Year 0 $ (1,210) Capital expenditures Revenues Expenses Depreciation Taxable income Taxes (0%) Income after taxes Depreciation Cash flow NPV @ 5% Year 1 $ $ $ $ 1,900 950 550 400 160 240 550 790 Year 2 $ $ $ $ 2,000 1,000 500 500 200 300 500 800 $268.00 When dealing with any cash flows, it is irrelevant whether you use real cash flows with the real interest rate or nominal cash flows with the nominal interest rate, the present value will always be the same. Chapter 6 - Section 5 Investments of Unequal Lives: The Equivalent Annual Cost Method To find the equivalent annual cost (EAC), we find the net present value of the project, then find the annuity that represents the annual cost based on the life of the p Suppose we have two different options for a pollution control system, a filtration system or a precipitation system. The relevant numbers for each alternative are: Equipment Operating cost Life (years) Filtration Precipitation system system $ 1,100,000 $ 1,900,000 $ 60,000 $ 10,000 5 8 Discount rate Tax rate 12% 34% We can calculate the NPV of each project as: Operating cost Depreciation EBIT Tax Net income Income Statements Filtration Precipitation system system $ 60,000 $ 10,000 220,000 237,500 $ (280,000) $ (247,500) (95,200) (84,150) $ (184,800) $ (163,350) So, using the bottom-up approach, the OCF for each alternative is: OCF $ 35,200 $ 74,150 Now, we can calculate the NPV of each project: NPV $ (973,112) $ (1,531,650) Using the PMT function to find the EAC, we get: EAC ($269,950.71) ($308,325.40) In the final analysis, we should choose the system that is the least expensive, which is the filtration system. Setting a Bid Price: A Capital Budgeting Extension Suppose the company you work for is entering a competitive bidding process for a new project. How do you determine the minimum bid price you would be willing the project? We know that you would not want to lose money on the project from a financial perspective. From our capital budgeting discussion, we know that if th a zero NPV, we make exactly the required return on the project. So, the minimum bid price we should submit is the price that results in a zero NPV. Since we know a flows of the project such as the initial investment, salvage value, net working capital, etc., we can set up the cash flows we know and back into the price the results i While doing this by hand is possible, it can often result in tedious calculations. Fortunately, Excel has a built-in function that will make the process much easier. We are bidding on the following project. The contract will last for four years, and the equipment will be depreciated on a three-year MACRS schedule. What is the m price we could submit? Equipment Pretax salvage value Units per year Price per unit VC as a percentage of sales Fixed costs MACRS Year 1 MACRS Year 2 MACRS Year 3 MACRS Year 4 Immediate NWC Tax rate Required return $ $ $ $ $ 3,300,000 75,000 125,000 22.64 45% 425,000 33.30% 44.40% 14.80% 7.40% 80,000 35% 10% We entered a price in the appropriate cell above. As we will show later, it does not really matter what price we entered. Next, we need to calculate the cash flows an the project with our hypothetical price. This will be: Year Revenues Variable costs Fixed costs Depreciation EBIT Taxes (35%) Net income $ $ $ 1 2,829,542 1,273,294 425,000 1,098,900 32,348 11,322 21,026 Pro Forma Income Statements 2 3 $ 2,829,542 $ 2,829,542 $ 1,273,294 1,273,294 425,000 425,000 1,465,200 488,400 $ (333,952) $ 642,848 $ (116,883) 224,997 $ (217,069) $ 417,851 $ 4 2,829,542 1,273,294 425,000 244,200 887,048 310,467 576,581 + Depreciation OCF $ 1,098,900 1,119,926 $ 1,465,200 1,248,131 $ 488,400 906,251 $ 244,200 820,781 To find the aftertax salvage value, we need to calculate the taxes. We get: Pretax salvage value: Taxes on sale: Aftertax salvage value: $ $ 75,000.00 (26,250.00) 48,750.00 The total cash flows for each year of the project are: Year OCF Change in NWC Capital spending Total cash flow 0 $ $ $ $ (80,000) (3,300,000) (3,380,000) $ Project Cash Flows 1 2 1,119,926 $ 1,248,131 $ 3 906,251 $ 1,119,926 $ 906,251 $ 1,248,131 $ 4 820,781 80,000 48,750 949,531 Finally, the NPV of the project at this unit price is: NPV: $ (949.26) The minimum bid price is the price at which the NPV of the project is zero. We can use Solver to find this unit price (and much more.) RWJ Excel Tip To use Solver, go to the Data tab, then click Solver. The inputs we used for this problem are: As you see, with Solver you first enter the target cell you would like to set to a specific value, in this case, the NPV cell. Since the lowest bid price is the price that res NPV, we chose to set the NPV cell equal to a value of zero. Next, we select the cell we would like to change in order to set the target cell equal to the value we chose we changed the unit price cell. This is why the original value we entered for the unit price is irrelevant: Solver will change the value when it solves the problem. Note we used Solver, we restored the original value. On the next worksheet, you can see the answer report generated by Solver. In this case, the bid price that results in a Minimum bid price: $ 22.64 We restored the original unit price so you could use Solver on this problem for practice. NOTE: There is a bug in Solver that will occur occasionally. In some cases, Solver will not launch, or if you try to save one or more of the reports, you may see "Solve unexpected internal error or available memory was exhausted" pop up. In this case, the solution is to uninstall Solver and re-install it. To do this: 1) Go to the Office button on the top left, click Excel options, choose Add-Ins, select Excel Add-Ins in the pulldown menu near the bottom of the box, and click 2) Uncheck the Solver add-in and click OK. 3) Go to the Office button on the top left, click Excel options, choose Add-Ins, select Excel Add-Ins in the pulldown menu near the bottom of the box, and click a repeat of Step 1. 4) Check the Solver add-in and select OK. he project. : ing to put in for f the project has w all of the cash lts in a zero NPV. e minimum bid s and NPV for results in a zero hose. In this case, Note that after in a zero NPV is: olver: An ick on Go. ick on Go. This is Microsoft Excel 14.0 Answer Report Worksheet: [CF Chapter 06 Excel Master.xlsx]Section 6.5 Report Created: 10/11/2015 3:41:55 PM Result: Solver found a solution. All Constraints and optimality conditions are satisfied. Solver Engine Engine: GRG Nonlinear Solution Time: 6.895 Seconds. Iterations: 1 Subproblems: 0 Solver Options Max Time 100 sec, Iterations 100, Precision 0.000001 Convergence 0.0001, Population Size 100, Random Seed 0, Derivatives Forward, Require Bounds Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 5%, Solve Without Integer Constraints Objective Cell (Value Of) Cell Name $C$92 NPV: Project Cash Flows Original Value Final Value $ 333,871.80 $ - Variable Cells Cell Original Value Name Price per unit Precipitation $D$48 system Constraints Cell Name $C$92 NPV: Project Cash Flows $ 25.00 $ Cell Value $ Final Value Integer 22.64 Contin Formula - $C$92=0 Status Binding Slack 0 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: a. What is the profitability index of the project? b. What is the IRR of the project? c. What is the NPV of the project? d. At what OEM price would Goodweek Tires be indifferent to accepting the project? Assume the replacement market price is constant. e. At what level of variable costs per unit would Goodweek Tires be indifferent to accepting the project? 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 OEM market: Price Variable cost Automobile production Growth rate Market share Replacement market: Price Variable cost Market sales Growth rate Market share Price increase above inflation VC increase above inflation Marketing and general costs Tax rate Inflation rate Required return Initial NWC NWC percentage of sales $ $ $ $ 10,000,000 5,000,000 140,000,000 54,000,000 14.30% 24.50% 17.50% 12.50% $ $ 38 22 5,600,000 2.50% 11.00% $ $ 59 22 14,000,000 2.00% 8.00% $ $ 1% 1% 26,000,000 40.00% 3.25% 15.90% 9,000,000 15% Nominal price increase Nominal VC increase Year 0 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 Replacement market Total Revenue Variable costs Marketing and general costs Depreciation EBT Tax Net income OCF New working capital: Beginning Ending NWC cash flow Book value of equipment Year 1 Year 2 Year 3 Year 4 Aftertax salvage value: Market value Taxes Total Year 0 Operating cash flow Capital spending Net working capital Total cash flows NPV IRR Profitability index Year 1 Year 2 Year 3 Year 4