What formula can speed up my monthly reporting

[mark9988]

Hello,

Each month I receive monthly data from my manager, here you see Oct & Sep. data. I proceed to make a variance report like what you see under the yellow heading manually by going down each monthly list.

So for example I go down the Oct list and I see that the same business is in the Sep. list so I calculate the variance as copy and past the other fields such as business name, group, and group name, but you can see that business 5 is in the Oct list, but not in the Sept list so I wouldn't be able to calculate the variance. This is very time consuming and I was hoping I can get some kind of formula to do the work for me.

I included an example of what my report looks like under the yellow heading, the variance formula in cell N3 is =(I3-D3)/D3.

Thank you for your help!!

Dealer_Variance_Example.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	September					October					Oct vs Sept
2	Business Name	Group No	Group Name	Deal Size		Business Name	Group	Group Name	Deal Size		Business Name	Group	Group Name	Variance %
3	Business 1	Group 1	Decrease	637,667.73		Business 1	Group 1	decrease	87,799.27		Business 1	Group 1	decrease	-86.23%
4	Business 2	Group 4	inc/dec mix	1,987,751.14		Business 2	Group 4	inc/dec mix	127,941.64
5	Business 3	Group 2	Control	25,642.21		Business 3	Group 3	increase	12,176.17
6	Business 4	Group 1	Decrease	46,444.24		Business 4	Group 2	control	81,865.19
7	Business 6	Group 1	decrease	15,405.88		Business 5	Group 1	decrease	46,587.15
8	Business 7	Group 4	inc/dec mix	97,203.25		Business 8	Group 4	inc/dec mix	107,149.74
9	Business 9	Group 1	decrease	55,154.64		Business 9	Group 1	decrease	240,171.26
10	Business 10	Group 4	inc/dec mix	18,800.00		Business 11	Group 2	control	1,200.00
11	Business 12	Group 1	Decrease	52,950.16		Business 12	Group 4	inc/dec mix	22,998.03
12	Business 13	Group 1	decrease	21,717.92		Business 13	Group 1	decrease	326,476.07
Sheet1

 

[babycody]

By no means am I an expert, but try this and tell me if it works for you. You can remove the N/A error messages with an error trap. Just shows up because there is no match.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	September					October					Oct vs Sept
2	Business Name	Group No	Group Name	Deal Size		Business Name	Group No	Group Name	Deal Size		Business Name	Group No	Group Name	NEW Variance %	YOUR Variance %
3	Business 1	Group 1	Decrease	637,667.73		Business 1	Group 1	Decrease	87,799.27		Business 1	Group 1	Decrease	-86.23%	-86.23%
4	Business 2	Group 4	inc/dec mix	1,987,751.14		Business 2	Group 4	inc/dec mix	127,941.64		Business 2			-93.56%	-93.56%
5	Business 3	Group 2	Control	25,642.21		Business 3	Group 3	increase	12,176.17		Business 3			-52.52%	-52.52%
6	Business 4	Group 1	Decrease	46,444.24		Business 4	Group 2	Control	81,865.19		Business 4			76.27%	76.27%
7	Business 6	Group 1	Decrease	15,405.88		Business 5	Group 1	Decrease	46,587.15		Business 5			#N/A	202.40%
8						Business 6	Group 2	Decrease	46,587.15		Business 6			202.40%
Sheet2

 

[jdee]

Do just your business names have to match, or do the business group number and group name have to match also before there is a variance calculation? The way I see what you have is that you have a business name of say 1, but the group number and group name varies. Whyat do you do then?

 

[acw]

Hi

Assuming that there is only a match on Business name then
N3: =IF(SUMPRODUCT(--($A$3:$A$12=K3)),I3/SUMPRODUCT(--($A$3:$A$12=K3),($D$3:$D$12))-1,"")
Adjust the ranges and copy down.

If there has to be a match on Business Name and Group then
N3: =IF(SUMPRODUCT(--($A$3:$A$12=K3),--($B$3:$B$12=L3)),I3/SUMPRODUCT(--($A$3:$A$12=K3),($D$3:$D$12))-1,"")

HTH

Tony

 

[mark9988]

I'm sorry, I should of been a little more specific on my initial post. The group and group name columns stay constant for each business name meaning they never vary.

So business 1 for example will always be in group number 1 and its group name will always be decrease , business 2 will always be in group number 4 and it will always be in group name inc/dec, etc.

I hope this makes sense.

I appreciate your help and posts and I will give your formulas a try.

 

[PaddyD]

have you invesigated using a pivot table?

 

[mark9988]

I'm not familiar with pivot tables, but if it can create this report for me than I'll look into it as an option.

My goal is to be able to paste and copy the September and October data that I receive from my boss and have the report populate automatically.

I have to do this variance report for the last 36 months and there are hundreds of businesses in each monthly data set so I'm hoping to obtain a method that can produce results quickly

 

[jdee]

Does the following work for you?
This is bringing in your data each month on a separate worksheet named mo. data for reference here. Then creating a variance report worksheet. In this case, all the business names would be listed in column A along with their corresponding data in columns B & C. This would be entered just the one time on this sheet or from another sheet once and looked up, would not have to be repeated after that.

Then from the data sheet you would only be looking up the "deal size" for each business name. Your lookups and calculations can be done ahead of time and would be automatically filled in when you enter your data into the data sheet. I have used dashes to indicate that there was no data for a particular business name for that month. You could then filter the data or perform other analysis per your needs.

testing for mr.excel quest.xls
	A	B	C	D	E	F
1	Sep-05		Oct-05		Nov-05
2	Bus. Name	Deal Size	Bus. Name	Deal Size	Bus. Name	Deal Size
3	1	637,667.73	1	87799.27	7	100,000.00
4	2	1,987,751.14	2	22496721	2	17,000.00
5	3	25,642.51	3	29416	4	25,619.00
6	4	46,446.24	4	179465	5	14,732.00
7	6	15,405.88	5	176321	1	15,976.00
8
9	Monthly data sheet bringing in data each month
Mo.Data

testing for mr.excel quest.xls
	A	B	C	D	E	F	G	H	I
1				Oct. vs. Sep. Variances			Dec. vs. Nov. Variances
2	Name	Group #	Grp. Name	Sep-05	Oct-05	Mo. Var.	Nov-05	Dec-05	Mo. Var.
3	1	4	Decrease	637,667.73	87,799.27	-86%	15,976.00
4	2	1	Increase	1,987,751.14	22,496,721.00	1032%	17,000.00
5	3	2	Control	25,642.51	29,416.00	15%	-
6	4	3	Inc/Dec/Mix	46,446.24	179,465.00	286%	25,619.00
7	5	7	Decrease	-	176,321.00	-	14,732.00
8	6	6	Increase	15,405.88	-	-	-
9	7	5	Control	-	-	-	100,000.00
10	8	10	Inc/Dec/Mix	-	-	-	-
11	9	8	Decrease	-	-	-	-
12	10	9	Increase	-	-	-	-
Variances