• 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

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

MIS Report

P

Pankaj Arora

New Member
I have to create an MIS Report (already a format is given) through Dump figures formatted through Pivot Table and ABP figures. How Can I automate this and take figures from Pivot Table and ABP.
Regards,

Pankaj Arora
 

Attachments

  • Query1.xlsx
    15 KB · Views: 13
Hi Pankaj,

Can you elaborate your requirement more in details like which sheets will be data what is pivot table for, in which sheet you want automation how is data added and any other details that required to do the task.

Regards,
 
Somendra Misra said:
Hi Pankaj,
Can you elaborate your requirement more in details like which sheets will be data what is pivot table for, in which sheet you want automation how is data added and any other details that required to do the task.

Regards,
Click to expand...
SAPDump is a data Sheet. Thereafter a Pivot Table is Prepared taking dump from SAP namely Pivot Table of SAP Dump. ABP is a Budget from which data are taken to prepare Reports. Data Are also Taken from Pivot Table. In MIS Sheet I have to Fill the relevants. I want to automate filling of data from PIVOT TABLE SHEET AND ABP Sheets.
 
See the attached file. I had added some formulas in ABP sheet & MIS sheet. Just check the data.

Regards,
 

Attachments

  • Query1.xlsx
    17.2 KB · Views: 16
Hi Somendra: It helps me a lot. But unable to use cumulative formula. Further the series may not contain all the number. All the query are marked in yellow colour.
 

Attachments

  • Query1(2nd Cut).xlsx
    16.9 KB · Views: 7
@pankaj

Please mention the Exact result which will be come in there in manual figure

with that it is ease to give the correct formula

Thanks
 
Pankaj,

Let me know if attached file is the output you are looking for.. if so our ninjas will give you the better version of the formula as I have hardcoded and used helper columns to your data
 

Attachments

  • Query1(2nd Cut).xlsx
    17.2 KB · Views: 4
Hi somendra:

Let me explian in detail . Suppose I h've to prepare MIS report for Q2x2014-15 (Say2014). For preparing said report I'm taking SAP dump from Q1X2013-14 to Q2x2014-15. It is pertinent to mention Q3 & Q4 of 2014-15 are still to come.

Now there are four colums i have to prepare:

Q2x2013-14(2013) Expenses (i.e. Expenses of Previous year of q2)

upto q2x2013-14(2013) Expenses (i.e.Expenses upto q2 of previous year- q1+q2 of 2013)

Q2x2014-15 (2014) Expenses (i.e. Expenses of Current Year of q1 )

upto Q2x2014-15 (2014) Expenses (ie, Expenses upto q2 of 2014-14 - q1+q2 of 2014)

How we will calculate uptopq2 2013-14 and upto q2 of 2014-15. Unable to write such formula in Excel 2007 tried a lot. Use of sumifs function between specified period.
 

Attachments

  • Query1(2nd Cut).xlsx
    16.9 KB · Views: 5
Hi Pankaj,

See I think you want a revised formula Column B & Column F on Sheet MIS. If this correct then column B formula are working OK.
and column F formula should be updated as below in F4 and copy down:

=SUMIFS(Table1[Amount],Table1[EXP code],MIS!$D4,Table1[y/m],"<="&"2014/02",Table1[y/m],">=2014/01")

Regards,
 
Hi:

I want to use Forecasting Technique in Excel while making forecast of Admin Expenses in Excel. In this connection it is pertinent to mention that :

1. Some Expenses are fixed.

2. Some Expenses are Variable.

3. Some Expenses are Semi- Variable.

Please suggest which Forecasting Techniques can be used depending upon the nature of Expenses.

Regards,

Pankaj Arora














Somendra Misra said:
Hi Pankaj,

See I think you want a revised formula Column B & Column F on Sheet MIS. If this correct then column B formula are working OK.
and column F formula should be updated as below in F4 and copy down:

=SUMIFS(Table1[Amount],Table1[EXP code],MIS!$D4,Table1[y/m],"<="&"2014/02",Table1[y/m],">=2014/01")

Regards,
Click to expand...
 
You must log in or register to reply here.
Back
Top