# What-if analysis

#### Krayna

##### New Member
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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

#### GlennUK

##### Well-known Member
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
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
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.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,258
Messages
5,836,281
Members
430,414
Latest member
ayla

### 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.

### Which adblocker are you using?

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

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