Excel Formula

[davidjearly]

Hi folks,

Excel question here. I am trying to come up with some sort of cumulative formula.

Here is the situation. I have a row called target, and each column represents a month in the year. I set myself the target to sell 250 particular items in each month of the year, and I want to have a formula that sets an adjusted target depending on what I sell.

In addition to the row called 'target', there is a row called 'actual', a row called 'variance' and then a row for 'adjusted target'. If I sell 298 items in one month, I am above my target, and the variance row calculates the difference. Then I need a formula that calculates an adjusted target for each month where I might miss that target, taking into account that I overshot it in a prior month.

This formula would ideally make sure I am close to meeting my target, each month on average.

Anyone help with this?

Thanks!

 

[ergdegdeg]

Variance: =actual-target
Adjusted Target: =target-variance

 

[Doctor Q]

I couldn't resist the challenge, so I just created a spreadsheet that might suit you, davidjearly.

Cell C2: =IF(ISBLANK(B3),B2,($O2-B5)/(COLUMN($O2)-COLUMN()))

Copy the C2 formula across to D2, E2, ..., N2.​

Cell O2: Your annual goal, which you fill in to get started. I used 3000 as an example.

Cell O3: =SUM(C3:N3)

Cell C4: =IF(ISBLANK(C3),"",C3-C2)

Copy the C4 formula across to D4, E4, ..., O4.

Note: This Variance row isn't necessary for the rest of the formulas to work. I included it because you mentioned it.​

Cell C5: =IF(ISBLANK(C3),"",B5+C3)

Copy the C5 formula across to D5, E5, ..., N5.​

All other nonblank cells should have the constant values shown, including the 0s in B3 and B5.

As you fill in the actual sales for a given month, the Variance and Cumulative sales will change in that column, and the remaining values in the Target sales row will change to reflect how far under or over target you are.

You could add a Cumulative variance row if you want that too.

Attachment 1: before any data is entered, with a target of 3000 for the year.



Attachment 2: after the first three months, with sales of 200, 500 and 270. The target is 276/month for the remaining 9 months.

 

[davidjearly]

I couldn't resist the challenge, so I just created a spreadsheet that might suit you, davidjearly.

That is a big help actually Doctor Q. Thanks very much for your effort.

One more question:

Would there be a method of doing this without having the cumulative total row? I know I mentioned it, but it is the adjusted target row that is actually the most important.

Here is the way it would ideally be structured:

Target Sales
Actual Sales
Variance From Target
Adjusted Target

There would be a column for each month, and it wouldn't necessarily be an annual period, but might be over 18 months and have a 'total' column at the end.

Thanks!

 

[Doctor Q]

That is a big help actually Doctor Q. Thanks very much for your effort.

One more question:

Would there be a method of doing this without having the cumulative total row? I know I mentioned it, but it is the adjusted target row that is actually the most important.

Yes.

First of all there's the trivial answer: you can always hide a row (Format -> Row -> Hide), so you don't see it, even if it's used in computations, so you could hide row 5. You could hide Column B as well; I used it so that the fomulas in all columns could be the same instead of needing Column C to use different "starting" formulas.

However, the Cumulative sales numbers doesn't have to be in their own row since they can be computed directly in the Target sales formula.

Change cell C2 to

=IF(ISBLANK(B3),B2,($O2-SUM($B3:B3))/(COLUMN($O2)-COLUMN()))​

and copy it to D2, E2, ..., N2.

Then you can delete the Cumulative sales row.

Here is the way it would ideally be structured:

Target Sales
Actual Sales
Variance From Target
Adjusted Target

Suppose the rows were as you say:

Target Sales
Actual Sales
Variance From Target
Adjusted Target​

If you filled in Target Sales and Actual Sales for the first month, and had Variance From Target and Adjusted Target computed automatically, what should happen to the Target Sales cell in the next column? Are you going to fill it in yourself, and if so, with what value? If, instead, it's merely a copy of the Adjusted Target computed from the previous month, then you don't need the Adjusted Target row at all because the changing targets are in the Target Sales row, as in the version I posted above.

Let me know and I'll adjust it accordingly.

There would be a column for each month, and it wouldn't necessarily be an annual period, but might be over 18 months and have a 'total' column at the end.

Thanks!

You can use this same chart for any number of months, by copying/inserting more columns in the middle.

 

[davidjearly]

Ok, many thanks for your continual help with this Doctor Q! I'll try and outline what I am looking for.

I would like to keep 'Target' and 'Adjusted Target' separate so that it is easy to see how the adjusted target deviates from the actual target.

The cumulative sales is not really important in this sheet.

So as before, the rows would be:

Target Sales
Actual Sales
Variance from Target (showing a simple percentage variance from the target)
Adjusted Target (showing the new target for the next month depending on whether the actual sales were +/- the target).

The months start at Jun 2008 and run to Mar 2010.

The last little thing I have been trying to do is to get the adjusted target to calculate based on the last months sales and to add/subtract this to/from the following months adjusted target.

Does that make sense?

Thanks again!

 

[davidjearly]

Ok, here goes another attempt to explain what I am after.

I have these 6 rows:

Target
Actual
Variance
Adjusted Target
Cumulative
New Target

Each column represents a month between June 08-May 09, and then a total.

If my target is 10 sales each month, I would like to have an adjusted target field (which changes for the following month depending on how much I missed or exceeded my target the previous month), and a new target field beginning in Nov 2008 (this begins here, because the adjusted target has become so big it will be impossible to acheive in December). Therefore, the 'New Target' field should be a formula which takes what I have already sold so far, and substracts it from the overall target (in this case: 120). Then, I would like the formula to divide the product of this over the remaining month so that the new target is balanced over each month from the remaining sales I've yet to achieve.

I've included an example below to try and help explain what I am going on about.

I've tried many formulas, and it seems a simple thing to do, but none of them seem to work!

Thanks in advance!

 

[Doctor Q]

I would like to have ... a new target field beginning in Nov 2008 (this begins here, because the adjusted target has become so big it will be impossible to acheive in December).

So far so good, but I'll need more information about this part of your request.

1. How do you know that November is when catching up is no longer possible? If sales stay low, you'll get further and further behind, but in any month sales could increase and make up for all past deficiencies. So there's no particular month where it's hopeless and I don't know how the spreadsheet would know to make a "catch-up" average start at November, as opposed to October, December, or any other month. If you can tell me the deciding factor, I'll work it in.

2. If you'd prefer, the New Target column could tell you, for each month, what your average sales need to be for all remaining months in order to reach your target. Based on your example, it could be 11 for November, meaning you'd want to average 11 from now on, but the value shown for each month would depend on how much you have to catch up and how long you have left to do it.

If you like choice #1, I need to know how to determine the New Target's starting month. If you like choice #2, then I know just what to do for you.

(Actually, I have a couple of other questions, but I'll ask those after we have this part of the plan figured out.)