Complete Excel with given information. Excel includes $4$ tabs $($only letting me upload one$)$

Project Details

The following are the assumptions developed by the PAK $1\mathrm{.}0$ Production and Sales & Marketing

Departments $($the $$Project Team$).$

Development Expenditures $($Period $0)$

$$ The following are the expected expenses and capital expenditures:

$$ Manufacturing expenses for ten test machines $-$ $$4,450,000($tax deductible$)$

PACK SYSTEMS INC. $$ CAPITAL PROJECT ANALYSIS

FIN $3504$ CASE ASSIGNMENT $$ Spring $2024$

$4|$ P a g e

$$ Engineering including salaries and wages of $$2,050,000($tax deductible$)$

$$ General and administrative expenses $$650,000($tax deductible$)$

$$ Capital expenditures for the needed manufacturing equipment $$13,050,000$

$$ $$9,800,000$ of equipment to be depreciated under $5-$year MACRS

$$ $$3,250,000$ of equipment to be depreciated under $3-$year MACRS

$$ MACRS table are supplied

$$ At the end of year $8$ it is estimated the equipment can be sold for $25\%$ of its

original cost

$$ Expenses to transport the PAK $1\mathrm{.}0$ to ten test customer locations and to install them

for testing $$35,000$ per machine $($tax deductible$)$

$$ Sales and marketing expenses of $$2,425,000$

$$ Initial working capital investment of $$500,000$

Years $18$

The Project Team determined to evaluate the PAK $1\mathrm{.}0$ over an eight$-$year life. However, it

believes its economic life could be longer but concluded eight years would be conservative in

evaluating the economics of the project. The Project Team has developed the following

forecast assumptions.

$$ Pricing per unit is as follows:

o $$765,000$ per unit

o $$32,000$ annual maintenance agreement. It is assumed this agreement will be

purchased in the year of the purchase of the unit only, as future renewals are

uncertain.

o Both are expected to have annual price increases of $3\%$ per year

o Number of units sold:

$$ Year $1-50$

$$ Year $275$

$$ Year $3125$

$$ Year $4150$

$$ Year $5170$

$$ Year $6170$

$$ Year $7135$

$$ Year $8110$

o Direct material expense per unit is expected to be $$400,000$ in the year of

introduction and will increase $3\%$ per year

o Manufacturing labor expense:

$$ Direct labor full$-$time$-$equivalents $($FTE$)$ of $2\mathrm{.}5$ per machine at a cost of

$$70,000$ per FTE

$$ Indirect labor FTE $1\mathrm{.}5$ per machine at a cost of $$55,000$ per FTE

$$ Labor costs will increase at $2\mathrm{.}0\%$ annually

PACK SYSTEMS INC. $$ CAPITAL PROJECT ANALYSIS

FIN $3504$ CASE ASSIGNMENT $$ Spring $2024$

$5|$ P a g e

o Direct overhead will be $8\mathrm{.}0\%$ of direct material cost in year $1$ decreasing $\mathrm{.}5\%$ each

year thereafter $($i$.$e$.$ year $2-8\mathrm{.}0\%-\mathrm{.}5\%=7\mathrm{.}5\%)$

o Indirect overhead will be $3\mathrm{.}0\%$ of direct material cost in year $1$ decreasing $\mathrm{.}25\%$

each year thereafter

o Sales and marketing expenses will be $7\mathrm{.}5\%$ of revenue in year $1$ decreasing $\mathrm{.}75\%$

each year thereafter

o General and administrative expenses will be $5\%$ of revenue in year $1$ decreasing

$\mathrm{.}5\%$ each year there after

Working capital assumptions

$$ As noted above the initial working capital investment is $$500,000$

$$ End of year working capital is projected to be $2\mathrm{.}0\%$ of the current year$$s revenue

$$ At the end of year $8$ the working capital accounts will be zero

Other

$$ Marginal tax rate $25\%$

$$ Required rates of return:

o Cost savings projects $10\%$

o Product line extensions $12\%$

o New products $14\%$

Financial Planning & Analysis Department

You are a financial analyst in the Company$$s financial planning and analysis department. You

have been assigned to the Project Team to develop a capital project analysis which will be part of

the presentation made to the Company$$s capital projects committee to obtain approval in order to

move forward with development of PAK $1\mathrm{.}0$