1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

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


  3. 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??

Discussion in 'Ask an Excel Question' started by Proteus, Apr 1, 2017.

  1. Proteus

    Proteus New Member


    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

    Attached Files:

  2. r2c2

    r2c2 Member

    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.

    Attached Files:

    Thomas Kuriakose likes this.
  3. Proteus

    Proteus New Member

    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

Share This Page