$**$PLEASE SHOW EXCEL WORK $-$ NEED TO SEE HOW THE FORMULAS

AS WELL. TY$**$The business case team had compiled the following baseline

information surrounding the Sneaker $2013$ project:$1.$The life of the Sneaker $2013$project was expected to be six

years. Assume the analysis took place at the end of $2012\mathrm{.}2.$The suggested retail price of the shoe was $$190.$ Gross margins

for high$-$end athletic footwear averaged about $60\%$ at the retail

level, meaning each pair sold would net New Balance $$115\mathrm{.}3.$The global athletic footwear marketing $2011$ totaled

approximately $$74\mathrm{.}5$ billion and was expected to grow at a CAGR of

$1\mathrm{.}8\%$ from $2011$ to $2018,$reaching $$84\mathrm{.}4$ billion by $2018\mathrm{.}3$ Based on

market research and analysis of other recent athlete endorsements,

the New Balance marketing division estimated the following sales

volumes for Sneaker $2013$:

Year:$$

$20132014201520162017$

$2018$Pairs sold $($millions$)1\mathrm{.}2$

$1\mathrm{.}61\mathrm{.}42\mathrm{.}4$

$1\mathrm{.}80\mathrm{.}9$The $2016$ number assumed Kirani James participated in the $2016$

games in Rio de Janeiro, Brazil, and won at least one medal$4.$For the first two years, the introduction of Sneaker $2013$

would reduce gross profit of existing New Balance shoes as

follows:Lost

sales:$2013$:

$$35$

million$$

$2014$: $$15$ millionAssume the lost revenue had the same margins as Sneaker

$2013\mathrm{.}5.$In order to produce the shoe, the firm needed to build a

factory in Vietnam. This required an immediate outlay of $$150$

million, to be depreciated on a $39-$year MACRS$5$basis. Depreciation

percentages for the first six years respectively were: $2\mathrm{.}6\%,5\%,$

$4\mathrm{.}7\%,4\mathrm{.}5\%,4\mathrm{.}3\%,$ and $4\mathrm{.}0\%.$ The firm$$s analysts estimated the

building would be sold for $$102$ million at project termination.

This $$salvage value$$ has not been taken into consideration when

computing annual depreciation charges$6.$The company must immediately purchase equipment costing $$15$

million. Freight and installation of the equipment would cost $$5$

million. The cost of equipment and freight$/$installation was to be

depreciated on a five$-$year MACRS basis. Depreciation percentages

for the six years respectively were: $20\%,32\%,19\%,12\%,11\%,$ and

$6\%.$ It was believed the equipment could be sold for $$3$ million upon

project termination$7.$In order to manufacture Sneaker $2013,$ two of the firm$$s

working capital accounts were expected to increase immediately.

Approximately $$15$ million of inventory would be needed quickly to

fill the supply chain, and accounts payable were expected to

increase by $$5$ million. By the end of $2013,$ the accounts receivable

balance would be $8\%$ of project net revenue; the inventory balance

would be $25\%$ of the project$$s variable costs; and accounts payable

would be $20\%$ of the project$$s variable costs. All working capital

would be recovered at the end of the project by the end of the

sixth year$\mathrm{.}8.$Variable costs were expected to be $55\%$ of net revenue$\mathrm{.}9.$Selling, general, and administrative expenses were expected to

be $$7$ million per year$\mathrm{.}10.$Kirani James would be paid $$2$ million per year for his

endorsement of Sneaker $2013,$ withan additional $$1$ million Olympic

bonus in $2016\mathrm{.}11.$Other advertising and promotion costs were estimated as

follows:

Year$$

$20132014$

$201520162017$

$2018$A&P Expense $($millions$)$

$$25$ $$15$

$$10$ $$30$

$$25$ $$1512.$New Balance had already spent $$2$ million in research and

development on Sneaker $2013\mathrm{.}13.$The Sneaker $2013$project was to be financed using a

combination of equity and debt. The interest costs on the debt were

expected to be approximately $$1\mathrm{.}2$ million per year. The New Balance

discount rate for new projects such as this was $11\%14.$New Balance$$s effective tax rate was $40\%$Question: Produce a projected capital budgeting cash

flow statement for the Sneaker $2013$ project by answering the

following:a$.$ What is the projects initial $($year $0)$ investment outlay?b$.$ What are the projects annual $($years $2013-2018)$ net operating

cash flows?c$.$ What is the projects terminal $(2018)$ non$-$operating net cash

flow?d$.$ Does Sneaker $2013$ appear viable from a quantitative

standpoint? To answer this question, estimate the projects payback,

NPV$,$ and internal rate of return.$$