B Excel: converting a 3-ish week count into a monthly count

  • B
  • Thread starter Thread starter DaveC426913
  • Start date Start date
DaveC426913
Gold Member
Messages
23,830
Reaction score
7,815
TL;DR Summary
I have numbers for orders placed approximately every three weeks; I want to convert that to months
A simple spreadsheet to work out cost of consumption.

DoP (raw): I've placed seven orders from Feb to Jun.
Amount (raw): Depending on the specific product, sometimes it comes in bundles of 20, sometimes in bundles of 25.
Cost (raw): I've listed the cost for each order.

Duration (derived): How many days since the last order.
Rate (derived): Number consumed / Duration (So, 20 were ordered on Feb 24, and they lasted 20 days, i.e. 1 per day)
Cost/day (derived): Number consumed / cost of order ($158/20= $7.90 per day)

1750963971189.webp


I've got the averages worked out. Even though there's seven orders, I can't count them all because either I don't have the consumption rates for February, or I don't have the consumption for June yet.

Where I'm stumped is C12: converting the cost to a monthly average, since so far, it's all based on orders approximately 18.5 days apart.

I am calculating the total cost and dividing by how many days have passed. (=SUM(C2:C8) / (DATEDIF(A2:A8,"d")/30) )

But $280/month is too high.
My sanity check says it should be approximately $250. I get this by taking the average cost per order ($152 (C11)) and dividing by .6 (total duration/30), which will give me approximately $250 per month.

What have I missed? I must not be including the full range of dates (DATEDIF(A2,A8,"d")/30), or maybe too many orders are included (C2:C8).
 
Mathematics news on Phys.org
The anomaly is that 25 appears to cost less than 20.

AM
 
Andrew Mason said:
The anomaly is that 25 appears to cost less than 20.

AM
Right. Sorry. Different products. Sometimes one isn't available so I get the other, which might be cheaper i.e. 25 of X may well be cheaper than 20 of Y.

But that should not cause any problems. The numbers are what they are. There's no predictions or guesswork here, only straight calcs.

I'm just a little confused exactly what range of dates/durations/costs I should use to get an accurate average monthly cost.
 
Your average is too high because you're taking two orders as your endpoints.

Here's a conceptual example: suppose you have two orders that are actually one month apart, 30 days, for 250 dollars each and you add up the values in rows 2 and 3 just like you did here. Then you get 500/30*30=500/month not 250/month.

If your first date is going to be the day of an order your last date for deciding how many days to divide by should be the day before the next order that you're not including. In this case adding about 18 days is probably sufficient.
 
  • Like
Likes Isaacpalmer and hutchphd
B11 strikes me as a tad too high. It seems to imply that you only averaged the last 4 purchases.
I get 21.67 for the last 6 weeks.

C11 also seems odd, as it appears you've averaged all 7 weeks. I think as before, this should only be the average for the last 6 weeks, where I get $151.17

I also get slightly different solutions in my C13 & C15, though nothing close to your $280.26

ABCDEF
1purchase dateqty$durationconsumption rate$/day
2
average days/month
2/24/202520158
3
30.44
3/16/202520158201.00$7.90
44/1/202520154161.25$9.88
54/23/202520154220.91$7.00
65/12/202525136191.05$8.11
76/1/202525147201.25$6.80
86/18/202520158171.47$8.65
9
10average21.67151.1719.001.16$8.05
11totals1309071141.14$7.96
12annual based on totals$2905.98
13monthly based on totals$242.17
14annual based on averages$2941.93
15annual based on averages$245.16
 
If you are satisfied with your average daily cost calculation, why not generate the monthly numbers from it ? Seems very involuted to me otherwise.
 
hutchphd said:
If you are satisfied with your average daily cost calculation, why not generate the monthly numbers from it ? Seems very involuted to me otherwise.
It seems to me that's going to compound any rounding errors.

Presumably, any derived values should only be derived from raw data, not derived data.
 
Last edited:
Office_Shredder said:
Your average is too high because you're taking two orders as your endpoints.

Here's a conceptual example: suppose you have two orders that are actually one month apart, 30 days, for 250 dollars each and you add up the values in rows 2 and 3 just like you did here. Then you get 500/30*30=500/month not 250/month.
Yes, this is what I assume is happening; I just haven't figured out which ones.
 
OmCheeto said:
B11 strikes me as a tad too high. It seems to imply that you only averaged the last 4 purchases.
I get 21.67 for the last 6 weeks.
Yeah, i think you're right.

