1
$\begingroup$

I'm not sure if I can accurately describe my situation.

We have data (a starting value usually between 30000 and 50000) from four different sources, and each day the values for each source increases or decreases. Each day we calculate the increase or decrease percentage overall - we're not so interested in each dataset individually, rather the change across all of them together - and at the end of the week we also calculate the weekly increase or decrease in value (and monthly, and so on)

example

Day Start Value End Value Pct Change
1 150000 153000 2.0%
2 153000 154530 1.0%
3 154530 148348.8 -4.0%
4 148348.8 152799.3 3.0%
5 152799.3 150507.31 -1.5%

And at the end of the week we just use the difference between the start value and the end value to calculate the week's change (is this correct?)

This seems to work out alright, except that occasionally we add a new datasource and include its increase/decrease in the daily/weekly calcs. And these new sources don't always get added a the beginning or end of a week, so it makes calculating the weekly percentages a bit more complicated for us.

So for example if we added a new datasource with a start value of 30000 at the end of day 3

Day Start Value End Value Pct Change
1 150000 153000 2.0%
2 153000 154530 1.0%
3 154530 148348.8 -4.0%
4 178348.8 183699.3 3.0%
5 183699.3 180943.8 -1.5%

We can't just take the start and end difference, as we don't want to include the new source's start value, just the change after being added (along with the other change values)

How do we accurately calculate the weekly percentage change while allowing for a new dataset being added?

My boss says we should just average the start values and use the change total against that average... Change of 0.58%

My colleague thinks we should be able to just add the daily percentages... Change of 0.5%

And the way I was doing it was applying the daily change percentage to a base number, eg 100000

100000 + 2% = 102000
102000 + 1% = 103020
103020 - 4% = 98899.2
98899.2 + 3% = 101866.2
101866.2 - 1.5% = 100338.2

So from 100000 base to 100338.2 we'd have a weekly change of 0.3%

What would be the correct way to accurately calculate the weekly change here?

We're clearly not mathematicians, so after an accurate but understandable explanation if possible. If I understand the solution the result will be entered into a google sheets formula.

$\endgroup$

1 Answer 1

Reset to default
0
$\begingroup$

Imagine a week consists of 2 days (to keep it simple).

Day 1
Start value $S_1$ = 20
End value $E_1$ = 30
Change = $C_1 = E_1 - S_1 = 30 - 20 = 10$
Percentage change for day 1 = $P_1$ $P_1 = \frac{C_1}{E_1} \times 100 = \frac{10}{20}\times 100 = 50 \%$

Day 2
Start value $S_2$ = 50
End value $E_2$ = 70 Change = $C_2 = E_2 - S_2 = 70 - 50 = 20$
Percentage change for day 2 = $P_2$
$P_2 = \frac{C_2}{S_2}\times 100 = \frac{20}{50}\times 100 = 40\%$

For the week (remember our week is exactly 2 days)

Start value = $S_w = S_1 + S_2 = 20 + 50 = 70$
End value = $E_w = E_1 + E_2 = 30 + 70 = 100$
Change = $C_w = E_w - S_w = 100 - 70 = 30$
Percentage change for the week = $P_w$
$P_w = \frac{C_w}{S_w}\times 100 = \frac{30}{70} \times 100\approx 43\%$

Notice:

  1. $P_w \ne P_1 + P_2$ i.e. we can't just add the percentages like one of your colleagues recommended. Each percentage change is calculated off of a different start value.
    If the start values were identical $(S_1 = S_2)$ then the actual change for the week would be $P_1 \times S_1 + P_2 \times S_2 = (P_1 + P_2)\times S_1 = (P_1 + P_2)\times S_2$ i.e. we can add the percentages. This, however, is not true in your case.
  2. For new data sources, say you have the start value $S_n$ and the change $C_n$. You would need to add $S_n$ to the total of all the start values and $C_n$ to to the total of the changes. From this you can calculate the percentage change for the week (don't forget our make-believe week is 2 days. You can extrapolate the result to a normal 7-day week), inclusive of the new data, as below:

$\frac{C_1 + C_2 + C_n}{S_1 + S_2 + S_n}\times 100$

$\endgroup$
6
  • $\begingroup$ I don't think this gives me the weekly growth. This appears to be average daily growth. How do I calculate weekly growth, particularly when I add to my dataset? $\endgroup$
    – Midavalo
    Aug 30 at 23:35
  • $\begingroup$ Assumption: The initial value is for a week and the final value is also for a (next) week. So, if the initial value is collected for Monday, the final value is for next Monday (a week passes between two Mondays). If we have only daily data, we could extrapolate for the week if the rate of change is constant. So, if the daily change is 4, for the week it's 7 × 4 = 28, but I don't think the daily change is gonna be a constant . Here's where statistics might come in handy. Find the mean weekly rate of change and, assuming it's W, for 5 weeks, the change is = 5 × W. $\endgroup$
    – Agent Smith
    Aug 31 at 6:36
  • $\begingroup$ @Midavalo, I edited my answer. I hope it's clearer now. $\endgroup$
    – Agent Smith
    Aug 31 at 6:51
  • $\begingroup$ Thanks, this works most of the time, however it doesn't appear to work when the base data is increased (like when we add a new datasource that already has a base reading) or maybe I've missed something? Perhaps I haven't explained it well enough in my question - I will think on how I can clarify it $\endgroup$
    – Midavalo
    Sep 1 at 17:23
  • $\begingroup$ @Midavalo, I've edited my answer. Hope that clears things up. $\endgroup$
    – Agent Smith
    Sep 1 at 18:59

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .