KEMBAR78
Sensitivity Analysis Excel Template | PDF | Net Present Value | Trade
Scribd
Upload
700 views5 pages

Sensitivity Analysis Excel Template

The document provides an expected cash flow forecast over 12 years for a project with an investment of $5,400 in year 0, annual sales of $16,000, costs of $13,000 and $2,000, and depreciation of $450. It calculates the net present value of $470.25 using a cost of capital of 8%. It also outlines possible pessimistic, expected, and optimistic outcomes by varying the investment, sales, and costs.

Uploaded by

Cele Mthoko
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
700 views5 pages

Sensitivity Analysis Excel Template

The document provides an expected cash flow forecast over 12 years for a project with an investment of $5,400 in year 0, annual sales of $16,000, costs of $13,000 and $2,000, and depreciation of $450. It calculates the net present value of $470.25 using a cost of capital of 8%. It also outlines possible pessimistic, expected, and optimistic outcomes by varying the investment, sales, and costs.

Uploaded by

Cele Mthoko
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
You are on page 1/ 5

Sensitivity Analysis Excel Template

Visit: www.educba.com
Email: info@educba.com
The expected Cash Flow forecast for the next 12 years is provided (see below). The cost of capital is 8% ,
assuming the variables remain constant, determine the Net Present Value (NPV) of the project.

0th Year- During the 1st year of Investment Years 1st - 12th
Investment ($5,400.00)
Sales $16,000.00
Variable Costs $13,000.00
Fixed Costs $2,000.00
Depreciation $450.00
Pretax Profit $550.00
Taxes@40% $220.00
Profit After Tax $330.00
Operating Cash flow $780.00

Net Cash Flow ($5,400.00) $780.00


NPV $470.25

Possible Outcomes

Variable Pessimistic Expected


Investment 5800 5400
Sales 14000 16000
Variable Costs 11620 which is 83% of Sales 13000 which is 80.25% of Sales
Fixed Costs 2100 2000
NPV ($121.00) $470.25
Optimistic
5000
18000
14400 which is 80% of Sales
1900
$778.00
Example of J&B Inc. The original or expected Sales Volume is $582,401 arising out of 7882 units and at the rate of $73.89.To
conducted two models with different input variables for the Pessimistic Model and the Optimistic Model, as seen highlighte

Pessimistic Expected
20% Decrease in Sales Original Sales
Sales:
Units $6,305.60 $7,882.00
Selling Price per Unit $73.89 $73.89
Total Sales $465,920.78 $582,400.98
Variable Costs:
Cost of Goods Sold $104,152.80 $130,191.00
Fixed Costs (Operating Costs) :
Advertising Expense  $130,000.00 $130,000.00
Wages & Employee Benefits  $122,366.00 $122,366.00
Casual Labor  $2,400.00 $2,400.00
Office Supplies  $1,500.00 $1,500.00
Rent Expense  $12,000.00 $12,000.00
Telephone/Fax Expense  $3,600.00 $3,600.00
Professional Services  $7,000.00 $7,000.00
Insurance Expenses  $1,500.00 $1,500.00
Toll-free above Variable  $15,685.00 $15,685.00
Bad Debt Expense  $5,824.00 $5,824.00
Interest on Operating Loan  $2,000.00 $2,000.00
Internet Storage & Accounts  $2,550.00 $2,550.00
Miscellaneous Expenses  $2,400.00 $2,400.00
Depreciation Exp. - Equipment  $3,142.00 $3,142.00
Depreciation Exp.- Furniture  $606.00 $606.00
Amortization of Initial R&D Costs  $15,924.00 15924
Amortization of Future R&D Costs  $24,720.00 $24,720.00
Total Fixed Costs $353,217.00 $353,217.00
Total Costs ( Variable & Fixed) $457,369.80 $483,408.00
Net Income Before Taxes $8,550.98 $98,992.98
Less: Estimated Tax Payable @ 30% $2,565.30 $29,697.89
Net Income After Taxes $5,985.69 $69,295.09
of 7882 units and at the rate of $73.89.To conduct the sensitivity analysis – J&B Inc
d the Optimistic Model, as seen highlighted below:

Optimistic
15 % Increase in Sales

$9,064.30
$73.89
$669,761.13

$149,719.65

$130,000.00
$122,366.00
$2,400.00
$1,500.00
$12,000.00
$3,600.00
$7,000.00
$1,500.00
$15,685.00
$5,824.00
$2,000.00
$2,550.00
$2,400.00
$3,142.00
$606.00
$15,924.00
$24,720.00
$353,217.00
$502,936.65
$166,824.48
$50,047.34
$116,777.13

You might also like