I am looking to take total historical sales and increase them by a YOY growth rate, and then split out the sales to different sales channels using a % split. I am struhggling to modify any of the existing revenue models and wondered if anyone can help. I have attached a spreadsheet with a basic outline of what I am trying to achieve
Attachments:
Hi Peter,
Just to clarify, in the end financial statements, will you need to show all sales channels, or are you going to be matching it as total sales?
There are a few methods to tackle this one, so your response will help.
Jun
Hi Jun
I only need to show one total sales line in the financial statements, but they may need to remain split to work out the correct working capital. Each sales channel has different working capital movements e.g. some are commisson only and the company keeps all the cash / sales it receives. Some channels the company receives the cash, keeps their 10 % commission and then passes the 90% on to a third party.
Regards
Peter
Hi Guys,
Peter - Is the main issue here that you have a single historical revenue line but want to forecast multiple revenue categories?
In your example, you've provided split % assumptions in every forecast period, as well as % Total Sales YOY growth assumptions. Would it be alright to simplify this by having a single column of split % assumptions which is applied once in the first forecast period, after which the % Total Sales YOY growth assumptions are then applied to the split out revenue categories? I noticed in your example that the split by sales channel % assumptions are currently the same for each category across all periods, so based on your current assumptions this could be simplified.
Personally, I'd recommend doing the one-to-many split/allocation once at the end of the historical data periods, then allow the user to enter forecast YOY growth rate % assumptions for each split out revenue category, as I'd imagine model users are likely to want to enter different YOY growth rate assumptions for different categories.
Let me know if you think this would work, and I can mock up an example for you tomorrow.
M.
Hi Michael
Yes what you are suggesting would work fine, if you could mock something up that would be great.
Regards
Peter
Gentlemen,
Sorry Mike - couldn't help myself, had to have a go at your suggestion.
Peter, I'm sure Mike will come up with a cleaner solution, but here's my attempt.
Jun
Hi Jun
Thanks for your work on this. It looks like your solution will work just fine. How do I convert your example model to a monthly timeseries and import into my exisiting model as a revenue module?
Peter
Hi Peter,
Easiest way is to pull up a Monthly H&F model and refer to the category and link-out logic that I used in the Annual example.
Jun
This is exactly what I was envisaging, fantastic work Jun. ;-)