• 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.

What If Analysis: Scenario Manager, Goal Seek or Data table??

Proteus

New Member
Hello,

I have attached a case problem that I am trying to figure out the best way to approach it.
Basically I am being asked to create a What If scenario (a monthly financial tool) that allows to test or change profit margins or sales volume. This scenario should allow also time parameter as it asks that expected margin is reached within 24 or 36 months depending on the product. I understand the problem however I am not sure what is the best way to present it. Does it need to be a what if scenario that allows changes to profit margin % and time in months? Or should I show a monthly data table that portrays monthly values by column with the respective cost, sales and margin? It is obviously more complex as there a couple more variables. Including the fact that in the problem the company expect to make the sales of the chairs within the 1st year.
Any input on which is the best way to present this via what if analysis is appreciated.

Chair Input Cells
Total Cost $ 200,000.00
Expected Profit $ 100,000.00
Profit Margin 33.33%
Term(months) 12
Sales % 80%
Qty 20000
 

Attachments

  • Capture.JPG
    Capture.JPG
    118.7 KB · Views: 27
Hello,

I have attached a case problem that I am trying to figure out the best way to approach it.
...

You do not need any of those to create monthly forecast for next few years. You can set up a simple model and calculate the cash flows.

However, if there is more to the problem (for example, Warren's Furniture wants to visualize monthly profit flow for 3 scenarios in same graph - one explained in the case, one with GP of 60,000 for chairs, one with 120,000) then you can use data tables to create the projections in one go (alternatively, you can also calculate them using simple formulas)

Goal seek is not necessary in this model. It is used when you need to figure out one variable value based on a set of other variables. Think of this as solving an equation for X.

Scenario manager is useful if you wish to save combinations of input variables and recall them one at a time to see output values.

I have attached a simplified model using just formulas. Play with it to understand how you can solve this problem.

All the best.
 

Attachments

  • chairs-tables.xlsx
    13.3 KB · Views: 19
Thank you very much for your reply r2c2. Indeed I have tried all 3 alternatives within What if Analysis tool however none of them allowed for what I had envisioned to resolved this problem. Your approach does include the basic requirements to solve the equation and portrays a monthly profit forecast. I went ahead and created an interactive tool that allows what if inputs and in turn shows desired results according to the 3 main variables.
Once again I thank you for your support
 
Back
Top