FNCE $301-$ Summer Term $2024$

Assignment $-$ Capital Budgeting

The Proposal

The R&D department of your company has just developed a new type of environmentally friendly electric light source called the EnviroLight. The group spent $$600,000$ developing this product and the Marketing Division has spent another $$50,000$ to assess the market demand. They believe that the EnviroLight has superior brilliance and have a significant longer life than conventional lightbulbs, and will have great market appeal at a competitive price of $$28.$ The Marketing Division estimates the following demand for the EnviroLight:

$\backslash$table$[[$Year $1-2025,170,000$ units$],[$Year $2-2026,230,000$ units$],[$Year $3-2027,280,000$ units$],[$Year $4-2028,300,000$ units$]]$

After that time they believe there would be no more sales of the product as newer products become available. The EnviroLight is expected to cost $$13$ each to manufacture $($variable cost$).$ In addition, fixed production costs are estimated at $$1\mathrm{.}4$ million per year. The manufacturing equipment necessary to produce the lights costs $$2,550,000$ to buy and would be depreciated at a $30$ percent CCA rate. The cost of installing the equipment is $$450,000,$ which will be covered by the supplier. The manufacturing equipment is expected to have a net salvage value equivalent to the ending balance of the UCC at the end of year four $($no income tax effects apply$).$ The required level of net operating working capital is $15\%$ of the next year's sales revenue $($Hint: you will need to determine the required investment in Net Operating Working Capital $-$ NOWC $-$ for the business starting in year o until year $4).$ The NWC will be recovered at the end of the project. The company's marginal income tax rate is $25$ percent.

Inflation adjustment: Inflation is expected to be $2\%$ per year, starting in the second year of the project $($Year $2-2026).$ Price per unit, variable cost, and fixed cost are adjusted by inflation.

Information about the firm's cost of capital $-$ WACC

The company has $1,250,000$ common shares outstanding that are currently trading at a price of $$45$ each; $200,000$ preferred shares with a market value of $$50$ each; and $80,00010-$year, $$1,000$ face value bonds that pay an annual coupon of $8\%$ and are currently trading for $$1,075.$

Requirements

Using the Excel spreadsheet $($template$)$ provided:

Populate part $1-$ Input Data with the key elements $/$ variables for the project. Use the yellow cells to input each data.

Populate part $2-$ CCA Schedule. Use the yellow cells to input each data $/$ formulas $/$ calculations.

Populate part $3-$ Projected Net Cash Flows $($Time line of annual cash flows$).$ Use the yellow cells to input each data $/$ formulas $/$ calculations.

Populate part $4-$ Key Output: Appraisal of the Proposed Project. Use the yellow cells to input each data $/$ formulas $/$ calculations $($Find the NPV$,$ IRR, MIRR, PI$,$ and payback of the EnviroLight project$).$

Set up the necessary equations by referencing to the input variable cells. The spreadsheet must be formula driven; do not put any numbers in equations, only cell references.

Use Excel's built$-$in functions $($i$.$e$.$ NPV and IRR functions$)$ when possible.

Format: Dollars should have the dollar sign $ and $2$ decimals for unit prices $($i$.$e$.$ price per unit or variable cost per unit $-$ $$50\mathrm{.}00)$ or no decimal for any other monetary value $($i$.$e$.$ fixed costs $-$ $$15,250).$ PI and Payback period values should have $2$ decimals. Percentage values should display with $\%$ symbol and $2$ decimals $($i$.$e$.15\mathrm{.}25\%)$

Requirement $1$ is your Base Case.

Sensitivity analysis

Change the sales price, variable cost per unit, and fixed cost $(+20\%$ and $-20\%)$ and recalculate the NPV of the project for each case. You will need to change one variable at a time.

Complete the sensitivity Table $-$ NPV with your results in the excel template. Use the yellow cells to input each data.

Hint: $0\%$ row should be equal to the NPV of the project $-$ Base Case for the three variables. You will need to calculate $6$ new NPV$,3$ for when each variable changes by $+20\%$ and $3$ more for when each variable changes by $-20\%.$

Note: In case EBIT is negative, allow the tax on operating income to be positive and provide inflow for simplification purposes $($we are not adding carry forward

iplexities to the model$).$ I.e$.$ if EBIT is negative $$100,$