Using Microsoft Excel or Google Sheets, create a spreadsheet with multiple tabs to answer each of the following questions.
For full credit, your spreadsheet should be dynamic. For example, if an interest rate such as a tax rate or a required rate of return is required, enter that in a cell by itself so that if someone decides to change the tax rate or required return assumption, the worksheet will automatically recalculate itself. This is best accomplished by including an input section at the top, and then use the data from the input section to create your formulas.
Upload your excel file (or shared link to a Google Sheet) in the space provided in the last question (do not upload a pdf file because I want to check your formulas).
Partial credit will be given for a correct result. To receive full credit, your excel sheet will need to have calculated the result properly, and be flexible enough to produce a correct result if one or more of the assumptions (interest rate, tax rate, etc.) are changed.
Capital Budgeting
Eyas’ Enterprises is considering a new 3-year expansion project that requires an initial fixed asset investment of $2.18 million. The fixed asset will be depreciated straight-line to zero over its 3-year tax life, after which time, the company expects it will be worthless. The project is estimated to generate $1.645 million in annual sales, with annual costs of $0.61 million.
1. Suppose the company’s marginal tax rate is 19%.
In the first tab of your worksheet:
Prepare a pro forma income statement for each of the three years (you may use Table 9.1 in our textbook as a model).
Prepare a series of abbreviated pro forma balance sheets showing the capital requirements for the project (you may use Table 9.2 in our textbook as a model).
Prepare a pro forma statement of operating cash flow (you may use Table 9.4 in our textbook as a model).
Finally, prepare a pro forma statement of total cash flows from assets (you may use Table 9.5 in our textbook as a model).
Based on the results of your worksheet, what is the expected OCF in the first year?
2. In the previous problem, suppose that the required return on the project is 20%. Create a second tab in your spreadsheet to illustrate the calculation of the project’s Net Present Value (NPV).
In the space provided below, state the project’s NPV, rounded to the nearest dollar, and indicate whether the company should accept this project.
In the previous problem, suppose that the required return on the project is 20%. Create a second tab in your spreadsheet to illustrate the calculation of the project’s Net Present Value (NPV).
In the space provided below, state the project’s NPV, rounded to the nearest dollar, and indicate whether the company should accept this project.
3.Suppose now that the project requires an initial investment in Net Working Capital of $250,000, and the fixed asset will have a market value of$180,000 at the end of the project. Create a third tab in your worksheet and prepare an updated projection of total cash flows. Using these cash flows, compute the project’s internal rate of return (IRR).
Using the results of your worksheet, in the space below, state the IRR as a percentage to two decimal places, and indicate whether the project should be accepted using the IRR rule.
4. Suppose that management applied the payback rule to your projected cash flows and decided to move forward with the project. Management would like to finance the project over three years. The company’s bank has presented two options:
A three-year amortized loan with equal quarterly payments made at the end of each quarter, with an interest rate of 11% APR.
A three-year amortized loan in which the company pays interest at the rate of 12% APR, plus equal principal payments, at the end of each quarter.
Create a new tab in your worksheet and prepare two amortization schedules – similar to the ones shown on page 149 of the textbook – showing each quarterly payment over the term of the loan, the principal and interest portion of each payment, and the beginning and ending balances. Keep in mind that loan interest rates are subject to change, and it would be useful to have the ability to vary the interest rates on both loans (i.e., treat the interest rates as input parameters).
In the space below:
Briefly comment on the appropriateness of using the payback rule to evaluate this project, and
Report the total interest paid on each loan, and
Make a recommendation as to which financing option would be better for the company.
