finance-Capital Budgeting and Risk Analysis

QUESTION

Case 59H

Student Version

9/29/96

TASTY FOODS
CORPORATION (B)

Capital Budgeting and Risk
Analysis

This
case focuses on capital budgeting risk assessment using the lite

athletic
drink project that was introduced in Case 12B, Tasty Foods

Corporation
(Part I).

The
model develops incremental cash flow estimates, then calculates

NPV,
IRR, MIRR, ARR, and Payback for the lite athletic drink project.

This
model differs from the Case 12B model only in that this one

includes
additional data tables which can greatly facilitate sensitivity

analysis. Also, this model contains a graph which can
be used to plot

the
sensitivity diagrams. You can change
the data tables, then use

them
to change the graph. Click on the tab
labelled ‘TASTY’ to view

the graph.

If
you are using the student version of the model, the cells containing

formulas
have been blanked out. Before using
the model, it is necessary

to
fill in the empty cells with the appropriate formulas. Once this is

done,
the model is ready for use.

=
=
=
=
=
=
=

INPUT DATA:

KEY OUTPUT:

Initial Investment:

Equipment
cost
$700,000

NPV

Freight

$35,000

IRR

Installation
$70,000

MIRR

Change
in NWC
$30,000

ARR

Payback

years

Operating Flows and Inflation
Rates:

Unit sales

700,000

t=0
sales price
$2.00

Fixed
oper costs
$190,000

t=0
VC per unit
$1.25

Price
inflation
4.0%

Cost
inflation
2.0%

Cannibalization of Other Projects:

SV, Taxes, and C of C:

Revenue Loss

$80,000

Salvage
value
$87,500

Cost Reduction
$35,000

Useful
life (yrs)
4

Net Cannibalization
$45,000

Tax rate

40%

Cost
of capital
12%

=
=
=
=
=
=
=

|::

MODEL-GENERATED DATA: TASTY FOODS CORPORATION (PART II)

Net Investment Outlay:

Depreciation
Schedule: Basis:

805000

Equipment
cost
700000

MACRS
Dep.
End of Year

Freight

35,000
Year
Factor
Expense
Book Value

Installation
70,000
——
——
——–
——–

Change
in NWC
30,000
1
33%
$265,650
$539,350

2
45%
$362,250
$177,100

835000
3
15%
$120,750
$56,350

4
7%
$56,350
$0

Cash Flows:

Year
Year
Year
Year
Year

0
1
2
3
4

——–
——–
——–
——–
——–

Unit
price
$2.00
$2.08
$2.16
$2.25
$2.34

Unit
sales

700,000
700,000
700,000
700,000

Revenues

$1,456,000
$1,514,240
$1,574,810
$1,637,802

Fixed
operating costs

190,000
190,000
190,000
190,000

Variable
operating costs

892,500
910,350
928,557
947,128

Total
operating costs

$1,082,500
$1,100,350
$1,118,557
$1,137,128

Depreciation

265,650
362,250
120,750
56,350

Net
cannibalization effects
45,000
45,000
45,000
45,000

Before
tax income

$62,850
$6,640
$290,503
$399,324

Taxes

25,140
2,656
116,201
159,730

Net
income

$37,710
$3,984
$174,302
$239,594

Plus
depreciation

265,650
362,250
120,750
56,350

Net
operating cash flow

$303,360
$366,234
$295,052
$295,944

Salvage
value

$87,500

SV tax

35,000

Recovery
of NWC

30,000

82500

Termination
CF
$ (835,000)
$ 303,360
$ 366,234
$ 295,052
$ 378,444

Project
NCF

Decision Measures:

Cumulative Cash Flows:

NPV
$178,337

0
$ (835,000)

IRR
21.6%

1
(531,640)

TV
1,594,505

2
(165,406)

MIRR
17.6%

3
129,646

ARR
#NAME?

4
508,090

Payback
2.56
years

|::

Scenario Analysis:

Scenario
Prob.
NPV
IRR
MIRR
ARR

——–
—–
———
—–
—–
—–

Worst

Base

Best

Expected value

Standard deviation

Coeff. variation

Data Tables for Sensitivity
Analysis

1. Unit Sales:

Units:
NPV:
IRR:
MIRR:
ARR:
Payback:

Deviation
178336.7163
0.215583062
0.175533063
#NAME?
2.560600324

-0.3
490,000

-0.2
560,000

-0.1
630,000

0
700,000

0.1
770,000

0.2
840,000

0.3
910,000

2. Initial Sales Price

Price:
NPV:
IRR:
MIRR:
ARR:
Payback:

Deviation
178336.7163
0.215583062
0.175533063
#NAME?
2.560600324

-0.3
1.40

-0.2
1.60

-0.1
1.80

0
2.00

0.1
2.20

0.2
2.40

0.3
2.60

3. Salvage Value

Salvage

Value:
NPV:
IRR:
MIRR:
ARR:
Payback:

Deviation
178336.7163
0.215583062
0.175533063
#NAME?
2.560600324

-0.3
61,250

-0.2
70,000

-0.1
78,750

0
87,500

0.1
96,250

0.2
105,000

0.3
113,750

4. Variable Cost:

VC per

Unit:
NPV:
IRR:
MIRR:
ARR:
Payback:

Deviation
178336.7163
0.215583062
0.175533063
#NAME?
2.560600324

-0.3
0.88

-0.2
1.00

-0.1
1.13

0
1.25

0.1
1.38

0.2
1.50

0.3
1.63

5. Cost of Capital:

WACC:
NPV:
IRR:
MIRR:
ARR:
Payback:

Deviation
178336.7163
0.215583062
0.175533063
#NAME?
2.560600324

-0.3
8.4%

-0.2
9.6%

-0.1
10.8%

0
12.0%

0.1
13.2%

0.2
14.4%

0.3
15.6%

Inflation
(Data Table 2 with NPV as Output Variable)

Price

Cost Inflation:

Inflation:
——–
–
–
–
–

0%
1%
2%
3%
4%

0%
$0
$0
$0
$0
$0

1%
0
0
0
0
0

2%
0
0
0
0
0

3%
0
0
0
0
0

4%
0
0
0
0
0

5%
0
0
0
0
0

6%
0
0
0
0
0

7%
0
0
0
0
0

8%
0
0
0
0
0

|::

=
=
=
=
=
=
=

END

 

ANSWER:

REQUEST HELP FROM A TUTOR