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:

Year $1-2025170,000$ units

Year $2-2026230,000$ units

Year $3-2027280,000$ units

Year $4-2028300,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 $0$ 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,000$

$10-$year, $$1,000$ face value bonds that pay an annual coupon of $8\%$ and are currently

trading for $$1,075.$

The company$$s common stock has a beta of $1\mathrm{.}25.$ The risk free rate is $4\%$ and the expected

market risk premium is $5\mathrm{.}50\%.$ Preferred stockholders receive $$1$ per quarter as preferred

dividends. The project is considered by the financial department to be as risky as the

company $($assumption: the project has the same risk and leverage as the company$).$

Requirements

$1.$ 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.

$2.$ 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

loss complexities to the model$).$ I.e$.$ if EBIT is negative $$100,000$ and tax rate is

$10\%$ Tax line should be positive $$10,000$ as you are using a formula to calculate

taxes, adding $$10,000$ to the NOPAT.

$3.$ Scenario analysis

$$ Create two scenarios $($Best Case and Worst Case$)$ as follows: create a copy of your

Base Case spreadsheet $($new tab$)$ and change the input variables according to the

following data provided bel