• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help with Scenario development

keasha

Member
hello and thankyou in advance for any help you might be able to give me.

i have to create scenarios for a fixed level of revenue based on 5 levels or type of expense. each of those levels has a base cost. however those 5 levels of expense type (see attached) will have one type of variable cost attached to it (there are three types of variable cost)

how do i go about setting something like this up please?
 

Attachments

  • scenariocosts.xlsx
    48.6 KB · Views: 3
Where do you variable and fixed costs in your scenario tab feed to?

What outputs do you want to monitor?
 
Where do you variable and fixed costs in your scenario tab feed to?

What outputs do you want to monitor?
Hi Hui

thankyou for getting back to me. I have not got them feeding anywhere at present.

what i need to do is say for a given level of revenue eg 900k, i can have x many combinations of levels one to five with x many combinations of the variables. so for example, a level 2 has a base cost of $1535 to develop the content on that level of clinical webpage, but it may in have a variable cost of either of the following in developing the referral webpage component.
CWG $ 1,684
SME $ 120
Referrral Page - Brief $ 501
Referrral Page - Moderate $ 8,058
Referrral Page - Intensive $ 12,584
how would i do that please?

you can see the break up of the costs within the level types and the referral types
 
see attached file

Setup one page as a model
setup one page as a table of inputs to the model (I have already done this but you can alter as required
Link the model to the Selected Scenario line
then post the file back here
 

Attachments

  • scenariocosts.xlsx
    50 KB · Views: 8
see attached file

Setup one page as a model
setup one page as a table of inputs to the model (I have already done this but you can alter as required
Link the model to the Selected Scenario line
then post the file back here
hi
was this file meant to be different that you attached? my apologies if i have missed something
 
hi
was this file meant to be different that you attached? my apologies if i have missed something
sorry hui i dont think i am quite following what you want me to do - sorry
 

Attachments

  • scenariocosts hui.xlsx
    50.7 KB · Views: 1
Thanx Narayan

Keasha,

I have setup a Table of all scenarios below your table on the level costs Tab
You can change the column layout or add/delete columns as appropriate

Then, can you setup one Tab as the model and link it to level costs Row 87 as appropriate
 
hello

i am so sorry but i dont think this is right? the level can have a choice of any of the variables i dont see how this is doing that? sorry
 
Yes

Setup 2 worksheets

1. With a list of scenario's in a Tabular Format Like I provided add as many columns as you need to, try and keep it logical like I have and allow for expansion, so future scenario's

Scenario's within scenario's - NO
Each is it's own scenario, they may have common fields like Name or Level 1 with different level 2 names

2. Setup a model
This will link to a row below the List of Scenario's above

See your file, absolutely rearranged to suit the above comments

In your model allow for Names, Titles, numbers etc etc we will retrieve these from the scenario's later

Just setup a single generic model for now and setup the Scenario's Table
 

Attachments

  • Scenario Costs 2.xlsx
    20.3 KB · Views: 5
In reagrds to Levels and Sub Levels
consider something Like this:
Remember you can add as many columns as you need and excel can handle 16,384 columns

upload_2017-12-30_16-38-32.png
 
Not all fields, columns, have to have values in all scenarios
That’s how you add stuff that is only relevant to that hose scenarios

Of course the model needs to allow for that

Have a great night
 
Hi there Hui hope you had a lovely New year.

I hope i have done the right thing here, I have added a total Cost column in. I was thinking i should probably break these columns out as the total costs of these 'pages' are primarily based on labour hours (of which there is a finite amount available in any FY- so i need to marry that back to the total labour hours available for that Financial year - hope that makes sense what do you think?
 

Attachments

  • Scenario Costs 2.xlsx
    22.4 KB · Views: 3
Firstly I have simplified the Model
It is now only a few lines

I have added a Data table below the model

The Data table feeds the Scenario Number to the Model
The model retrieves the appropriate values from the scenario Worksheet
calculates the new cash flows and the Data Table summarises it for each scenario

Have a review and come back with questions as I am sure you will
 

Attachments

  • Scenario Costs 2.xlsx
    23.4 KB · Views: 6
Firstly I have simplified the Model
It is now only a few lines

I have added a Data table below the model

The Data table feeds the Scenario Number to the Model
The model retrieves the appropriate values from the scenario Worksheet
calculates the new cash flows and the Data Table summarises it for each scenario

Have a review and come back with questions as I am sure you will
Hi Hui
sorry i dont have much (any) experience with data tables. have been sitting here for an hour or so trying to work out whats going on. how can it generate the mix of scenarios to maximise the amount of income?
 
Keasha

The Data table in your model does the following

1. It feeds the values in Column B into B12
2. The model uses the new scenario number to retrieve the values from the Scenario Table according to the Scenario Number
3. The model recalculates
4. The values of the Revenue, Costs, Income and Pathway and ID are transferred to Row26
5. The values from Row 26 are transferred to the matching Scenario Number
6. The next Scenario No. is fed into B12
7. This is repeated for all values in the Column B of the Data Table

So you end up with a summary of the key inputs and outputs for all the scenario's (The blue area)

Up until now you have never mentioned Maximizing, it has ball been about Scenario's

Maybe you need to explain what you are trying to achieve ?
 
Keasha

The Data table in your model does the following

1. It feeds the values in Column B into B12
2. The model uses the new scenario number to retrieve the values from the Scenario Table according to the Scenario Number
3. The model recalculates
4. The values of the Revenue, Costs, Income and Pathway and ID are transferred to Row26
5. The values from Row 26 are transferred to the matching Scenario Number
6. The next Scenario No. is fed into B12
7. This is repeated for all values in the Column B of the Data Table

So you end up with a summary of the key inputs and outputs for all the scenario's (The blue area)

Up until now you have never mentioned Maximizing, it has ball been about Scenario's

Maybe you need to explain what you are trying to achieve ?
Hi Hui

I am so sorry if I havent been very clear. can i email you tommorow as this is quite complicated and i need to step it all out for you properly. happy to pay you for this .
 
Back
Top