+ Reply to Thread
Results 1 to 3 of 3

Scenario Analysis with inputs/outputs across spreadsheets

  1. #1
    Registered User
    Join Date
    10-14-2008
    Location
    South Africa
    MS-Off Ver
    Office 2010
    Posts
    67

    Scenario Analysis with inputs/outputs across spreadsheets

    Hi friends,

    Does anyone know of a way to do scenario/sensitivity analysis with input parameters spread across multiple workbooks?
    I have two workbooks with 27 tightly coupled worksheets and have multiple inputs spread over them. Is there any way I can do a "what-if" or "scenario analysis" without collating all the inputs and calculations of the 27 worksheets on one worksheet? All examples I have seen use information contained on one worksheet?

    I have been searching the internet for some guidance for the last 2 days but were unable to find any solution to this problem. Any guidance of where I can get information on this?

    Regards

    Dan

  2. #2
    Registered User
    Join Date
    07-31-2012
    Location
    Germany
    MS-Off Ver
    Excel 2010
    Posts
    2

    Thumbs up Re: Scenario Analysis with inputs/outputs across spreadsheets

    Hello Dan,

    as far as I know, the built in Excel "what-if" functions are indeed only usable within a single worksheet .
    I came across the same problem and have written a free Add-In macro, which allows for cross worksheet manipulation of input cells and tracking of the respective result in output cells.
    The macro allows the manipulation of up to 20 input cells and concurrently tracking of up to 20 output results. That is, you can click and select the respective cells and also state the percentage range for the input variation. Your "27 tightly coupled worksheets" are therefore no longer a problem, since the input and output cell selection can span as many worksheets as you want as long as you stay within the same Excel file!

    http://www.life-cycle-costing.de/sensitivity_analysis/

    Feel free to reuse my work and give us a feedback on how you got on.

    Hope that helps,
    Thomas

  3. #3
    Registered User
    Join Date
    10-14-2008
    Location
    South Africa
    MS-Off Ver
    Office 2010
    Posts
    67

    Re: Scenario Analysis with inputs/outputs across spreadsheets

    Hi Thomas,

    Thanks for the reply! I thought I would never get any! I will definitely try your macro.

    I tried some commercial software on a trial basis (which doesn't come cheap) and this also had a lot of limitations which their programmers will now apparently address.

    Will let you know of the outcome of the testing of your macro.

    Dan

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1