Hello all, I'm being asked to help with a bit of forecasting. My customer wants to tell me qty of deals for a year, and is asking me to map that to accumulated monthly revenue. In my uploaded mini-sheet, the forecast tab shows their expected deal count, my "Cal Sched" tab is where I've created a table to manually distribute deal counts across the months, and my "Cal Deals" tab is where I want to have my totals show up across the months.
So the first minisheet, for Deal Type "A" is 4, so per 2nd minisheet the Cal Sched, there would be deals signed in Jan, April, July, and October. The expected output is shown in the 3rd minisheet, Cal Deals tab. With an annual deal value of $180k, that would be $15k a month for each of the 4 deals that happened, and revenue would start in each those 4 months, and deals get signed and dollars start coming in and accumulate throughout the year, and then decrement as the 12 months of each deal expires.
I'm having a formula "writers block" on how to get from qty of deals to the monthly accumulated revenue. Any and all advice or input is greatly appreciated!! I'm happy to hear about any easier ways of doing what I'm trying to accomplish, thanks!
Thanks,
Gary
So the first minisheet, for Deal Type "A" is 4, so per 2nd minisheet the Cal Sched, there would be deals signed in Jan, April, July, and October. The expected output is shown in the 3rd minisheet, Cal Deals tab. With an annual deal value of $180k, that would be $15k a month for each of the 4 deals that happened, and revenue would start in each those 4 months, and deals get signed and dollars start coming in and accumulate throughout the year, and then decrement as the 12 months of each deal expires.
I'm having a formula "writers block" on how to get from qty of deals to the monthly accumulated revenue. Any and all advice or input is greatly appreciated!! I'm happy to hear about any easier ways of doing what I'm trying to accomplish, thanks!
Thanks,
Gary
| Forum Upload Sheet.xlsx | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | Forecast - 2023 | ||||||||
| 2 | Deal Type | Deal Quantity | Subscribers | Monthly Transfer Cost | Activation | Annual Deal Value | |||
| 3 | A | 4 | 500 | $ 7.50 | $ 45,000.00 | $ 180,000.00 | |||
| 4 | B | 1 | 3,000 | $ 5.50 | $ 30,000.00 | $ 198,000.00 | |||
| 5 | C | 1 | 10,000 | $ 3.50 | $ 75,000.00 | $ 420,000.00 | |||
| 6 | 2023 Forecast: | $150,000.00 | $798,000.00 | ||||||
| 7 | |||||||||
| 8 | |||||||||
Forecast | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E3 | E3 | =F3*0.25 |
| F3:F5 | F3 | =(D3*C3*12)*B3 |
| E6 | E6 | =SUM(E3:E5) |
| F6 | F6 | =F3+F4+F5 |
| Forum Upload Sheet.xlsx | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
| 1 | # of Deals in Deal Month | |||||||||||||||
| 2 | # of Deals | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | |||
| 3 | 1 | 1 | ||||||||||||||
| 4 | 2 | 1 | 1 | |||||||||||||
| 5 | 3 | 1 | 1 | 1 | ||||||||||||
| 6 | 4 | 1 | 1 | 1 | 1 | |||||||||||
| 7 | 5 | 1 | 1 | 1 | 1 | 1 | ||||||||||
| 8 | 6 | 1 | 1 | 1 | 1 | 1 | 1 | |||||||||
| 9 | 7 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | ||||||||
| 10 | 8 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | |||||||
| 11 | 9 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | ||||||
| 12 | 10 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | |||||
| 13 | 11 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | ||||
| 14 | 12 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | |||
Cal Sched | ||||||||||||||||
| Forum Upload Sheet.xlsx | |||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | |||
| 1 | 1/1/23 | 2/1/23 | 3/1/23 | 4/1/23 | 5/1/23 | 6/1/23 | 7/1/23 | 8/1/23 | 9/1/23 | 10/1/23 | 11/1/23 | 12/1/23 | 1/1/24 | 2/1/24 | 3/1/24 | 4/1/24 | 5/1/24 | 6/1/24 | 7/1/24 | 8/1/24 | 9/1/24 | 10/1/24 | 11/1/24 | 12/1/24 | |||
| 2 | A SaaS | 15000 | 15000 | 15000 | 30000 | 30000 | 30000 | 45000 | 45000 | 45000 | 60000 | 60000 | 60000 | 45000 | 45000 | 45000 | 30000 | 30000 | 30000 | 15000 | 15000 | 15000 | 0 | 0 | 0 | ||
| 3 | |||||||||||||||||||||||||||
| 4 | B SaaS | ||||||||||||||||||||||||||
| 5 | |||||||||||||||||||||||||||
| 6 | C SaaS | ||||||||||||||||||||||||||
| 7 | |||||||||||||||||||||||||||
Cal Deals | |||||||||||||||||||||||||||
Last edited:
