Simplifying 30 IF nested formula for purposes of forecasting with overlays

[ExcellingOcfUpsides]

Hi guys.

First time poster, would REALLY appreciate some help with a formula i'm trying to write. First off appreciate if this is too large a task, simple guidance of where to self research would also be appreciated.

What I'm trying to do is create a 5 year operating model for a business which uses existing data load as a basis point for year to date months and then applies a flex of some sort to the remaining months.

[[EDIT - REMOVED FORMULA - ATTACHED WORKBOOK BELOW INSTEAD]]]

what i am trying to make it into is something like this setup:

in this set up there are 4 base scenarios (rather than 20) - where future months are either existing data (HFM) load, run rate of year to date, run rate of prior month or a flat input.

i am hoping to do the flexing using switches (drop down choices) in the input columns.

ideally the formula would allow me to e.g. do a 5% rise in month 6, either: run rate (same % increase), one-off (no increase on base scenario next month), flat phased (next month is equal to prior month) THEN do a further incremental increase in month X & Y (timing 2 & 3).

my initial thoughts for the rework have me at an IF formula for 4 basic scenarios and then i think i need to multiply that function by another formula which works off the timing switches. however unfortunately i've only gotten it to work on the specific month. e.g. putting through individual increases in month 6, 9, 11 rather than having those rises being run rated / flat phased if needed

Many thanks for any help, suggestions & recommendations

[AliGW]

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a description of one.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[ExcellingOcfUpsides]

without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

thank you for your reply.

i've attached a sample work book.

essentially the point is to flex the future months (base data HFM tab). these then drive the calculations of a seperate work sheet (not included)
in my original attempt (assumptions tab) i had ~ 20 scenarios which id chose from then do the timing & the % and or abs +-.
now im trying to get it down to 4 base scenarios and then have all the rest be done from the drop downs.

ive colour coded to hopefully help illustrate.

primarily using absolute figures but sometimes % too. not as important to make this work for % data.

focusing on flexing by % +- but if can work in an absolute that would be great. problem is i think it would require either separate column or crazy formula.
e.g. its one thing to do it all by IF(....)*(1+x%)...but making it a +-abs? im too n00b for that

someone suggested using Case statements

https://www.techonthenet.com/excel/formulas/case.php

is this a good idea for this situation?

EDIT: just wanted to say that i THINK i've managed to solve my initial problem but it was only done by recreating a same IF function formula within the main one to basically say: if month is > than previous month input & less than next input month AND its not one off, continue the flexing by * by cell X.

EDIT2: just wanted to say that managed to figure everything out and get it working in a way that was even better than my previous attempt...although AGAIN...this feels really awkward formula wise and im sure not the best solution....i feel like i need to learn some VBA? although at this point i only need to add maybe a 4th input group and then id have much more detial than id ever need...but guess good to learn?