Kenneth Johan Angeltun
New Member
Hi.
I have a forecast due soon, and I need some help with the phasing of revenue per month.
Our business is rental of construction machinery, where we expect to rent about 1000 machines to our customers during 2014. I have the following assumptions:
Number of days in a year: 252
Number of days per month: 21
Seasonality:
Jan: 5,000%
Feb: 5,625%
Mar: 7,500%
Apr: 7,500%
May: 9,375%
Jun: 10,625%
Jul: 7,500%
Aug: 9,375%
Sep: 10,625%
Oct: 10,625%
Nov: 8,750%
Dec: 7,500%
Total 100,000%
Lets say I have Machine A that we assume the following for:
Daily rate in EUR: 100
Average Time Utilization for the whole year: 50%
I then know that we can only make maximum (on average) the following per month
50% * 21 days * EUR 100 = EUR 1050
But, the thing is that we buy and sell machines in different months during the year, so that I would like to calculate Time Utilization percentages per months (based on seasonality assumptions) instead of having an assumed Average TU % for the whole year. That way I can calculate more realistic revenue per month for machines in our fleet, in stead of phasing each machine with flat revenue rate and then phasing out the total revenue per month at the end of the forecasting.
I also want to have the monthly Time Utilization % take the seasonality assumptions into account, so that I say that machine A will have a time utilization of X % per month (based on seasonality).
What formulas do I use to distribute total expected revenue for the whole year based on seasonality assumptions - with constraint that revenue don't exceed what is possible for those months where seasonality assumptions > what is possible.
Example:
June have seasonality assumption of 10,625 % of total revenue.
Maximum revenue for Machine A (month): EUR 100 * 100 % * 21 days = EUR 2.100
Maximum revenue for Machine A (year): EUR 100 * 100% * 252 days = EUR 25.200
June based on seasonality assumption 10,625 % would be: 25.200 * 10,625 = EUR 2.677,50
June: EUR 2.677,50 > max revenue of EUR 2.100 per month and basically says that we rent out Machine A for EUR 2.677,50 / EUR 100 (daily rate) = 26,775 days (which is not possible).
So, my question is:
a) How to distribute total revenue for Machine A based on seasonality assumptions without exceed monthly revenue constraint (EUR 2.100)?
b) If revenue for on month reaches maximum EUR 2.100, what goes over 2.100 should be redistributed to other months (when Average TU % - that we set per machine, increases) based on the same seasonality assumptions.
I tried the following formulas for calculating Time Utilization % per month:
Avg TU % (whole year) of 50 % * June 10,625 % * 12 months, and get a TU % for June of over 60 %. But the problem is when the average TU % for the whole year inreases. Then some months will have TU % higher than 100 % (rental days > 21 days) which is not logical.
I know this is very complicated, but are there some distribution formulas or other statistical methods that I can use in Excel without over-complicating my forecasting model?
Best regards,
Kenneth
Please see attachement (sheet 1) of how I tried to solve the problem, but it involves way too many calculations for distributing the revenue per month based on seasonality. It would make the model too complicated. I believe there must be a way to distribute revenue based on seasonality assumptions and restrictions of 21 days * day rate (EUR 100) per month).
I have a forecast due soon, and I need some help with the phasing of revenue per month.
Our business is rental of construction machinery, where we expect to rent about 1000 machines to our customers during 2014. I have the following assumptions:
Number of days in a year: 252
Number of days per month: 21
Seasonality:
Jan: 5,000%
Feb: 5,625%
Mar: 7,500%
Apr: 7,500%
May: 9,375%
Jun: 10,625%
Jul: 7,500%
Aug: 9,375%
Sep: 10,625%
Oct: 10,625%
Nov: 8,750%
Dec: 7,500%
Total 100,000%
Lets say I have Machine A that we assume the following for:
Daily rate in EUR: 100
Average Time Utilization for the whole year: 50%
I then know that we can only make maximum (on average) the following per month
50% * 21 days * EUR 100 = EUR 1050
But, the thing is that we buy and sell machines in different months during the year, so that I would like to calculate Time Utilization percentages per months (based on seasonality assumptions) instead of having an assumed Average TU % for the whole year. That way I can calculate more realistic revenue per month for machines in our fleet, in stead of phasing each machine with flat revenue rate and then phasing out the total revenue per month at the end of the forecasting.
I also want to have the monthly Time Utilization % take the seasonality assumptions into account, so that I say that machine A will have a time utilization of X % per month (based on seasonality).
What formulas do I use to distribute total expected revenue for the whole year based on seasonality assumptions - with constraint that revenue don't exceed what is possible for those months where seasonality assumptions > what is possible.
Example:
June have seasonality assumption of 10,625 % of total revenue.
Maximum revenue for Machine A (month): EUR 100 * 100 % * 21 days = EUR 2.100
Maximum revenue for Machine A (year): EUR 100 * 100% * 252 days = EUR 25.200
June based on seasonality assumption 10,625 % would be: 25.200 * 10,625 = EUR 2.677,50
June: EUR 2.677,50 > max revenue of EUR 2.100 per month and basically says that we rent out Machine A for EUR 2.677,50 / EUR 100 (daily rate) = 26,775 days (which is not possible).
So, my question is:
a) How to distribute total revenue for Machine A based on seasonality assumptions without exceed monthly revenue constraint (EUR 2.100)?
b) If revenue for on month reaches maximum EUR 2.100, what goes over 2.100 should be redistributed to other months (when Average TU % - that we set per machine, increases) based on the same seasonality assumptions.
I tried the following formulas for calculating Time Utilization % per month:
Avg TU % (whole year) of 50 % * June 10,625 % * 12 months, and get a TU % for June of over 60 %. But the problem is when the average TU % for the whole year inreases. Then some months will have TU % higher than 100 % (rental days > 21 days) which is not logical.
I know this is very complicated, but are there some distribution formulas or other statistical methods that I can use in Excel without over-complicating my forecasting model?
Best regards,
Kenneth
Please see attachement (sheet 1) of how I tried to solve the problem, but it involves way too many calculations for distributing the revenue per month based on seasonality. It would make the model too complicated. I believe there must be a way to distribute revenue based on seasonality assumptions and restrictions of 21 days * day rate (EUR 100) per month).