Capital Budgeting
.pdf
keyboard_arrow_up
School
Mississippi State University *
*We aren’t endorsed by this school
Course
8113
Subject
Finance
Date
Apr 3, 2024
Type
Pages
8
Uploaded by BrigadierHedgehog2979
García and Martinez manufacture widgets and currently have $25,000,000 in taxable income. The company is considering an expansion, and they’ve asked you to evaluate the project. The expansion requires the firm to produce 70,000 widgets a year for 6 years, and the company estimates they can sell them for $32 per widget. García and Martinez estimate they will need an additional $5,000,000 worth of machinery. The machinery costs a fixed $350,000 a year to operate. The machinery’s depreciable life is 8-years, and the company expects to salvage the machinery for $900,000 at the end of Year 6. If the project is accepted, the company will immediately increase inventory by $400,000 and maintain the new inventory level over the project’s life. Similarly, the company will immediately add $175,000 to their cash balance at start-up and maintain that higher cash balance over the project’s life. The investments in cash and inventory will be recovered when the project is completed. The variable cost of producing a widget is $8.00 and the cost of capital is 10.00%. The company estimates that the variable cost will increase in each subsequent year by 5.00% and the sales price will increase by 3.00%. All other numbers are assumed to remain constant. You have been asked to evaluate the project's profitability and sensitivity to the base assumptions. Evaluate the project by linking to the named variables in Column K. Capital Budgeting Exam Requirements
100.0
total points
1. Enter the relevant values for the variables that you use in solving this problem in Column K. Name each Information/Variable cell in Column K that contains a variable. Use the following names: Income, Quantity, Life, Price, Machinery, MaintenanceCost, DepLife, Salvage, Inventory, CashBalance, VariableCost, growthVC, growthPrice, WACC, and TaxRate. For instance, define the name of cell K3 to be Income and the name of cell K4 to be Quantity. When linking cells or writing functions, use these names to reference the cells in your calculations. This makes it easier for others to know what the functions are doing. (7.5 points)
2. Calculate revenues in Row 3. Formulas should use cell names when possible. Use the ^ symbol to exponentiate and refer to Row 2 to select the number of periods for compounding. (5.0 points)
a. Fun fact: I formatted Row 2 to accept a number (which is why you can reference the cell in the INDEX function), but Excel displays text with the number to make it more readable as well as functional. If you look in cell D2, only a one is displayed in the formula bar but not the text Year. I did this by using a custom cell format. Here the custom format I use is "Year "0. The text inside the “ ” tells Excel the text I want to display in front of the number, and the 0 tells Excel to display a whole digit after the text. If I had instead typed 0.0 it would display a whole digit as well as one decimal place (e.g., Year 1.0).
b. As an example, to compound two periods of growth in Excel your formula would look like (1+growthPrice)^2, but the value 2 should be a link to that value in Row 2.
If done correctly with relative and absolute cell references, you can write the formula for year 1 and then copy and paste the formula into the other cells.
3. Calculate variable costs in Row 4. Formulas should use cell names when possible. As before, your formula should compound growth by linking to the cells in Row 2. (5.0 points)
4. Calculate fixed costs in Row 5. Formulas should use cell names when possible. (5.0 points)
5. Use the information in the previous steps to calculate EBITDA in Row 6. (5.0 points)
6. Use the INDEX function in Row 7 to calculate depreciation using the depreciation table. (5.0 points) Some helpful pointers:
a. Reference the entire depreciation table in the INDEX function. Reference the current project year in Row 2 in the function to determine the correct row for depreciation. Reference the depreciable life of the asset to identify the correct column.
b. Note for calculating after-tax salvage value: If the depreciable life is not equal to the life/years that you will own the asset, the book value may not equal zero when you sell the asset. c. If done correctly, the Year 1 depreciation should be $1,250,000. 7. Calculate EBIT in Row 8. (5 points)
8. Use that marginal tax rate to calculate taxes owed. (7.0 points)
a. Use the XLOOKUP function on the provided tax table to calculate the marginal tax rate in cell K17. You may use web resources to learn about the function (e.g., https://www.goskills.com/Excel/Resources/Xlookup-in-Excel). If you have trouble with this at first, just type in the rate in K17 and fix it later after completing the other steps below.
b. Name cell K17 TaxRate and use the name in your formulas to calculate taxes in each year in Row 9. 9. Calculate operating cash flow for each period in Row 10. The OCF in Year 1 should be about $1,300,000, and in Year 6 it should be about $1,130,000. (7.0 points)
Note that when I say about
, I mean just that. I have not provided the exact amount.
10. Calculate the change in net working capital for each period in Row 11. In most years it will be zero. (5.0 points)
11. Calculate the net capital spending for each period in Row 12. Don’t forget to pay taxes on the salvage value. In most years net C.S. will be zero. (5.0 points)
12. Calculate the cash flow from assets for each period in Row 13. CFFA in Year 0 should be about -$5,580,000 and the Year 6 should be about $2,570,000. (5.0 points)
13. Evaluate the project. (10.0 points)
a. Name cell C24 IRR. Use the IRR function to then calculate the internal rate of return on the project. b. Name cell D24 NPV. Use the NPV function to then calculate the net present value of the project. The value should be close to $400,000. If you haven’t used the NPV function before, I posted a video demonstrating the function.
c. Name cell E24 Decision. Use an IF statement to return the text “Accept” or “Reject” depending on the NPV calculation. You can find examples at https://corporatefinanceinstitute.com/resources/excel/functions/excel-if-statement-guide/ Scenario Analysis
14. Construct a sensitivity analysis for units sold (quantities are listed in Column B starting in Row 25). Define a 1-way data table using Excel’s What-If Analysis located in the data tab. (5.0 points)
The table should include the IRR, NPV, and Decision for each quantity. That is, for each quantity Excel will calculate these three items in the table which will extend from C25 to E33. https://www.wallstreetmojo.com/one-variable-data-table-in-excel/ 15. Define a base-case, best-case and worst-case scenario using the Scenario Manager in Excel’s What-If Analysis. Name them Base Case, Best Case, and Worst Case. (4.5 points)
ExPrep is case sensitive, so name the scenarios exactly as I’ve written them. Examples are at https://www.educba.com/what-if-analysis-in-excel/ Use the following value ranges for the best and worst cases: a. Per unit price is plus/minus $3.00
b. Quantity sold is plus/minus 12,000 units
c. Marginal cost of producing a widget is plus/minus $1.00
Note
: Price per unit, quantity, and costs do not all move in the same direction when constructing best/worst case scenarios. 16. Using the Scenario Manager in Excel’s What-If Analysis, create a scenario summary (this will create a new worksheet titled Scenario Summary; do NOT change the name). (6.0 points)
Reference the IRR, NPV, and Decision calculations in cells C24:E24 when creating the summary. The left-hand column in the scenario summary on the new worksheet should list the changing cells as: Quantity, VariableCost, Price (example below). If you correctly named all cells, Excel will generate a table with these row and column names in a new sheet. When you submit your file for grading, be sure that the base-case is displayed in the worksheet!!!!! All three cases will be displayed on the Scenario Summary worksheet, but the NPV worksheet you submit to ExPrep should be based on the base case values. Scenario Summary
Current Values:
Base Case
Worst Case
Best Case
Changing Cells:
Quantity
VariableCost
Price
Result Cells:
IRR
NPV
Decision
Sensitivity Analysis
17. Use the values in Year 1 to estimate the percentage change in OCF given a 1% change in quantity. Use the short-cut formula given in the book and used in the homework. (3.0 points)
Place your answer in D17. The answer should be about 1.3.
18. Use that estimate for the sensitivity of OCF to quantity to estimate what OCF would be in Year 1 under the best-case scenario (i.e., if quantity were 12,000 units higher). (2.0 points)
Place your answer in D18. Hint: sensitivities relate % changes to % changes so you'll need to calculate the % change in sales to get the answer (roughly $1,585,000).
19. Using the 1-way table you constructed previously, estimate the sensitivity of NPV to changes in quantity. (2.0 points)
To estimate the percentage changes, use the base-case value of 70,000 units as the starting value and compare that to the new values if quantity increases by 5,000 units.
Year 0
Year 1
Revenues
Variable Costs
Fixed Costs/Expenses
EBITDA
Depreciation
EBIT
Taxes
OCF
Change NWC
Net Capital Spending
CFFA
Sensitivity Analysis
Sensitivity of OCF to Q OCF if Q is Best Case % Change in NPV (70,000 to 75,000 units)
% Change in Q (75,000 to 80,000 units)
Sensitivity of NPV to Q
IRR
NPV
Units Sold
12.19%
396,710.36
$ 50,000
4.06%
(1,034,007.39)
$ 55,000
6.16%
(676,327.96)
$ 60,000
8.21%
(318,648.52)
$ 65,000
10.22%
39,030.92
$ 70,000
12.19%
396,710.36
$ 75,000
14.13%
754,389.80
$ 80,000
16.03%
1,112,069.24
$ 85,000
17.91%
1,469,748.68
$
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Related Questions
Pappy’s Potato has come up with a new product, the Potato Pet. Pappy’s paid $50,000 for a marketing survey to determine the viability of the product. It is estimated that Potato Pet will generate sales of $780,000 per year. The fixed costs associated with this project will be $178,000 per year and variable costs will amount to 30% of sales. The equipment will cost $600,000 and be depreciated in a straight-line manner for the four years of the project life. It can be sold for $20,000 at the end of the project. The initial net operating working capital is $40,000 and will increase by $10,000 each year until the end of the project. Pappy’s is paying a 25% tax rate and has a required rate of return of 6%.The NPV of this project is $__________________.
arrow_forward
Cameron Industries is purchasing a new chemical vapor depositor in order to make silicon chips. It will cost $7,000,000 to buy the machine and $15,000 to have it delivered and installed. Building a
clean room in the plant for the machine will cost an additional $3 million. The machine is expected to raise gross profits by $4,000,000 per year, starting at the end of the first year, with associated
costs of $1 million for each of those years. The machine is expected to have a working life of five years and will be depreciated over those five years. The marginal tax-rate is 20%. What are the
incremental free cash flows associated with the new machine in year 2?
A. $1,597,000
B. $1,403,000
C. $1,397,000
O D. $2,680,600
arrow_forward
Shine Co. is looking at expanding its business to include Bitcoin Mining, and change their name to CoinCo. They will need to purchase $1,000,000 worth of mining machines. It is estimated that these machines will generate $1,000 in revenue per day. The machines will require 15 days of maintenance per year, during which time they will not produce any revenue. Shine Co. estimates that the machines will cost $100 per day of operation. CoinCo also has to pay a commission of 1 penny per dollar of revenue. CoinCo has a 7.5% cost of capital and a 25% tax rate. The machines can be sold for 25% of the original cost after 5 years. The machines will be depreciated using straight-line depreciation over 5 years.What is the project's:NPVIRRPAYBACK PERIODDISCOUNTED PAYBACK PERIODNET PAYBACK PERIOD
arrow_forward
SEE MORE QUESTIONS
Recommended textbooks for you
Intermediate Financial Management (MindTap Course...
Finance
ISBN:9781337395083
Author:Eugene F. Brigham, Phillip R. Daves
Publisher:Cengage Learning
EBK CONTEMPORARY FINANCIAL MANAGEMENT
Finance
ISBN:9781337514835
Author:MOYER
Publisher:CENGAGE LEARNING - CONSIGNMENT
Principles of Accounting Volume 2
Accounting
ISBN:9781947172609
Author:OpenStax
Publisher:OpenStax College
Related Questions
- Pappy’s Potato has come up with a new product, the Potato Pet. Pappy’s paid $50,000 for a marketing survey to determine the viability of the product. It is estimated that Potato Pet will generate sales of $780,000 per year. The fixed costs associated with this project will be $178,000 per year and variable costs will amount to 30% of sales. The equipment will cost $600,000 and be depreciated in a straight-line manner for the four years of the project life. It can be sold for $20,000 at the end of the project. The initial net operating working capital is $40,000 and will increase by $10,000 each year until the end of the project. Pappy’s is paying a 25% tax rate and has a required rate of return of 6%.The NPV of this project is $__________________.arrow_forwardCameron Industries is purchasing a new chemical vapor depositor in order to make silicon chips. It will cost $7,000,000 to buy the machine and $15,000 to have it delivered and installed. Building a clean room in the plant for the machine will cost an additional $3 million. The machine is expected to raise gross profits by $4,000,000 per year, starting at the end of the first year, with associated costs of $1 million for each of those years. The machine is expected to have a working life of five years and will be depreciated over those five years. The marginal tax-rate is 20%. What are the incremental free cash flows associated with the new machine in year 2? A. $1,597,000 B. $1,403,000 C. $1,397,000 O D. $2,680,600arrow_forwardShine Co. is looking at expanding its business to include Bitcoin Mining, and change their name to CoinCo. They will need to purchase $1,000,000 worth of mining machines. It is estimated that these machines will generate $1,000 in revenue per day. The machines will require 15 days of maintenance per year, during which time they will not produce any revenue. Shine Co. estimates that the machines will cost $100 per day of operation. CoinCo also has to pay a commission of 1 penny per dollar of revenue. CoinCo has a 7.5% cost of capital and a 25% tax rate. The machines can be sold for 25% of the original cost after 5 years. The machines will be depreciated using straight-line depreciation over 5 years.What is the project's:NPVIRRPAYBACK PERIODDISCOUNTED PAYBACK PERIODNET PAYBACK PERIODarrow_forward
Recommended textbooks for you
- Intermediate Financial Management (MindTap Course...FinanceISBN:9781337395083Author:Eugene F. Brigham, Phillip R. DavesPublisher:Cengage Learning
- EBK CONTEMPORARY FINANCIAL MANAGEMENTFinanceISBN:9781337514835Author:MOYERPublisher:CENGAGE LEARNING - CONSIGNMENTPrinciples of Accounting Volume 2AccountingISBN:9781947172609Author:OpenStaxPublisher:OpenStax College
Intermediate Financial Management (MindTap Course...
Finance
ISBN:9781337395083
Author:Eugene F. Brigham, Phillip R. Daves
Publisher:Cengage Learning
EBK CONTEMPORARY FINANCIAL MANAGEMENT
Finance
ISBN:9781337514835
Author:MOYER
Publisher:CENGAGE LEARNING - CONSIGNMENT
Principles of Accounting Volume 2
Accounting
ISBN:9781947172609
Author:OpenStax
Publisher:OpenStax College