Mathematical problem-ROUND function doesn't help

[ashish514]

I have an annual sales target of no. of units to be sold which is to be divided quarterly. My historical sales data gives me the ratios in which the annual target is to be divided into quarters, the ratios are 0.17, 0.23, 0.3 and 0.3 respectively for Q1,Q2,Q3 and Q4. When I divide the target into these ratios, I do not always get whole nos., thus in order to get whole numbers I use ROUND function. But now when I obtain quarterly figures, sometimes their sum doesn't match with the original annual figure. For example, if my annual target is 3, then 0.17*3=0.51 which is rounded up as 1. Similarly I get a target of 1 for each quarter which adds up to 4 units annually while my original annual figure was 3. Currently I have to manually visit each such case and make corrections. Is there a faster way of getting the required solution???

 

[RonB1111]

Make your formula for Q4 equal to Annual Target minus the sum of Q1,Q2 & Q3.

 

[ashish514]

But that will distribute my target of 3 equally in first 3 quarters and 4th quarter will show 0, whereas most of the sales happen in last 2 quarters. However when I do what you told in 1st quarter instead of 4th, it gives me the right solution for specific no. 3. But now when my annual target is 1, it will allocate the target of 1 unit to 1st quarter, showing zero in 3rd and 4th quarters which are my higher selling quarters.

 

[Peter_SSs]

If you have an annual sales target of numbers like 1 or 3, and you need a spreadsheet to work out quarterly targets, then something seems seriously wrong to me!

However, if you insist, then perhaps something like these formulas, copied down, may do what you want. These ensure something comes in Q4 if there is any target at all, and any rounding adjustment is done in Q1.

Excel Workbook
	C	D	E	F	G
2		0.17	0.23	0.3	0.3
3	Annual Target	Q1	Q2	Q3	Q4
4	0	0	0	0	0
5	1	0	0	0	1
6	2	0	0	1	1
7	3	0	1	1	1
8	4	1	1	1	1
9	5	0	1	2	2
10	6	1	1	2	2
11	10	2	2	3	3
12	30	5	7	9	9
13	99	16	23	30	30
14	1003	170	231	301	301
Assign Target

 

[ashish514]

Thanks Peter, Working them out manually will be very tough, because the list of products is very long and it is even bifurcated in variants. It's about 60 variants in all and I have a sheet for each of the five regions which have at least 3 states each. Moreover I have annual targets for next 3 years, so again multiply the no. of sheets by 3 and you will see what's wrong

 

[Peter_SSs]

Thanks Peter, Working them out manually will be very tough ..

I was only suggesting manual if the annual target was a very samll number like 1 or 3 as in your stated examples.

.. because the list of products is very long and it is even bifurcated in variants. It's about 60 variants in all and I have a sheet for each of the five regions which have at least 3 states each. Moreover I have annual targets for next 3 years, so again multiply the no. of sheets by 3 and you will see what's wrong

All of that means nothing to me I'm afraid as all I have to go on is the limited information provided in your first couple of posts. There was no mention of lists of products, multiple sheets, multiple years, different regions etc.

Does my previous suggestion provide anything in the right direction?

 

[RonB1111]

But that will distribute my target of 3 equally in first 3 quarters and 4th quarter will show 0, whereas most of the sales happen in last 2 quarters. However when I do what you told in 1st quarter instead of 4th, it gives me the right solution for specific no. 3. But now when my annual target is 1, it will allocate the target of 1 unit to 1st quarter, showing zero in 3rd and 4th quarters which are my higher selling quarters.

Could you explain the nature of your sales. Are they mostly extremely low volume but very high cost (eg: only a few sales per year for most items)? What % of total dollar revenue do the low volume quantity items represent? In the case of an annual sales target of 1, which quarter do you want the target to be placed in? Same question for sales targets of 2 & 3?

 

[ashish514]

@Peter SS- Your solution is exactly what I wanted initially(at the time when i posted the question) and this was what i was doing manually. But as I progressed into it, I realized that this will put more pressure on last 2 quarters than intended. If you sum total the columns d,e,f,g in your given example and then take out the ratios, the shares of q1 and q2 will be less then 0.17 and 0.23 respectively while those of q3 and q4 will be more than 0.3. More so, when the figures are smaller, which is exactly my case. I guess this process cannot be automated and i will have to do it manually. Thanks anyways

@Ron B1111- Yes they are low volume high cost products. Initially I was thinking that the sales target of 1 will be placed in last quarter but what I actually require is that the total quarterly targets(i.e. for all products combined) should be in the required ratio stated earlier. So i will have to do it manually.

 

[Peter_SSs]

See if this does what you want. The quarterly target ratios are in D2:G2. I have included columns I:L to show the cumulative quarterly ratios.

Formulas in D4:G4 copied down.
Formula in I4 copied across and down.

Excel Workbook
	C	D	E	F	G	H	I	J	K	L
2		0.17	0.23	0.3	0.3
3	Annual Target	Q1	Q2	Q3	Q4		Q1	Q2	Q3	Q4
4	3	0	1	1	1		0.00	0.33	0.33	0.33
5	6	1	1	2	2		0.11	0.22	0.33	0.33
6	5	2	1	1	1		0.21	0.21	0.29	0.29
7	1	0	0	0	1		0.20	0.20	0.27	0.33
8	1	0	0	1	0		0.19	0.19	0.31	0.31
9	5	1	2	1	1		0.19	0.24	0.29	0.29
10	6	1	1	2	2		0.19	0.22	0.30	0.30
11	10	1	3	3	3		0.16	0.24	0.30	0.30
12	30	6	6	9	9		0.18	0.22	0.30	0.30
13	99	16	23	30	30		0.17	0.23	0.30	0.30
14	7	1	2	2	2		0.17	0.23	0.30	0.30
15	18	4	4	5	5		0.17	0.23	0.30	0.30
Assign Target

 

[ashish514]

Thanks a lot, I already completed the thing, but this will be quite helpful when something like this comes again.

What I did this time is
1. D4= round(D2*$C4,0) copied down and across
2. sum of each column in the row below the last entry
3. respective final ratios in the next row
4. H4=sum(D4:G4) copied down

After this I manually compared entries of column C with column H and made changes such that H4=C4 and at the same time maintaining the required ratio.

Till now,I have never been disappointed on Mr. Excel, thanks a lot.