Sensitivity Analysis

[Ellap]

Hey everyone,

I have two difficulties to finalize my Sensitivity Analysis and hope that someone can help me out.

This is my simplified basis table:

Income Statement	Forecast
NET SALES	4907,7
Number of Stores	368
Net Sales per Store	13,3
Cost of Good Sold	-1976,4
GROSS PROFIT	2931,1
Selling expenses	2406,14
Administrative expenses	-217,30
OPERATING PROFIT	307,64

<tbody>
</tbody>

1) My plan was to create a 2-dimensional table by using the "What-if-Analyis". So I create a table with a variety of "Sales per Store" in the top row. And a variety of "Numbers of Stores" on the left column. Then I linked the Operating Profit (307,64) to the upper left cell, marked the whole table and chose the "What-if-Analysis". Then I was asked to link the "Row Input Cell" (= 13,3 from the basis table) and the "Column input cell" (= 368 from the basis table).

The result looks as follows:

Sales per Store

307,64	10,64	11,97	13,30	14,63	15,96
294	-1467,37	-1075,82	-684,27	-292,72	98,83
331	-1075,82	-635,33	-194,83	245,67	686,16
368	-684,27	-194,83	294,61	784,05	1273,49
404	-292,72	245,67	784,05	1322,43	1860,82
441	98,83	686,16	1273,49	1860,82	2448,15

<tbody>
</tbody>

Now I am wondering what went wrong. Usually, the middle line should show my base case from the table above which means: 368 to 13,30 = 307,64. But instead it shows 294,61.

The basis table is very easy linked: Net Sales = Sales per Store * Number of Stores
EBIT = Net Sales - COGS - Selling expenses - Adm. expenses

2) In addition to the 2-deminsional chart I was planning to make an analysis that shows the change in EBIT in accordance with changes in net sales and cost of goods sold (+/- 10%). Therefore, I prepared another chart with two columns, "Change" and "EBIT". The "Change" has three rows and shows 0% / 10% / -10% ; the "EBIT" columns stays empty for the time being. Then I added in the basis table "+0%" to the cell which shows the net sales. As result the line shows "=4907,7+0%". The next step is to link the EBIT of the base case to the "EBIT" column, in the 0% row. Then i select the area of whole table and choose the "What-if-Analysis". In this case, I only need the column to be filled. Therefore, I dont fill anything in when Excel asks me for the "Row Input Cell" but I do fill in the "Column Input Cell" by choosing the 0% cell (which indicated the column which the percent data).

But instead of calculating the outcome of a change in net sales of +/- 10 % Excel calculated the same EBIT for all cases. The result looks as follows:

Change	EBIT
0%	307,64
10%	307,64
-10%	307,64

<tbody>
</tbody>

The bold numer is correct. But the others are obviously not. Now my question: How can that be? What did I wrong?

Thank you very much in advance! I hope my explanation is understandable, otherwise, please ask for more details!

Ellap

 

[pgc01]

Hi
Welcome to the board

I'd check the value 13,3 in the model table. Maybe the 13,3 displayed is not its real value but an approximation, for ex. 13,265 displayed with just 1 decimal.
If that's the case, the 13,30 that you use in your what-if table would not be the same value as the one in the model and that explains the difference.

To see a better approximation of the 13,3 value in the model table format the cell with 3 or more decimals.

 

[Ellap]

13,3 is real. So that's not the issue I am afraid...