Background
I am working on a problem for a rudimentary business intelligence initiative. I'm in IT by trade, so this level of mathematics is a bit of a stretch for me.
Problem
Finance projects sales on a monthly basis. I want to break it down to a daily sales number, with a nod to the average percent sales on a given day of the week.
Sample input data
- $S = $ Month sales projection: $50,000$
- $[DP]=$ Percent sales based on day of week
| Su | Mo | Tu | We | Th | Fr | Sa |
|---|---|---|---|---|---|---|
| $0$ | $0$ | $18$ | $19 $ | $20$ | $22$ | $21$ |
- $[DN] = % $ Number of each sales day in month:
| Su | Mo | Tu | We | Th | Fr | Sa |
|---|---|---|---|---|---|---|
| $0$ | $0$ | $4$ | $4$ | $4$ | $4$ | $5$ |
My Attempts
First, I've done two different routes to get a 'workable' answer, but I'm not satisfied. Both involve an error correction step, which is my indicator that this is not the correct method.
Attempt #$1$
$[X] = \frac{[DN]}{Average[DN]}(S\cdot [DP])$
$[Y] = (S-sum([X])\cdot [DP] $
Daily Budget $= \frac{([X] + [Y])}{[DN]}$
Result
| Su | Mo | Tu | We | Th | Fr | Sa |
|:---|:---|:---|:---|:---|:---|:---|
| $0$ | $0$ | $2137.5$ | $2256.25$ | $2375$ | $2612.5$ | $2495$ |
Attempt #$2$
$ X = (\frac{S}{sum([DN]})\cdot(1+DP-Average[DP])\cdot[DN] $
$ [Y] = (S-sum([X])\cdot [DP] $
Daily Budget $= \frac{([X] + [Y])}{[DN]}$
Result
| Su | Mo | Tu | We | Th | Fr | Sa |
|:---|:---|:---|:---|:---|:---|:---|
| $0$ | $0$ |$2332.26$|$2356.01$ | $2379.76$ | $2427.26$ | $2403.76$ |
Desired Result
Numerically, I don't know what the correct numbers should be. But eliminating the error correction step (the $[Y]$) seems like a good idea.
Thank you for any input you may have.