Gross Deal Qty and Revenue Spread into Monthly Values?

GaryV

New Member
Joined
Mar 23, 2018
Messages
14
Hope I can explain this correctly, and am posting this well. I'm trying to do some sales forecast calculating. The first image is the "source data", customer wants to enter number of deals of each of 3 types, for the entire year. I'm being asked to map that into expected revenue by deal type by month. The 2nd table is what I'm trying to auto-create from the source data.

I want automatically to convert, as example, the 4 deals in the 2023 "essential" table into, 1 deal in the first qtr, 1 in the 2nd qtr, etc., while splitting up the Activation and SaaS prices. (SaaS = Annual Deal Value - Activation). The Activation is a one-time fee and would happen 2 months after signature. The Annual Revenue start 4 months after signature and would be split up by month, for 12 months. I also want to accumulate the Annual Revenue of each deal type, to reflect a total revenue by deal type.

Any and all help is greatly appreciated, this one is stumping me! The source data format cannot change. The second table can change, if there's a more intelligent way of executing this and getting to accumulated revenue by month.

Thanks a LOT!!
GaryV

Screen Shot 2023-07-15 at 5.33.04 PM.jpg



Screen Shot 2023-07-15 at 5.33.56 PM.jpg
 

Attachments

  • Screen Shot 2023-07-15 at 5.16.47 PM.jpg
    Screen Shot 2023-07-15 at 5.16.47 PM.jpg
    192.9 KB · Views: 7
  • Screen Shot 2023-07-15 at 5.17.07 PM.jpg
    Screen Shot 2023-07-15 at 5.17.07 PM.jpg
    235.4 KB · Views: 8

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top