$*$SOLVE USING EXCEL PLEASE!$*$ Milani Electronics sells only one product, it$$s ABC$123$ gadget. The company put together a strategic

plan that included the following sales estimates:

Year Sales in Units

$19,000$

$215,000$

$318,000$

$4622,000$

Currently, the product sells for $$33\mathrm{.}00.$ Cost data associated with the product is as follows:

$$ Materials to create the product: $$5$

$$ Each product requires $42$ minutes of labor and the average hourly rate is $$10$ per hour

$$ Variable MOH costs are $$2$ per unit

$$ Selling Costs are $$1$ per unit

$$ Factory Supervisor Salaries are $$55,000$ per year

$$ Factory Property Tax and Insurance are $$25,000$ per year

$$ Other Administrative Costs are $$10,000$ per year

$$ Production levels are expected to be $9,000,17,000,$ and $16,000$ for the next three years.

Milani$$s is preparing to ask Hesburgh Bank for a loan for some new equipment. The loan is expected

to be for $$315,000$ and it carries an $8\%$ interest rate. As part of its due diligence, the bank would like

to see three years of projected income statements following GAAP $($you can ignore any potential

interest expense from the loan$).$ In order to better understand the implications on profits, Milani$$s

executive management team prefers to use a contribution formatted income statement for the same

three$-$year period.

Requirement $1$: Please provide three years of projected income statements for the bank and

the management team. The bank and management team are also interested in analyzing the

company$$s profitability so please use all appropriate ratios or calculations to help each group

understand the business.

The proceeds from the loan will be used to purchase some new factory equipment that will enhance

the product and give it broad market appeal. The company has performed marketing and cost

studies that revealed the following information:

a$.$ New equipment would have to be acquired to produce the device. The equipment would cost

$$315,000$ and have a six$-$year useful life. After six years, it would have a salvage value of

about $$15,000.$

b$.$ Sales unit projections are at $5\%$ above the previously budgeted levels and the quality

enhancement would allow Milani$$s to sell the product for $$45.$

c$.$ Updated cost information includes the following:

$$ Materials to create the product: $$5$

$$ Each device requires $48$ minutes of labor and the average hourly rate is $$12$ per hour

$$ Variable MOH costs are $$2\mathrm{.}50$ per unit

$$ Selling Costs are $$1$ per unit

$$ Factory Supervisor Salaries are $$55,000$ per year

$$ Factory Property Tax and Insurance are $$25,000$ per year

$$ Other Administrative Costs are $$10,000$ per year

$$ Production levels would also increase by $5\%$ per year over previously budgeted amounts

Accounting $121$ Final Case

c$.$ Production and sales of the device would require working capital of $$60,000$ to finance

accounts receivable, inventories, and day$-$to$-$day cash needs. This working capital would be

released at the end of the project$$s life.

e$.$ The company uses straight line depreciation by taking the cost of an asset less the salvage

value.

f$.$ In addition to the costs above and to gain rapid entry into the market, the company would

have to advertise heavily. The advertising costs would be:

Year

Amount of Yearly

Advertising

$12$ $ $50,000$

$3$ $ $30,000$

$46$ $ $20,000$

Milani$$s finance team has warned the executive management that due to the current rise in interest

rates, the rate offered on the loan is at risk and could change. As a result, any capital budgeting

analysis should include interest rate swings of $+/-1\%.$

Requirement $2$: Please provide the necessary capital budgeting analysis for this project

$($assume cash flows in years $4-6$ are the same as year $3)$ as well as updated projected income

statements, ratios, and calculations from requirement $1$ for the next three years for Milani$$s$.$

Unlike before, you should include interest expense in your projections $($assume $8\%$ is the

correct interest rate$)$ and you can assume the entire loan won$$t be paid off until the end of the

third year.

Requirement $3$: Prepare a written analysis that provides a recommendation for or against

purchasing the new equipment. The quality of your work should be that of a report ready to

be presented to the executive management team at Milani Inc.