Variance Analysis

[vishwas]

Dear Forum Experts,

I need your help on the below request.
I have 3 work sheets
1. Actual [ will be updated every month]
2. Budget [ this data is already available]
3. Variance

Actual data is as below



Budget data has a similar format


Variance Analysis is a result ( Actual -Budget)

The output should be based on user selection of

1.Start & End month

2. Function name

Variance analysis should show the

1.selected month Actual , Budget & Variance and
2.Year to date Actual , Budget & Variance

File attached - Refer Variance analysis


Kindly help

Thanks
Vishwas

 

[Nebu]

Hi:

Please find the attached. I have used an array formula to get the results
Enter array formula using Control Key+Shift Key+Enter key

Thanks

 

[vishwas]

Hi Nebu - Thanks a million!!

In the variance file, the user will also use the month selection; start month and the end month. Only the selected months and the Total year figures should be displayed.Can you please help me on this as well

Thanks Again
Vishwas

 

[Nebu]

Hi:

If you have drop down for dates, you should be having only one sets of data for your budget and actual . Is that alright with you.

Thanks

 

[vishwas]

Hi Nebu,

In the budget and Actual tabs the headers will remain the same, however the items may or may not be present. Its a dump file extract.

For example : In the budget tab ,Item salaries may be 200000 for Jan2015, but during the month there are no entries for salaries, hence in the Actuals tab it will not be present.

If the above is not possible, than I will have to align both sets of data. Please advice

Thanks
Vishwas

 

[Nebu]

Hi:

Since your template is designed to accommodate all the dates present in the actual and budget tab , you really do not need a date drop down.

Do the following

• Wrap the current actual formula with iferror, so that if values are not present it will give you a zero.
• Similarly put an if condition to your current budget formula saying that if the actual gives you a zero value make the budget value zero.

Let me know your thoughts.

Thanks

 

[vishwas]

Hi Nebu,

I think I may not have explained clearly.
The budget data is fixed. This will not change. In the actual, there may be additional items during the month or less.

My variance tab will include all the items i.e From Budget + additional items from Actual. I will be manually keying in the additional items in the Variance sheet [ I dont know how to automate this? Is this possible?]

If the actuals items are not present during a selected month, the values should be displayed as "ZERO". However there may be Budgeted values against this


Hope this helps . Sorry for the confusion, appreciate your help

Regards
Vishwas





Where

 

[Nebu]

Hi:

It is possible to put the formulas whichever way you want , but before that I need some clarifications.

1. Do your variance template have exhaustive list of all the dates?
2. Do you want to pull budget figures if the actuals are not present?
3. What do you mean by additional items from the actuals?

With the current design you have I believe that there is no need for dates drop down , it can be made dynamic in the current formula by wrapping it with if conditions.

Thanks

 

[Nebu]

Hi:

Another layout,

Select Function from drop down the function name will be automatically populated.

Select Start month and end month from drop down , the actual and budget figures will be automatically populated for the period selected . I have used helper columns which is hidden. Once you highlight the formulas you will be able to figure out.

Thanks

 

[vishwas]

Dear Nebu - You are a star!!. Thank you. I will work on this for the moment and should I experience any more issues wherin I need help I will post it back in the forum as I new post.

The only missing part in the layout is Year to date is missing.

For example if the month selected is Jan & Feb, the layout should include

Jan ( Actual , Budget & Variance)-----> Separate Column
Feb ( Actual ,Budget , Variance)-----> Separate Column
Total ( Jan+ Feb) ----> ( Actual , Budget & Variance)

I know I'm asking more help


Regards
Vishwas

 

[Nebu]

Hi:

This is possible if you have drop down for month instead of month & year.

Thanks

 

[vishwas]

Hi Nebu,

Can you guide me how to do this please
Thanks
Vishwas

 

[Nebu]

Hi:

Please find the attached.

The template will only populate value based on the selection made with the dates.

Thanks

 

[vishwas]

Dear Nebu,

I selected only 001.2015 , for both start and end date,but it displayed 002.2015 as well.
There is a possibility that the user will select 001.2015 as both start and end date. This happens if he wants to review only one month.

Can you please help
Regards,
Vishwas

 

[Nebu]

Hi:

Please find the attached.

Thanks