+ Reply to Thread
Results 1 to 8 of 8

Data table and calculation and input on separate sheets

  1. #1
    Registered User
    Join Date
    06-22-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    8

    Data table and calculation and input on separate sheets

    Dear excel community,

    I just finished building a financial model and I am trying to do some sensitivity analysis on gross margins. Basically I would like to see the effect of various levels of pricing on the annual gross margin. As I see it, I need a one dimensional data table where my input is price and output in gross margin. My issue is my pricing input is on a separate assumption sheet and then the gross margin is calculated based on cost data that is flowing in from several sheets to the P&L, which is again a separate sheet from the sensitivity analysis sheet where I want to place the data table. Can anyone suggest a way to accomplish this?

    I searched this forum and google as I assumed this would be a fairly common issue but surprisingly I could not find a solution. Help!!!!

    Regards,
    Aman

  2. #2
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,803

    Re: Data table and calculation and input on separate sheets

    Not sure I understand. Maybe if you attached the file (or example), it may make more sense.

  3. #3
    Registered User
    Join Date
    06-22-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Data table and calculation and input on separate sheets

    Greg, See the attached file. Basically, I'd like the price sensitivity analysis on the first tab. The pricing is on the second tab, and the margin (which is the output in the data table), is on the third tab. Note that the contribution margin is calculated based on data that is pulled from several sheets.

    Thanks in advance for your help
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,803

    Re: Data table and calculation and input on separate sheets

    Sorry, still unclear on what you are needing. The sheet you supplied has no formulas in it and I'm not sure exactly what you are wanting. Maybe if you explained it without the domain knowledge (like "sensitivity analysis") and just say that "I want to enter data on the first sheet in cell cell F2, and have it display the result of dividing what's in cell B4 on sheet 3 by the corresponding value in column F in sheet 2" (or something like that.

  5. #5
    Registered User
    Join Date
    06-22-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Data table and calculation and input on separate sheets

    Greg,

    See attached with formulas. I put in the commentary about what exactly I need on the first ("Sensitivity Analysis") tab.

    Regards,
    Aman

  6. #6
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,803

    Re: Data table and calculation and input on separate sheets

    I have the answer to your specific question, but I'm sure you're looking for more. You specifically asked how to see the contribution margin in E11 that's from the Contribution Margin tab. Of course that's as simple as entering ='Contribution Margin Summary'!D12 in cell E11. Can you expand on what you're looking for (or is that it)?

  7. #7
    Registered User
    Join Date
    06-22-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Data table and calculation and input on separate sheets

    I actually want the contribution margin in E11 to be a data table output based on price. I just showed the value as an illustration of the output. Basically, I am looking for a data table which will show several values of contribution margins based on the price (input).

  8. #8
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,803

    Re: Data table and calculation and input on separate sheets

    The price is not the only input, is it? Don't you also input the number of users? Or in this scenario, is the price always based on the same # of users (as far as the Sensitivity Analysis sheet goes)? Should the price analysis on the first sheet always be for 1000 users? It also looks like you are calculating the Revenue for the month, but the cost is for the year.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Pulling data from two separate sheets and compiling into a new table
    By TPDave in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-31-2017, 06:04 AM
  2. Replies: 2
    Last Post: 11-10-2015, 12:23 PM
  3. Tables on separate sheets. Want to Display All Data in one Table
    By NoExcelSkills17 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-20-2015, 06:06 PM
  4. [SOLVED] Generating Separate Sheets from Pivot Table Data
    By Suzerain in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-10-2014, 08:03 PM
  5. Input Data/Output data in separate sheets
    By boyboyboy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-09-2013, 12:24 AM
  6. Transfer single column input data to fixed table in a separate Sheet
    By Prokis in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-07-2012, 05:37 AM
  7. Excel 2007 : Separate sheets by input
    By hyun7447 in forum Excel General
    Replies: 0
    Last Post: 09-05-2010, 07:41 PM

Tags for this Thread

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