Scribd
Upload
7 views26 pages

NPV Analysis for Cash Flow Scenarios

The document outlines a financial analysis involving cash flows, capital outflows, and net present value (NPV) calculations over a five-year period with a discount rate of 10%. It presents various scenarios with different cash inflows and discount rates, ultimately showing desired outcomes and NPV results for management review. The analysis indicates that the NPV is acceptable in several scenarios, with specific values calculated for different cash flow conditions.

Uploaded by

pg22harsh.sharma
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd
7 views26 pages

NPV Analysis for Cash Flow Scenarios

The document outlines a financial analysis involving cash flows, capital outflows, and net present value (NPV) calculations over a five-year period with a discount rate of 10%. It presents various scenarios with different cash inflows and discount rates, ultimately showing desired outcomes and NPV results for management review. The analysis indicates that the NPV is acceptable in several scenarios, with specific values calculated for different cash flow conditions.

Uploaded by

pg22harsh.sharma
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd

Contents

Problem!A1
'Data Validation'!A1
'Combo Conditional'!A1
Sensitivity!A1
'Scroll Bar'!A1
'Scenario Dynamic'!A1
'Goal Seek'!A1
'Scenario Summary '!A1
'Data Table Grpah'!A1
'Different Intervals'!A1
Problem 1 : Data as given
Capital Outflow in Year 0 -1000000
Annual Cash inflows 28000
No of years 5
Discount rate 10%
Required to find out NPV
Contents!A1
Desired Outcome ( Management
Inputs Variables
Summary)
Cash Outflows (100,000.00)
Annual cash inflows 28,000.00 NPV 6,142.03 ACCEPT
No. of years 5.00
Rate of discounting 10.00%

Calculations
year 0 1 2 3 4 5

captial outflows (100,000)


Cash inflows 28,000 28,000 28,000 28,000 28,000
Total cash flows (100,000) 28,000 28,000 28,000 28,000 28,000
Discounting factor 1.00 0.91 0.83 0.75 0.68 0.62
Net Cash Flow (100,000) 25,455 23,140 21,037 19,124 17,386

Desired Outcome

NPV 6,142.03

Workings (Back End)


Contents!A1
Desired Outcome ( Management
Inputs Variables
Summary)
Cash Outflows (100,000.00)
Annual cash inflows NPV 19,030.97 ACCEPT
No. of years 5.00
Minimum desired NPV 5,000.00
Rate of discounting

Calculations
year 0 1 2 3 4 5

captial outflows (100,000)


Cash inflows - - - - -
Total cash flows (100,000) 31,000 31,000 31,000 31,000 31,000
Discounting factor 1.00 0.91 0.83 0.76 0.70 0.64
Net Cash Flow (100,000) 28,311 25,854 23,611 21,563 19,692

Desired Outcome

NPV 19,030.97

Workings
Discounting Rate Annual Cash Flows
Interval 0.5% Interval 1000
8.0% 3 25000 6
8.5% 10% 26000 31000
9.0% 27000
9.5% 28000
10.0% 29000
10.5% 30000
11.0% 31000
11.5% 32000
12.0% 33000
Contents!A1
Desired Outcome ( Management
Inputs Variables
Summary)
Cash Outflows (96,910.24)
Annual cash inflows NPV 17,662.57 ACCEPT
No. of years 5.00
Minimum desired NPV 5,000.00
Select
Rate of discounting

Calculations
year 0 1 2 3 4 5

captial outflows (96,910)


Cash inflows - - - - -
Total cash flows (96,910) 31,000 31,000 31,000 31,000 31,000
Discounting factor 1.00 0.90 0.81 0.73 0.66 0.59
Net Cash Flow (96,910) 27,928 25,160 22,667 20,421 18,397

Desired Outcome

NPV 17,662.57

Workings
Discounting Rate Annual Cash Flows
Interval 0.5% Interval 1000
8.0% 6 25000 6
8.5% 11% 26000 31000
9.0% 27000
9.5% 28000
10.0% 29000
10.5% 30000
11.0% 31000
11.5% 32000
12.0% 33000
Contents!A1
Scenario Summary
Current Values: 10% Increase 10% decrease
Changing Cells:
$C$3 (100,000.00) (110,000.00) (90,000.00)
Result Cells:
$C$22 19,030.97 9,030.97 29,030.97
Notes: Current Values column represents values of changing cells at
time Scenario Summary Report was created. Changing cells for each
scenario are highlighted in gray.
Contents!A1
Scenario Summary
Current Values: Base case Worst Case Best Case
Changing Cells:
$B$2 -100000 -100000 -120000 -90000
$B$3 28000 28000 25000 30000
$B$5 10% 10% 12% 9%
Result Cells:
$B$26 8910 8910 -1090 18910
Notes: Current Values column represents values of changing cells at
time Scenario Summary Report was created. Changing cells for each
scenario are highlighted in gray.
Contents!A1
Input data
capital investment -100000
Annual cash flows 28000
No of years (forecast) 5
Discounting rate 10%

Calculations
Year 0 1 2 3 4 5

Cash outflows -100000


