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

  • one Incentive Budget worksheet for each of my key accounts +
  • one Summary worksheet, showing the key metrics of each accounts' Budget at a glance

Current set up is: 

  • Individual account’s Incentive Budget includes up to 6 tiers of Revenue Growth Targets
  • Each Budget has 4 Incentive components
    • The %/$ payable for some/all of these components varies by account and
    • The %/$ payable for some/all of these components varies by Revenue Growth tier
  • Total Incentive payable to an account will depend on the Revenue Growth tier they achieve.
  • I need to ensure the total Incentive money available to 'spend', is weighted toward my top (revenue generating) accounts over the less productive ones.
  • Numbers from the individual budget worksheets map through to the matching summary worksheet fields

I'm wanting to see how my overall Incentive 'spend' is affected, when changing one (or more) of the Incentive components of one (or more) accounts. Clearly I can currently make the change(s) in each individual account's budget then jump over to the Summary tab to see the impact but ideally, I'd like to manipulate the variable(s) AND see the overall impact to my total budgeted spend immediately in the one place.

 

I looked at Scenario Manager (not used it before) however not sure this does what I need ... the summary worksheet is essentially a series of formulas. 

The only thing I can think of is to reverse what I've done and enter/change all variables on the summary sheet, mapping them back to the relevant fields on the individual account budgets using formulas.

 

I'm not so advanced in Excel so feel there is likely a more clever and efficient way to do this so any suggestions would be hugely appreciated. Many thanks. 

9 Replies

  • wumolad's avatar
    wumolad
    Iron Contributor

    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.

Resources