vash011390
Registered User.
- Local time
- Today, 16:59
- Joined
- Jul 29, 2015
- Messages
- 29
Hi All,
My job requires me to create a template that will simplify our variance composition analysis.
This is the set-up: Our organization consist of employees who handle more than 1 business unit. A business unit is required to prepare variance composition analysis report for their interim schedules. An interim schedule do have more than 30 accounts to check for variance composition.
What is variance composition: Variance composition is an initial analysis of an account. An account has customers. The analysis is simply to enumerate all changes on customer balances for that account. Changes are INCREASE and DECREASE. This will be combined on 1 explanation only.
Below is an example of an account schedule:
Account: Payments Due
Customer | October | November | Change
Raccoon | USD 50 | USD 75 | USD 25
Marconi | USD 75 | USD 25 | USD -50
Jack | USD 0 | USD 10 | USD 10
Mexus | USD 25 | USD 0 | USD -25
Seeing the above will make a variance explanation of:
Payments Due INCREASE by USD 10, offset DECREASE by USD -50.
I already prepared an Access file that will generate an explanation an I attached it here:
Tables:
I created two tables to separate the current and prior period amounts.
Queries:
Two queries to delete the tables data.
Select queries were divided according to steps.
1_uni_conso_prior¤t - this is used to combine the current and prior data.
2_sel_variance - to compute the variance
3_sel_inc&dec - to define increase and decrease and to shorten amounts
4_sel_dec_variance - to concat decrease
4_sel_inc_variance - to concat increase
sel_explain_DECREASE - to generate explanation of DECREASE
sel_explain_INCREASE - sel_explain_INCREASE
Modules:
This is the macro that I use to concat the texts. I actually got here in access programmers site.
The main drawback is:
As expected it concats all DECREASE and INCREASE explanation which the end user find it not practical.
Which to improve:
Can you check if you can limit the explanations to:
1. Top 10 Highest INCREASE and Top 10 Highest DECREASE amounts.
or
2. Total amount of explanation for INCREASE should be not more than 80% of the total INCREASE amount and total amount of explanation for INCREASE should be not more than 80% of the total INCREASE amount.
I know the enhancement is not simple but I would really appreciate the possible solutions you may advise.
Cheers,
vash
My job requires me to create a template that will simplify our variance composition analysis.
This is the set-up: Our organization consist of employees who handle more than 1 business unit. A business unit is required to prepare variance composition analysis report for their interim schedules. An interim schedule do have more than 30 accounts to check for variance composition.
What is variance composition: Variance composition is an initial analysis of an account. An account has customers. The analysis is simply to enumerate all changes on customer balances for that account. Changes are INCREASE and DECREASE. This will be combined on 1 explanation only.
Below is an example of an account schedule:
Account: Payments Due
Customer | October | November | Change
Raccoon | USD 50 | USD 75 | USD 25
Marconi | USD 75 | USD 25 | USD -50
Jack | USD 0 | USD 10 | USD 10
Mexus | USD 25 | USD 0 | USD -25
Seeing the above will make a variance explanation of:
Payments Due INCREASE by USD 10, offset DECREASE by USD -50.
I already prepared an Access file that will generate an explanation an I attached it here:
Tables:
I created two tables to separate the current and prior period amounts.
Queries:
Two queries to delete the tables data.
Select queries were divided according to steps.
1_uni_conso_prior¤t - this is used to combine the current and prior data.
2_sel_variance - to compute the variance
3_sel_inc&dec - to define increase and decrease and to shorten amounts
4_sel_dec_variance - to concat decrease
4_sel_inc_variance - to concat increase
sel_explain_DECREASE - to generate explanation of DECREASE
sel_explain_INCREASE - sel_explain_INCREASE
Modules:
This is the macro that I use to concat the texts. I actually got here in access programmers site.
The main drawback is:
As expected it concats all DECREASE and INCREASE explanation which the end user find it not practical.
Which to improve:
Can you check if you can limit the explanations to:
1. Top 10 Highest INCREASE and Top 10 Highest DECREASE amounts.
or
2. Total amount of explanation for INCREASE should be not more than 80% of the total INCREASE amount and total amount of explanation for INCREASE should be not more than 80% of the total INCREASE amount.
I know the enhancement is not simple but I would really appreciate the possible solutions you may advise.
Cheers,
vash