Forum Discussion

mexicarla's avatar
mexicarla
Copper Contributor
Apr 23, 2020

Scenario manager alternatives?

I have a National Incentive budget from Head Office, against which I have to calculate my projected 'spend' on growth incentives for my key sales accounts. I created an Excel workbook which contains...
excel
Formulas and Functions
wumolad's avatar
wumolad
Iron Contributor
Apr 23, 2020

mexicarla Can you send an excel sheet containing some fictitious data to follow your explanation? Not sure I understand your points. Seeing the datasheet will make it easier to understand.

mexicarla's avatar
mexicarla
Copper Contributor
Apr 23, 2020

wumolad Absolutely!  Give me a little time to make my details anonymous. Thanks for the quick reply!!!!

    • mexicarla's avatar
      mexicarla
      Copper Contributor
      Apr 24, 2020

      wumolad Here we go. Have slimmed it down to 3 'customers' ... you'll get the idea I'm sure. 

      No hidden tabs but there are some named ranges for which you won't be able to get a list I'm afraid.

      Any suggestions/feedback is welcomed. Many thanks. 

      • wumolad's avatar
        wumolad
        Iron Contributor
        Apr 24, 2020

        mexicarla 

        My suggestion will be to use dropdown list in your formula by inserting from developer tab.

         

         I have put an example on Customer B sheet. This will reduce the so many "if functions" used on Target sales 2020 section on column E having this formula:

         

        =IF($B$11="Tier 1",C14*TargetRevGrowthPC_Tier1,IF($B$11="Tier 2", C14*TargetRevGrowthPC_Tier2,IF($B$11="Tier 3", C14*TargetRevGrowthPC_Tier3,IF($B$11="Tier 4", C14*TargetRevGrowthPC_Tier4,IF($B$11="Tier 5", C14*TargetRevGrowthPC_Tier5,IF($B$11="Tier 6", C14*TargetRevGrowthPC_Tier6))))))

         

        which now shows only:

         

        =INDEX($B$5:$B$9,B32,1)*C14

         

        as seen in cell F34.

         

        You can also copy this dropdown list to any part of the workbook and they are synched together in which case you can make changes from the summary tab and see the impact of changes immediately.

         

        Let me know if you need more clarification on using dropdown.

Resources