What-if analysis

[Krayna]

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
	A	B	C	D	E
1	Budget Analysis 2021
2
3	Month	Expense	Gross Income	Net Income	Profit/Loss
4	January	£12,598.00	£8,804.00	£1,485.00	Below Average
5	February	£12,588.00	£10,747.00	£3,493.00	Above Average
6	March	£12,738.00	£10,860.00	£3,651.00	Above Average
7	April	£12,962.00	£9,901.00	£2,440.00	Below Average
8	May	£12,828.00	£9,208.00	£1,994.00	Below Average
9	June	£12,562.00	£9,346.00	£2,035.00	Below Average
10	July	£12,958.00	£10,180.00	£3,701.00	Above Average
11	August	£0.00	£0.00	£0.00	Below Average
12	September	£0.00	£0.00	£0.00	Below Average
13	October	£0.00	£0.00	£0.00	Below Average
14	November	£0.00	£0.00	£0.00	Below Average
15	December	£0.00	£0.00	£0.00	Below Average
16	GRAND TOTAL	£89,234.00	£69,046.00	£18,799.00
17				£0.00
18	PREDICTION
19	Average Monthly Net Income	£2,685.57
20	Yearly Revenue Goal	£32,226.86
21	Income Required	£13,427.86
22	Monthly Expected Income	£0.00
23
Budget Analysis

Cell Formulas
Range	Formula
B4:B15	B4	=TRANSPOSE('Monthly Expenses Summary'!D29:O29)
C4:C15	C4	=TRANSPOSE('Monthly Income Summary'!D21:O21)
E4:E15	E4	=IF(D4>$B$19, "Above Average", "Below Average")
B16:D16	B16	=SUM(B4:B15)
D4	D4	=January!$F$56
D5	D5	=February!$F$56
D6	D6	=March!$F$56
D7	D7	=April!$F$56
D8	D8	=May!$F$56
D9	D9	=June!$F$56
D10	D10	=July!$F$56
D11	D11	=August!$F$56
D12	D12	=September!$F$56
D13	D13	=October!$F$56
D14	D14	=November!$F$56
D15	D15	=December!$F$56
D17	D17	=SUM(D11:D15)
B19	B19	=AVERAGEIF($D$4:$D$15,"<>0",$D$4:$D$15)
B20	B20	=(B19)*12
B21	B21	=B20-SUM(D4:D10)
B22	B22	=AVERAGE(D11:D15)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E4:E15	Cell Value	="Above Average"	text	NO

 

[GlennUK]

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]

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]

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.