Using sales objectives to calculate daily sales rates by month

Well -- I looked at the blue cells, and noticed that every region had a similar trend in last year: daily sales for each region begin at about .42% of annual sales in April 2015, and they increase to about .59% by March 2016.

Assuming that a similar trend might apply to the next year, I tried to mimic this trend in row 65 of the attached sheet.

Then, I applied my "trend" to the yellow cells and set up test column O23:O34 to compare with your targets in F7:F18.

And I began testing values in C65 until I found one that matched your target. I think that Solver could have done this, but it was faster for me to do it manually.

Not very scientific, I'm afraid, but perhaps it will help get you started in the right direction?

You can play with the trend in row 65, and as long as the sumproduct total at the end of the row = 100%, the numbers in the yellow cells should adjust appropriately.

Attached is my workbook – I am currently trying to use sales objectives to establish a ‘daily sales rate’ objective for each month, by region, using the # of selling days per month.

The ‘daily sales rates’ for each month (by region) for last year are highlighted in blue – and I’m trying to fill in the yellow cells.

Please note how the values in green are calculated (beginning in cell O39). I’m hoping to use my objectives highlighted in orange to reverse this process and fill in the yellow cells. I know that solely doing this could result in countless solutions but since I have the % increase by region (D7:D18) I think there may be a way to logically work this out.

Thank you in advance for your input.

Etaf,

Isn't your method not taking into consideration the new # of selling days for Apr 2016 - Mar 2017? In the attached sheet beginning in cell P23 is what the Region totals would be using your values in yellow, which doesn't match my total objective values beginning in cell F7.

Any idea how to adjust these values for the new # of selling days? The total objective values beginning in cell F7 are not able to be adjusted.

For my own amusement and also to find out if I can upload files to this forum I've
made a solver model than can be run with the macro "SolvLoop"

In order to do so a reference must be set to solver in "Visual Basic"

"Developers" tab -> "Visual Basic" icon -> Tools -> Refernces and tick box marked
solver.

Alf

Eibi,

Sorry I missed your post from yesterday. Your method does address the issue of different day counts per month, but instead of using the values in C65:N65 for each of the regions, I'd like to use the values specific to that region (C52:N63). This is because the % from month to month may increase for some regions while it decreases for others, and even then the regions are increasing & decreasing at different rates. In the attached sheet I tried to do this in cell O52:O63 but I'm not sure how to adjust the values so they equal 100%.

Alf,

I'll have to look at your sheet and get back to you.

Hi ohisofly

I'll have to look at your sheet and get back to you.

You are welcome, but this is not a sophisticated model so it probably could be improved to better suit the OP's need. Your comments about solver made me build a solver model and the I had a bit of bother trying to upload the file.

In my oppinion this forum get more and more difficult to participate in. Slooooov update, or one gets blocked by "Sucuri Cloud" and now attaching files using "Paper Clip" icon is not working.

Alf

Hi Alf,

I'm the OP - I accidentally made a second thread so the first one was deleted and merged with this one (Eibi's first post was a reply to my thread yesterday). Hopefully that clears up any confusion.

Alf,

I just ran your macro and while it does work, the increases are not very proportionate so I can't use it. Region A for April 2016 increased from $255 to $347 while Region A for March 2017 increased from $362 to $390.

Originally Posted by ohisofly

Your method does address the issue of different day counts per month, but instead of using the values in C65:N65 for each of the regions, I'd like to use the values specific to that region (C52:N63).

I've tried to apply the approach you suggested.

Basically, we use each region's daily rates from last year to assume a trend for this year's daily rates. This leaves a tiny margin of discrepancy (O52:O63) due to the different number of sales days in the year...which I spread across all the days of the year equally.

What do you think?

After modifying what Eibi posted I think I came up with a solution - please see the attached sheet. I used solver to adjust my multiplier for each region so that the sumproduct formulas in cells O65:O76 equal 100%. Any input regarding if my solution makes sense or not would be greatly appreciated.

Originally Posted by eibi

I've tried to apply the approach you suggested.

Basically, we use each region's daily rates from last year to assume a trend for this year's daily rates. This leaves a tiny margin of discrepancy (O52:O63) due to the different number of sales days in the year...which I spread across all the days of the year equally.

What do you think?

Eibi,

Even though both of our approaches provide very similar results I think I'm going to go with yours - we both adjusted the multiplier to compensate for the different number of days, just did so in a different way.

the increases are not very proportionate so I can't use it

As I said this was a simple model. In order to solve the problem in a specific way one would then have to add constrains i.e. perhaps starting value for a row should be 0.42% of annual sales in April 2015, and the final value for a row should increase to about .59% by March 2016.

Then probably all values in between should be greater than the starting value and less than stop value.

Alf