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?
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 |