Question Simple Variance Composition Generator (1 Viewer)

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&current - 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

Hopefully someone can reply to my post or tell me if my post contains info that too hard to answer. :banghead: I am still "locked" at my own procedure so I am seeking the help of prof programmers.

so what's the problem?

you can sort the data by change.
you can summarise in a variety of ways.
you can select top x.

I presume the increase is the sum(change)
I don't see where the offset decrease of (-50) is derived.
I don't see exactly what you are trying to achieve for your final output.

Hello Gemma,

Many thanks for replying

The main problem is how can I limit the explanation generated.
I have these two options but need help how am I gonna start.

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.

The output of the access file is to produce a combined explanation for INCREASES and DECREASE.
An example of the output is:
Payments Due INCREASE by USD 10, offset DECREASE by USD -50.

Payments Due = customer account
INCREASE = increase amount tagging
by = concatenated text word
USD = concatenated text word
10 = increase amount
, = concatenated text word
offset = concatenated text word
DECREASE = decrease amount tagging
by = concatenated text word
USD = concatenated text word
-50 = decrease amount

the access file already produces these results but the problem is when there are too many customer accounts. It will generate an explanation beyond 255 characters and also impractical to end user.

below again are the main queries.
Queries:
Two queries to delete the tables data.
Select queries were divided according to steps.
1_uni_conso_prior&current - 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

Payments Due INCREASE by USD 10, offset DECREASE by USD -50.

the problem is understanding precisely what your attempting to output from the data

a) I do not see what exactly the 10 or the -50 represent.
b) the statement does not include the customer account - so I do not see whether there is one statement for the entire ledger, or one statement per account.
c) I therefore so do not see why the string exceeds 255 characters.
d) but even if it does, that surely isn't a problem.
e) so - given the sample data, what data do you use to generate the statement, and give us an example that would exceed 255 characters.

Hello Gemma,

Please find below my remarks below.

a) I do not see what exactly the 10 or the -50 represent.
Assuming below is in table and this is the Payments Due ledger:

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

the top row are the field names, 1st column contains the row labels, the next two columns are the customer amounts for the two periods, the last column was for the change from November and October. The -50 came from the DECREASE change of Marconi customer account and the 10 is from Hack customer account.

b) the statement does not include the customer account - so I do not see whether there is one statement for the entire ledger, or one statement per account.
I did not include it at first but we can include.

Payments Due from Marconi INCREASE by USD 10, offset from Jack DECREASE by USD -50.

c) I therefore so do not see why the string exceeds 255 characters.
Our organization do have so many customer accounts exceeding 100+, thus if you concatenate all these differing INCREASEs and DECREASEs from these customer accounts it will exceed 255 chars.

d) but even if it does, that surely isn't a problem.


e) so - given the sample data, what data do you use to generate the statement, and give us an example that would exceed 255 characters.
it's the same as the above table. only that more customers.

cheers,
vash

Our organization do have so many customer accounts exceeding 100+, thus if you concatenate all these differing INCREASEs and DECREASEs from these customer accounts it will exceed 255 chars.

Click to expand...

Why are these concatenated? Seems logical that if you have many accounts and you concatenate information, at some point you will exceed XXX characters. So why are they concatenated?

I suggest you step back from the programming and look at the process in simple business terms and identify the logic of the processes. Once you sort out what is/shuld be happening, and what is required as an output/result, you can design appropriate options. But you really need to work with the business facts.

I also think that readers need a detailed answer to Dave's question

so - given the sample data, what data do you use to generate the statement, and give us an example that would exceed 255 characters.

Click to expand...

Hello jdraw,

I attached here a sample of the data that we are getting from our system.

on the second sheet, there you find data for 2 periods. this is not the maximum data, it can be more than this data.

i also do some pivots on the other sheet and calculated some variances.

regarding the explanation sample, i manually do some concatenation based on per item analysis.

concatenated output is the format of explanation we use.

@vash

sorry, I am completely unclear about what you are trying to achieve, in respect of multiple accounts. I don't think I will be able to assist any further on this.

Hello dave,

it's ok, no worries and thanks

????

regarding the explanation sample, i manually do some concatenation based on per item analysis.

Click to expand...

If you are concatenating output and it is exceeding 255 characters, what do you think should happen???

hello jdraw,

manually concatenation consumes a lot of time and effort considering the number of customers we have. i believe this process should be automated.

that's why I created the access file.

i actually solved the problem in access itself for those items exceeding 255 characters. the field that i used is Memo field. but the problem is when I transfer it to excel. I use two or more cells.

the solution that I am thinking is to limit the number of customers INCLUDED in concatenation.

and the two are the possible solution, but I am unable to continue as I do not know how it will be done.

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.