In excel Sunshine Smoothies Company $($SSC$)$ manufactures and distributes smoothies. SSC is considering the development of a new line of high$-$protein energy smoothies. SSC$\text{'}$s CFO has collected the following information regarding the proposed project, which is expected to last $10$ years:

$$ The project can be operated at the company's Charleston plant, which is currently vacant.

$$ The product development team has spent $$450,000$ over four years developing this line of high$-$protein energy smoothies.

$$ The project will require that the company spend $$6\mathrm{.}25$ million today $($t $=0)$ to purchase additional equipment. This equipment is depreciated a accelerated depreciation schedule at the rate of $20\%$ in year $1,25\%$ in year $2,15\%$ in year $3,10\%$ for each of the following four years. The company plans to use the equipment for all $10$ years of the project. At t $=10($which is the project's last year of operation$),$ the equipment is expected to be sold for $$1,025,000$ before taxes.

$$ The project will require an increase in net operating working capital of $$850,000$ at t $=0.$ This working capital will be partially funded by Accounts Payable of $$350,000.$ Accounts Receivable will be $$250,000$ starting in year $3$ and will continue to increase by $10\%$ a year until the end of the project.

$$ Expected high$-$protein energy smoothie sales are as follows:

Year $1$ $$1,500,000$

Year $2$ $$2,250,000$

Year $3$ $$3,750,000$

Years $4-8+15\%$

$$ The project's annual operating costs $($excluding depreciation$)$ are expected to be $60\%$ of sales in year one and decline ratably to $45\%$ of sales in Year $3$ and again ratably until $35\%$ of sales in Year $5$ until the end of the project.

$$ The company's tax rate is $25\%.$

$$ The following table gives SSC$$s earnings per share for the last $10$ years. The common stock, $8\mathrm{.}1$ million shares outstanding, is now $(1/1/22)$ selling for $$63\mathrm{.}00$ per share. The expected dividend at the end of the current year $(12/31/22)$ is $40\%$ of the $2021$ EPS. Because investors expect past trends to continue, g may be based on the historical earnings growth rate. $($Note that $9$ years of growth are reflected in the $10$ years of data.$)$

$$ Year $$ EPS $$ Year $$ EPS

$2012$ $$3\mathrm{.}902017$ $$5\mathrm{.}73$

$20134\mathrm{.}2120186\mathrm{.}19$

$20144\mathrm{.}5520196\mathrm{.}68$

$20154\mathrm{.}9120207\mathrm{.}22$

$20165\mathrm{.}3120217\mathrm{.}80$

$$ The current interest rate on new debt is $12\%$; SSC$$s marginal tax rate is $25\%$; and its target capital structure is $35\%$ debt and $65\%$ equity.

Please help me with the free cash flow set up$.$

$$ Find the NPV$,$ Payback Period, IRR & MIRR for the project.

$$ Run two new scenarios on this project

o Scenario A: Operating performance only improves to $45\%$ and stays flat at that rate until the end of the project. This is $30\%$ likely to happen

o Scenario B: SSC$$s production facility has been recently changed to an empowerment zone, making them eligible for a reduced federal tax obligation. Their new tax rate is $20\%.$ There is a $20\%$ likelihood of this happening.

o Assume there is a $50\%$ chance of the original scenario occurring.

$$ With these new scenarios, what is the adjusted rate of return on the project and what is the risk and coefficient of variation?

solve in excel