Cash inflows 28000 28000 28000 28000 28000
Total Cash flows -100000 28000 28000 28000 28000 28000
Discounting factor 1.00 0.91 0.83 0.75 0.68 0.62
P.V. of cash flows -100000 25455 23140 21037 19124 17386
NPV 6142

Scenario Outcome
2023E 2024E 2025E 2026E 2027E 2028E
Capital Investment -100000 0 0 0 0 0
Discount rate 0% 10% 10% 10% 10% 10%
Cash flows 0 28000 28000 28000 28000 28000
NPV -3507.25

Choose Sccenaraio
Base Case 2

Scenarios 2023E 2024E 2025E 2026E 2027E 2028E


Cash Inflows
Best case 20000 25000 30000 35000 40000
Base Case 28000 28000 28000 28000 28000
Worst Case 0 10000 15000 20000 25000

Discount rate
Best case 9% 9.00% 9% 9% 9%
Base Case 10% 10% 10% 10% 10%
Worst Case 11% 11% 11% 11% 11%

Investment
Best case -70000 -15000
Base Case -100000
Worst Case -110000
Contents!A1
Desired Outcome ( Management
Inputs Variables
Summary)
Cash Outflows (12,000.00)
Annual cash inflows NPV 102,572.81 ACCEPT
No. of years 5.00
Minimum desired NPV 5,000.00
Select
Rate of discounting

Calculations
year 0 1 2 3 4 5

captial outflows (12,000)


Cash inflows - - - - -
Total cash flows (12,000) 31,000 31,000 31,000 31,000 31,000
Discounting factor 1.00 0.90 0.81 0.73 0.66 0.59
Net Cash Flow (12,000) 27,928 25,160 22,667 20,421 18,397

Desired Outcome

NPV 102,572.81

Workings
Discounting Rate Annual Cash Flows
Interval 0.5% Interval 1000
8.0% 6 25000 6
8.5% 11% 26000 31000
9.0% 27000
9.5% 28000
10.0% 29000
10.5% 30000
11.0% 31000
11.5% 32000
12.0% 33000
Contents!A1
Inputs Variables Desired Outcome ( Management Summary)
Cash Outflows (100,000.00)
Annual cash inflows NPV 6,142.03
No. of years 5.00
Minimum desired NPV 5,000.00
Select
Rate of discounting

Calculations
year 0 1 2 3

captial outflows (100,000)


Cash inflows - - -
Total cash flows (100,000) 28,000 28,000 28,000
Discounting factor 1.00 0.91 0.83 0.75
Net Cash Flow (100,000) 25,455 23,140 21,037

Desired Outcome

NPV 6,142.03

Sensitivity
8.0% 8.5% 9.0% 9.5%
6,142.03 11796 10338 8910 7512

NPV Vs Discounting Rate


14000

12000

10000

8000

6000

4000

2000

0
0.08 0.085 0.09 0.095 0.1 0.105 0.11

Row 28
Workings
Discounting Rate Annual Cash Flows
Interval 0.5% Interval 1000
8.0% 4 25000 3
8.5% 10% 26000 28000
9.0% 27000
9.5% 28000
10.0% 29000
10.5% 30000
11.0% 31000
11.5% 32000
12.0% 33000
ome ( Management Summary)

ACCEPT

4 5

- -
28,000 28,000
0.68 0.62
19,124 17,386

10.0% 10.5% 11.0% 11.5% 12.0%


6142 4800 3485 2197 934

NPV Vs Discounting Rate

0.09 0.095 0.1 0.105 0.11 0.115 0.12

Row 28
Contents!A1
Inputs Variables Desired Outcome ( Management Summary)
Cash Outflows (100,000.00)
Annual cash inflows NPV 6,142.03 ACCEPT
No. of years 5.00
Start date/period 1/1/2023
Interval
Minimum desired NPV 5,000.00
Select
Rate of discounting

Calculations
year 1/1/2023 1/1/2024 1/1/2025 1/1/2026 1/1/2027

captial outflows (100,000)


Total cash flows (100,000) 28,000 28,000 28,000 28,000
Cummulative Cash Flow (100,000) (72,000) (44,000) (16,000) 12,000

Desired Outcome

NPV 6,142.03

Dynamic Graph
Select Data 1
Year 1/1/2023 1/1/2024 1/1/2025 1/1/2026 1/1/2027
Total Cash Flows (100,000.00) 28,000.00 28,000.00 28,000.00 28,000.00

60000
40000
20000
0
1
-20000
-40000
-60000
-80000
-100000
-120000
Workings
Discounting Rate Annual Cash Flows
Interval 0.5% Interval 1000
8.0% 4 25000 3
8.5% 10% 26000 28000
9.0% 27000
9.5% 28000
10.0% 29000
10.5% 30000
11.0% 31000
11.5% 32000
12.0% 33000
Contents!A1
gement Summary)

1/1/2028

28,000
40,000

1/1/2028
28,000.00
Options for graphs
1 Total Cash Flow
2 cummulative graph

Frequency 4 Option to be chosen


Monthly 12 1 No of times frequency of cash flows
Quarterly 4 12 Period for which cash flows calculated
Bi-Annual 2
Annual 1

You might also like