Need Help in Pivot Tables Case Study: Willing to pay a Fees (file attached)

[Rania Alissa]

Need HELP in CASE STUDY

I am looking for someone who can 1) provide written steps or video to show how to solve the case study of the attached file and 2) Suggest your fees for doing so.
The 1st TAB of the attached file has the data and the 2nd TAB has the table format of the desired output.
Time is of essence, I need the case study in the next 3 days. For more information email me.
The following is the case study:
There will be three financial datasets (Prior Year, Actual Year and Budget of Actual Year). The data will be in months. I would like to create an income statement report from the data. ALL the calculations have to be in PIVOT tables (no macros, no VBA). The report should show variances between 1) Actual vs. prior and 2) Actual vs. budget. The two variances should be performed on a specific month and on YTD of that month. I would like to highlight areas where the expenses are high and provide possible recommendations. The presentation of the report should be professional not looking like a pivot table (more like a statement). So the cascading lines and the indentations of the subtotals are of importance. Finally, that table should be illustrated in the best graph possible that tells the story. The steps should not take more than 30 minutes.

Can You HELP?
https://www.linkedin.com/profile/view?id=174675967&trk=nav_responsive_tab_profile
Rania

 

[NARAYANK991]

Hi Rania ,

It would help if you can mention the Excel version for which this has to be developed.

Narayan

 

[Rania Alissa]

It is Excel version 7.
I am expecting this CAST STUDY to come up in an interview I will be having in the next few days. I need to be prepared to impress the hiring manager and go the extra mile. I really appreciate your help. I am above average in EXCEL and Pivot Tables but I am having difficulty in providing the data in the requested format. The challenging part is that the case study should only be done in PIVOT tables (no macros or VBA) and in no more than 30 minutes.
Can you provide some background about yourself? Are you a financial analyst? Do you work in budgeting?
Looking forward to hearing from you.

 

[Hui]

Hows this

see attached file:

Please check the calculations and especially the summary lines

ps: I answered that before reading your second post about Only Pivot Tables

 

[Rania Alissa]

Hows this
View attachment 19693
see attached file:

Please check the calculations and especially the summary lines

ps: I answered that before reading your second post about Only Pivot Tables

Thanks Hui......you are awesome and super fast. The challenging part is that it has to be done in PIVOT tables ONLY (with some tweaks to original data.
Is it doable ONLY in PIVOT? Please Advise.
Rania

 

[Rania Alissa]

Can you provide a custom video tutorial on how you did the calculations above? I am interested in learning fast and would like to talk to you.
Rania

 

[Rania Alissa]

Hi Hui
Can you expain the SUMPRODUCT formula in Columns D, E and F? What is the "MONTH=Sel_Month"? How does it work? Thanks
Rania

Hows this
View attachment 19693
see attached file:

Please check the calculations and especially the summary lines

ps: I answered that before reading your second post about Only Pivot Tables

 

[Hui]

I rarely use Pivot Tables and so am not best suited to giving this as a solution if it is possible

I don't make video tutorials

Sel_Month is a named formula goto Formula, Name Manager

 

[Rania Alissa]

Hui
Need your help!!
I was trying to understand your SUMPRODUCT formula for YTD Prior Year Calculations. Can you explain the ScrDate 1st two components of the formula below, especially the -1,1,1.......your insights are highly appreciate. Do you know of a good link that explains how you calculated the YTD figures above. I am eager to learn but time is of essence. Warm regards
Rania

SUMPRODUCT((SrcDate>=DATE($G$11-1,1,1))*((SrcDate<=DATE($G$11-1,$F$11,1))*(ITEM=$C5)*(ACTUAL)))

 

[Hui]

SrcDate is s named range referring to your data
Date(G11-1,1,1) is the 1st of January last year before the year in G11

 

[Rania Alissa]

Hi Lui
Need your help understanding your model. I am fascinated by how fast you did it and am learning a lot. I am trying to find a link that would explain how you did it. I figured out how to use the Name Manager, a very helpful tool. I am finding challenges in two function under the Name Manager:

• To which cell reference is "Sel_Month" referring?
• What does {...} value mean?
• Why do I have to convert "Sel_Month" to =TEXT(Date,"MMMM")
• Can you explain the following Named cell under Names Manager =DATE('Desired Output'!$G$10,'Desired Output'!$F$10,1)

Thanks a million. Your model taught me a lot
Rania

 

[Rania Alissa]

Hi Lui
Can you show me how you solved your CASE STUDY budgeting model you sent me earlier? I would love it if you visually explain the steps. I am very keen to learn what you did. None of the video tutorials I looked at do financial statements and budgeting the way you do? Your solution is very unique, practical and creative. I would like to have a recorded version of the steps. It is very important for my new job. I have an interview coming soon where I would be asked to solve a CASE STUDY similar to the one you solved. I am willing to donate fees to the website for the time you invested in explaining to me how you solved the CASE study.
Regards

 

[Somendra Misra]

Hi Rania,

I was following this thread for quite some time. Although I tried doing this through Pivot Table (See attached). But here are the problems I faced.

1. There are couple of helper columns I added in the main data to calculate Prior year and YTD data.
2. Report filter will not work in this case (which I think), so gone for data validation drop down and than connected the helper cells with these drop down to update the data.
3. Every time you change month or year you need to refresh the Pivot, so increased steps, which I think most of us don't like.
4. Could not obtains Gross Margin % & Operating Profit %.
5. For GM & OP the percentage fields are coming wrong.

So this can be for practice purpose but in actual I think you must go with @Hui model, as it quick easy to follow and much flexible to changes in the raw data that might take in future.

Regards,