Help with forecasting budget vs actuals

Hi all.

I’d really appreciate a little bit of help if possible, please, as I'm stuck!

See my example spreadsheet attached.

I’m doing a yearly product sales forecast where for each month of the year I have a budget set and then an actual total achieved.

I’d like to populate the small simple table cells on row 28 so that I can create a chart, but for each month state if it should calculate the budget total or the actual Toal (both shown in row 27 against each month) - I hope that make sense.

So you can see April to November I’d like to use Actual totals, but for December to March use the Budget totals.

Is there a way to do this please?

I then have a second question - is it possible that the cells in row 27 automatically change based on the month we’re in. So November and all before will show Actual, but December onwards will show Budget. When 1st December then starts, the cell for December changes from Budget to Actual.

Many thanks, really appreciate the help.

Hi dnbt1980.
I would organice your data differently. More in a Database format like in the example in the attachment (page "Data").
Then if you need any totals, averages, differences, etc. its easier to get you results.
You can for example use dynamic tables like in the page "Dynamic tables", to get yearly or quarterly totals.
Or you can get some totals per month with simple formulas to build a chart, like in page "Chart". Here the column "Forecast/Actual" changes as you need based on the current month.
Hope this helps.
If organizing your data differently isn't and option, tell us and we can try another approach.

Hi felixstraube.

Thanks so much for helping on this.

organising the data differently is not an option, so was hoping a formula would work in the structure I have in my example.

Thanks again.

Hi alansidman.

Thanks so much for your help with this, that's brilliant.

I wasn't aware of Power Query's so will 100% do some research and get to know them.

Thanks so much again.