What-if analysis

Krayna

New Member
Joined
Feb 2, 2021
Messages
33
Office Version
  1. 365
Platform
  1. Windows
I'm planning a spreadsheet to be taught to spreadsheets of an advanced course. It must demonstrate particular skills and functions.
I am struggling to add evidence of what-if analysis.
Would anyone have an idea how I can do this to predict August income?

Sample.xlsm
ABCDE
1Budget Analysis 2021
2
3MonthExpenseGross IncomeNet IncomeProfit/Loss
4January£12,598.00£8,804.00£1,485.00Below Average
5February£12,588.00£10,747.00£3,493.00Above Average
6March£12,738.00£10,860.00£3,651.00Above Average
7April£12,962.00£9,901.00£2,440.00Below Average
8May£12,828.00£9,208.00£1,994.00Below Average
9June£12,562.00£9,346.00£2,035.00Below Average
10July£12,958.00£10,180.00£3,701.00Above Average
11August£0.00£0.00£0.00Below Average
12September£0.00£0.00£0.00Below Average
13October£0.00£0.00£0.00Below Average
14November£0.00£0.00£0.00Below Average
15December£0.00£0.00£0.00Below Average
16GRAND TOTAL£89,234.00£69,046.00£18,799.00
17£0.00
18PREDICTION
19Average Monthly Net Income£2,685.57
20Yearly Revenue Goal£32,226.86
21Income Required£13,427.86
22Monthly Expected Income£0.00
23
Budget Analysis
Cell Formulas
RangeFormula
B4:B15B4=TRANSPOSE('Monthly Expenses Summary'!D29:O29)
C4:C15C4=TRANSPOSE('Monthly Income Summary'!D21:O21)
E4:E15E4=IF(D4>$B$19, "Above Average", "Below Average")
B16:D16B16=SUM(B4:B15)
D4D4=January!$F$56
D5D5=February!$F$56
D6D6=March!$F$56
D7D7=April!$F$56
D8D8=May!$F$56
D9D9=June!$F$56
D10D10=July!$F$56
D11D11=August!$F$56
D12D12=September!$F$56
D13D13=October!$F$56
D14D14=November!$F$56
D15D15=December!$F$56
D17D17=SUM(D11:D15)
B19B19=AVERAGEIF($D$4:$D$15,"<>0",$D$4:$D$15)
B20B20=(B19)*12
B21B21=B20-SUM(D4:D10)
B22B22=AVERAGE(D11:D15)
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E4:E15Cell Value="Above Average"textNO
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
Forecasting using trends is not what I would call What-If. Why don't you aim for something that is more like ... IF you choose this, then this is WHAT happens ... especially for something like a Data Table (one var or two var), where you specify all the IFs and the table generates WHAT that equates to in each case.
 

Krayna

New Member
Joined
Feb 2, 2021
Messages
33
Office Version
  1. 365
Platform
  1. Windows
It seems that what-if analysis only works when changing cell is actual value - but on my sheet all are referencing previous sheets.
I've enclosed workbook here - any ideas where I can add in this skill??
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
You said "I'm planning a spreadsheet to be taught to spreadsheets of an advanced course. It must demonstrate particular skills and functions.
I am struggling to add evidence of what-if analysis." ... but you seem to be intent on using something that does not appear to be What-If analysis. Start from scratch and make something that demonstrates What-If ... like a 2 value Table.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,117
Messages
5,640,206
Members
417,131
Latest member
Seanr19871

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top