OmCheeto said:
C11 also seems odd, as it appears you've averaged all 7 weeks. I think as before, this should only be the average for the last 6 weeks, where I get $151.17
This is what I thought i was doing, taking only six weeks. I checked my formulae but I'll check again.
OmCheeto said:
I also get slightly different solutions in my C13 & C15, though nothing close to your $280.26
Thanks. I'll revisit those numbers.
 
  • #10
DaveC426913 said:
Yes, this is what I assume is happening; I just haven't figured out which ones.

C12 should be something line sum(C2:C8)/(D11+DATEDIF(A2:A8))/30)

Adding D11 to the DATEDIF (before converting into months)
 
  • Skeptical
Likes DaveC426913
  • #11
OmCheeto said:
B11 strikes me as a tad too high. It seems to imply that you only averaged the last 4 purchases.
Oops. Had a comma where there should be a colon.
1751031472925.webp



Office_Shredder said:
C12 should be something line sum(C2:C8)/(D11+DATEDIF(A2:A8))/30)

Adding D11 to the DATEDIF (before converting into months)
That does seem to give a better number, although I'm not entirely sure of the rationale.

1751032115812.webp
 
  • #12
DaveC426913 said:
It seems to me that's going to compound any rounding errors.

Presumably, any derived values should only be derived from raw data, not derived data.
As a general rule I agree. But a day seems like the shortest time interval of interest and the longer intervals overlap variously. I have not gotten far enough into the weeds here to see the fine detail, but there is no reason a priori that the daily average should be inexact. (The displayed number can be rounded by Excel format as I recall......its been a while )
 
  • #13
DaveC426913 said:
That does seem to give a better number, although I'm not entirely sure of the rationale.

You should think of a single order as the order happening and then a bunch of days until the next order. So the first data point you have is you spent 158 dollars and then waited 20 days until the next order. Then you spent 158 dollars and waited 15 days until the next order. The very last data point is you spent 158 dollars on June 18th and then... How long until the next order? The formula you picked basically says 0 days since you included the cost of that order but you don't have any time included until the order that comes after.

An alternate fix would be to just not include the last cost in the numerator but use your original denominator. There are merits for each approach.
 
  • #14
hutchphd said:
As a general rule I agree. But a day seems like the shortest time interval of interest and the longer intervals overlap variously. I have not gotten far enough into the weeds here to see the fine detail, but there is no reason a priori that the daily average should be inexact. (The displayed number can be rounded by Excel format as I recall......its been a while )
Yes, I suppose so.

Cost per month could just be cost per day (G10) x 30*.

* works out perfectly in this case because of Feb: i.e. 28+31+30+31 == 120.

Although now I get wildly different number for cost per month: $204 (6.80x30) instead of $240.
 
  • #15
DaveC426913 said:
Cost per month could just be cost per day (G10) x 30*.
This makes no sense: the year is not 360 days long*. The best way to convert a cost per day to a cost per month is usually to multiply by ## \frac{365.25}{12} = 30.4375 ##: this minimizes the error due to different month lengths and leap years over the long term. Alternatively you can use a "standard" year of 365 days which is less than one part in a thousand different.

DaveC426913 said:
Although now I get wildly different number for cost per month: $204 (6.80x30) instead of $240.

$6.80 is the MINIMUM cost per day, it cannot therefore also be the mean.

Office_Shredder said:
You should think of a single order as the order happening and then a bunch of days until the next order. So the first data point you have is you spent 158 dollars and then waited 20 days until the next order.

Yes, this is the best approach. During the 114 days between 24 Feb and 18 June you have ordered (and presumably comsumed) a total of 105 units at a cost of $907. You have not consumed any of the 20 units you ordered on 18 June so you should ignore these.

## \dfrac{$907}{114} \approx $7.96 ## per day or $242 per month.

You could refine these calculations by keeping a tally of how many units you have in stock to increase the accuracy of how many you consume in each interval.

* in financial markets many different conventions are used: these are almost always based on (i) tradition and (ii) maximizing profit for the issuer and are therefore of no use outside their individual context.
 
  • #16
pbuk said:
This makes no sense: the year is not 360 days long*.
If this data spanned a year, that would make sense, but since the data currently only spans a few months (114 days), the average yearly month length will produce inaccurate results for this data.

pbuk said:
$6.80 is the MINIMUM cost per day, it cannot therefore also be the mean.
Exactly. So it's prolly wrong.

pbuk said:
You could refine these calculations by keeping a tally of how many units you have in stock to increase the accuracy of how many you consume in each interval.
Yes, I generally reorder when I drop below four or so. Takes about two days for delivery.

Shoot! I have to re-order! I just ran out! thnak for the reminder!
 
Back
Top