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:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD] Income Statement[/TD]
[TD]Forecast[/TD]
[/TR]
[TR]
[TD]NET SALES[/TD]
[TD]4907,7[/TD]
[/TR]
[TR]
[TD] Number of Stores[/TD]
[TD]368[/TD]
[/TR]
[TR]
[TD] Net Sales per Store[/TD]
[TD]13,3[/TD]
[/TR]
[TR]
[TD]Cost of Good Sold[/TD]
[TD]-1976,4[/TD]
[/TR]
[TR]
[TD]GROSS PROFIT[/TD]
[TD]2931,1[/TD]
[/TR]
[TR]
[TD]Selling expenses[/TD]
[TD]2406,14[/TD]
[/TR]
[TR]
[TD]Administrative expenses[/TD]
[TD]-217,30[/TD]
[/TR]
[TR]
[TD]OPERATING PROFIT[/TD]
[TD]307,64[/TD]
[/TR]
</tbody>[/TABLE]
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[TABLE="width: 432"]
<tbody>[TR]
[TD="class: xl66, width: 72"]
307,64[/TD]
[TD="class: xl66, width: 72"]10,64[/TD]
[TD="class: xl66, width: 72"]11,97[/TD]
[TD="class: xl66, width: 72"]13,30[/TD]
[TD="class: xl66, width: 72"]14,63[/TD]
[TD="class: xl66, width: 72"]15,96[/TD]
[/TR]
[TR]
[TD="class: xl66"]294[/TD]
[TD="class: xl66"]-1467,37[/TD]
[TD="class: xl66"]-1075,82[/TD]
[TD="class: xl66"]-684,27[/TD]
[TD="class: xl66"]-292,72[/TD]
[TD="class: xl66"]98,83[/TD]
[/TR]
[TR]
[TD="class: xl66"]331[/TD]
[TD="class: xl66"]-1075,82[/TD]
[TD="class: xl66"]-635,33[/TD]
[TD="class: xl66"]-194,83[/TD]
[TD="class: xl66"]245,67[/TD]
[TD="class: xl66"]686,16[/TD]
[/TR]
[TR]
[TD="class: xl66"]368[/TD]
[TD="class: xl66"]-684,27[/TD]
[TD="class: xl66"]-194,83[/TD]
[TD="class: xl66"]294,61[/TD]
[TD="class: xl66"]784,05[/TD]
[TD="class: xl66"]1273,49[/TD]
[/TR]
[TR]
[TD="class: xl66"]404[/TD]
[TD="class: xl66"]-292,72[/TD]
[TD="class: xl66"]245,67[/TD]
[TD="class: xl66"]784,05[/TD]
[TD="class: xl66"]1322,43[/TD]
[TD="class: xl66"]1860,82[/TD]
[/TR]
[TR]
[TD="class: xl66"]441[/TD]
[TD="class: xl66"]98,83[/TD]
[TD="class: xl66"]686,16[/TD]
[TD="class: xl66"]1273,49[/TD]
[TD="class: xl66"]1860,82[/TD]
[TD="class: xl66"]2448,15[/TD]
[/TR]
</tbody>[/TABLE]
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:
[TABLE="width: 144"]
<tbody>[TR]
[TD="class: xl68, width: 72"]Change[/TD]
[TD="class: xl69, width: 72"] EBIT[/TD]
[/TR]
[TR]
[TD="class: xl72"]0%[/TD]
[TD="class: xl73"]307,64[/TD]
[/TR]
[TR]
[TD="class: xl70"]10%[/TD]
[TD="class: xl66"]307,64[/TD]
[/TR]
[TR]
[TD="class: xl71"]-10%[/TD]
[TD="class: xl67"]307,64[/TD]
[/TR]
</tbody>[/TABLE]
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
I have two difficulties to finalize my Sensitivity Analysis and hope that someone can help me out.
This is my simplified basis table:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD] Income Statement[/TD]
[TD]Forecast[/TD]
[/TR]
[TR]
[TD]NET SALES[/TD]
[TD]4907,7[/TD]
[/TR]
[TR]
[TD] Number of Stores[/TD]
[TD]368[/TD]
[/TR]
[TR]
[TD] Net Sales per Store[/TD]
[TD]13,3[/TD]
[/TR]
[TR]
[TD]Cost of Good Sold[/TD]
[TD]-1976,4[/TD]
[/TR]
[TR]
[TD]GROSS PROFIT[/TD]
[TD]2931,1[/TD]
[/TR]
[TR]
[TD]Selling expenses[/TD]
[TD]2406,14[/TD]
[/TR]
[TR]
[TD]Administrative expenses[/TD]
[TD]-217,30[/TD]
[/TR]
[TR]
[TD]OPERATING PROFIT[/TD]
[TD]307,64[/TD]
[/TR]
</tbody>[/TABLE]
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[TABLE="width: 432"]
<tbody>[TR]
[TD="class: xl66, width: 72"]
307,64[/TD]
[TD="class: xl66, width: 72"]10,64[/TD]
[TD="class: xl66, width: 72"]11,97[/TD]
[TD="class: xl66, width: 72"]13,30[/TD]
[TD="class: xl66, width: 72"]14,63[/TD]
[TD="class: xl66, width: 72"]15,96[/TD]
[/TR]
[TR]
[TD="class: xl66"]294[/TD]
[TD="class: xl66"]-1467,37[/TD]
[TD="class: xl66"]-1075,82[/TD]
[TD="class: xl66"]-684,27[/TD]
[TD="class: xl66"]-292,72[/TD]
[TD="class: xl66"]98,83[/TD]
[/TR]
[TR]
[TD="class: xl66"]331[/TD]
[TD="class: xl66"]-1075,82[/TD]
[TD="class: xl66"]-635,33[/TD]
[TD="class: xl66"]-194,83[/TD]
[TD="class: xl66"]245,67[/TD]
[TD="class: xl66"]686,16[/TD]
[/TR]
[TR]
[TD="class: xl66"]368[/TD]
[TD="class: xl66"]-684,27[/TD]
[TD="class: xl66"]-194,83[/TD]
[TD="class: xl66"]294,61[/TD]
[TD="class: xl66"]784,05[/TD]
[TD="class: xl66"]1273,49[/TD]
[/TR]
[TR]
[TD="class: xl66"]404[/TD]
[TD="class: xl66"]-292,72[/TD]
[TD="class: xl66"]245,67[/TD]
[TD="class: xl66"]784,05[/TD]
[TD="class: xl66"]1322,43[/TD]
[TD="class: xl66"]1860,82[/TD]
[/TR]
[TR]
[TD="class: xl66"]441[/TD]
[TD="class: xl66"]98,83[/TD]
[TD="class: xl66"]686,16[/TD]
[TD="class: xl66"]1273,49[/TD]
[TD="class: xl66"]1860,82[/TD]
[TD="class: xl66"]2448,15[/TD]
[/TR]
</tbody>[/TABLE]
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:
[TABLE="width: 144"]
<tbody>[TR]
[TD="class: xl68, width: 72"]Change[/TD]
[TD="class: xl69, width: 72"] EBIT[/TD]
[/TR]
[TR]
[TD="class: xl72"]0%[/TD]
[TD="class: xl73"]307,64[/TD]
[/TR]
[TR]
[TD="class: xl70"]10%[/TD]
[TD="class: xl66"]307,64[/TD]
[/TR]
[TR]
[TD="class: xl71"]-10%[/TD]
[TD="class: xl67"]307,64[/TD]
[/TR]
</tbody>[/TABLE]
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
