Regal Ware, Inc. is a midsized cookware and small appliance manufacturer located in West Bend,

WI$.$ The company president is Ryan Reigle, who inherited the company. The company originally

manufactured cookware and other household appliances when it was founded over $70$ years ago.

Over the years, the company has expanded, and it is now a reputable manufacturer of various

specialty cookware items. Jay Smithers, a recent UW$-$Milwaukee MBA graduate, had been hired

by the company in its finance department. One of the major revenue$-$producing items

manufactured by Regal Ware is a coffee maker. Regal Ware currently has a smart coffee maker

model on the market and sales have been excellent. The smart coffee maker is a unique item in

that it comes in a variety of tropical colors and is preprogrammed to play Jimmy Buffett music.

However, as with any small appliance item, technology changes rapidly, and the current smart

coffee maker has limited features in comparison with newer models. Regal Ware spent $$750,000$

to develop a prototype for a new smart coffee maker that has all the features of the existing one

but adds new features such as Wi$-$Fi tethering. The company has spent a further $$200,000$ on a

marketing study to determine the expected sales figures for the new smart coffee maker. Regal

Ware can manufacture the new smart coffee maker for $$205$ each in variable costs. Fixed costs

for the operation are estimated to run $$5\mathrm{.}1$ million per year. The estimated sales volume is $64,000,$

$106,000,87,000,78,000,$ and $54,000$ per year for the next five years, respectively. The unit price

of the new smart coffee maker will be $$485.$ The necessary equipment can be purchased for

$$34\mathrm{.}5$ million and will be depreciated on a seven$-$year MACRS schedule. It is believed the value of

the equipment in five years will be $$5\mathrm{.}5$ million. Networking capital for the smart coffee makers will

be $20$ percent of sales and will occur with the timing of the cash flows for the year $($i$.$e$.,$ there is no

initial outlay for NWC$).$ Changes in NWC will thus first occur in Year $1$ with the first year's sales.

Regal Ware has a $35$ percent corporate tax rate and a required rate of return of $12$ percent. Ryan

has asked Jay to prepare a report that answers the questions on Parts $1$ and $2.$

Input Area $($below$)$: Enter data in this section only

Equipment $ $-$

Salvage value $ $-$

R&D $ $-$ sunk cost

Marketing study $ $-$ sunk cost

Year $1$ Year $2$ Year $3$ Year $4$ Year $5$

Sales$($units$)00000$

Depreciation rate $0\mathrm{.}00\%0\mathrm{.}00\%0\mathrm{.}00\%0\mathrm{.}00\%0\mathrm{.}00\%$

Price $ $-$

VC $ $-$

FC $ $-$

Tax rate $0\%$

NWC percentage $0\%$

Required return $0\%$

Sensivity analysis

New price $ $-$

Quantity change $0$ NOTE: Change in units per year

Output Area $($below$)$: Note, contains cell formulas $-$ do not change.

Regal Ware, Inc

Year $1$ Year $2$ Year $3$ Year $4$ Year $5$

Sales $$0$ $$0$ $$0$ $$0$ $$0$

VC $00000$

Fixed costs $00000$

Dep $00000$

EBT $$0$ $$0$ $$0$ $$0$ $$0$

Tax $00000$

NI $$0$ $$0$ $$0$ $$0$ $$0$

$+$Dep $00000$

OCF $$0$ $$0$ $$0$ $$0$ $$0$

NWC

Beg $$0$ $$0$ $$0$ $$0$ $$0$

End $00000$

NWC CF $$0$ $$0$ $$0$ $$0$ $$0$

Net CF $$0$ $$0$ $$0$ $$0$ $$0$

Salvage

BV of equipment $$0$

Taxes $0$

Salvage CF $$0$

Net CF Time

$0$ $ $-$

$1-$

$2-$

$3-$

$4-$

$5-$

Payback period FALSE

PI #DIV$/0!$

IRR #NUM!

NPV $$0\mathrm{.}00$

Sensitivity to change in price

Sales Year $1$ Year $2$ Year $3$ Year $4$ Year $5$

Sales $$0$ $$0$ $$0$ $$0$ $$0$

VC $00000$

Fixed costs $00000$

Dep $00000$

EBT $$0$ $$0$ $$0$ $$0$ $$0$

Tax $00000$

NI $$0$ $$0$ $$0$ $$0$ $$0$

$+$Dep $00000$

OCF $$0$ $$0$ $$0$ $$0$ $$0$

NWC

Beg $$0$ $$0$ $$0$ $$0$ $$0$

End $00000$

NWC CF $$0$ $$0$ $$0$ $$0$ $$0$

Net CF $$0$ $$0$ $$0$ $$0$ $$0$

Salvage

BV of equipment $$0$

Taxes $0$

Salvage CF $$0$

Net CF Time

$0$ $ $-$

$1-$

$2-$

$3-$

$4-$

$5-$

NPV $ $-$

DNPV$/$DP #DIV$/0!$

Sensitivity to change in quantity

Sales Year $1$ Year $2$ Year $3$ Year $4$ Year $5$

Sales $$0$ $$0$ $$0$ $$0$ $$0$

VC $00000$

Fixed costs $00000$

Dep $00000$

EBT $$0$ $$0$ $$0$ $$0$ $$0$

Tax $00000$

NI $$0$ $$0$ $$0$ $$0$ $$0$

$+$Dep $00000$

OCF $$0$ $$0$ $$0$ $$0$ $$0$

NWC

Beg $$0$ $$0$ $$0$ $$0$ $$0$

End $00000$

NWC CF $$0$ $$0$ $$0$ $$0$ $$0$

Net CF $$0$ $$0$ $$0$ $$0$ $$0$

Salvage

BV of equipment $$0$

Taxes $0$

Salvage CF $$0$

Net CF Time

$0$ $ $-$

$1-$

$2-$

$3-$

$4-$

$5-$

NPV $$0\mathrm{.}00$

DNPV$/$DQ #DIV$/0!$

I need help filling this in