New project that is completely out of my scope, help me understand!

[nexus]

I have been working at my new finance job for little over a week but the work load has been getting very tough. Especially this one project that is giving me a hard time understanding so I am hoping that someone here can help me understand it better.

My boss made a P&L (profit and loss) excel spreadsheet for FY13 and she wants me to simplify it for FY14 with various different drop down menus added. Now before I get into that, I still need to understand the big picture. The Excel sheet has two "master" sheets titled "Pivot_Master" and "Pivot_FuncDetail". I assume that the information is gathered from those sheets but why is there two?

In addition, there are about 10 different separate slides with headcounts and expenses (operating expensive and cost of goods sold). They all have different expense reports for the different sectors of the business.

I understand the basics of Excel. I understand how Power Pivot and Macros work, along with the formulas. However, I am having a hard time seeing where the data is pulled from. For example, in one of the expense sheets under headcount, this is the formula used for the Research and Development cell:

=IFERROR(GETPIVOTDATA(+$H$9,INDIRECT($H$11),"Fiscal Month",$E$6,"Line Item",$D14,"Profit Center SEC Func Area2", $C14), 0)

Now can someone explain this to me. Where is the Excel sheet pulling the data from? Is it Pivot_Master or Pivot_FuncDetail? Both Pivot sheets go down to about 700 rows with numbers and data and I am having a hard time seeing where they are pulling the information from.

Thank you for your help!

 

[wigi]

Where is the Excel sheet pulling the data from?

Have a look at the second argument in the GETPIVOTDATA function, hence cell H11.
GETPIVOTDATA - Excel - Office.com

 

[nexus]

Have a look at the second argument in the GETPIVOTDATA function, hence cell H11.
GETPIVOTDATA - Excel - Office.com

Thank you for that link. However, I looked at cell H11... it is a blank cell. I don't even understand which Pivot Table it is pulling the data from... Is it Pivot_Master or Pivot_FuncDetail? What is the point of having two pivot sheets?

 

[wigi]

Hello

You will need to provide an example file with fake data and information, but for the rest the same as your file.
For example, through some filesharing website. Then I will have a look at it.

 

[nexus]

Hello

You will need to provide an example file with fake data and information, but for the rest the same as your file.
For example, through some filesharing website. Then I will have a look at it.

Unfortunately, there is too much data for me to insert fake data. However, I figured out where the data was being pulled from. Some of the columns and rows were hidden so I had to unhide it to view it. I have a few more specific questions now.

The formula at the ‘Incentive Compensation’ column and row ‘Actual’ for the Expense Sheet is:

=IFERROR(GETPIVOTDATA(1:1+$H$2,INDIRECT($J$11),"Profit Center SEC Func Area2",$A22,"Class", $B22,"Fiscal Month",$E$6,"Exec Function Summary",$L$1,"Sub Class",$D22,"Group",$E22,"Line Item",$C22), 0)


1. The data field for the GETPIVOTDATA is 1:1+$H$2. What does that mean and what is the point of 1:1? H2 is a cell with the word “Actual” in it with no formula. In that case, is there even a point to do $H$2 instead of just H2?

2. Explain =IFERROR, so if the value is 0, it’s an error?

3. Why the need for an INDIRECT($J$11) function? J11 has the following formula ‘MercPivot_Master!$B$20’ – Why the need for $J$11 and not just J11? Seems like it is just pulling the data from J11 anyway?

4. Why is there an exclamation point between MercPivot_Master and $B$20?

Thank you.

 

[wigi]

Hello

Sorry to say, but most of these questions are basic and you will want to frist get that straight before you delve into (difficult) formulas and pivot tables.

For example,

2. Explain =IFERROR, so if the value is 0, it’s an error?

This says Excel to have 0 as the formula result in case the first part of the formula results in an error.
This information can be obtained in the help files.

Or:

3. Why the need for an INDIRECT($J$11) function? J11 has the following formula ‘MercPivot_Master!$B$20’ – Why the need for $J$11 and not just J11? Seems like it is just pulling the data from J11 anyway?

4. Why is there an exclamation point between MercPivot_Master and $B$20?

$ signs keep the cell references the same when you drag down or accross or you copy the formula.