• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Forecasting: Phasing of revenue based on seasonality assumptions

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).
 

Attachments

  • Forecasting based on Seasonality.xlsx
    23.6 KB · Views: 22
Hi Kenneth,

Welcome to Chandoo.org forum.

1. You have dump your whole problem in one shot. Which is making it difficult to understand.

2. You said, "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." ---> As far as I know Machine utlization % will depend upon its condition or health of the machine. How well you have maintained it. How much time in a year will it remain breakdown, and if it is breakdown how fast it is recovered. Than how much time is spent upon its schedule & preventive maintainance. So it can be calculated based on these data from the machine histroy & if you don't have a machine histroy I doubt you have to go with some assumption only. Because machine utlilization % will depend upon ita availabilty %.

Say if a machine is Available for 70% in the year you can use it 100% of that 70%. So from the data that you had provided it is not possible to calculate that.( I think so, if you have done this than please share that).

3. Break your problem in chunks one by one with refrence to data that is used for it and required output.

Kindly, give a thought over it and rephrase your proble. It may also happen that some Ninja will come up with some solution in a mean time.

Regards,
 
Hi, Somendra. Thanks for your reply.

First off, let me provide you with my starting point.

1) Machine utilization % (Time Utilization) is an assumption we set for each machine in our fleet based on what you mentioned (demand + down time due to maintenance). We assumed that the machine is available 100% of the time and that Machine utilization is a measure of how much of the time the machine is leased to a customer. So lets have that as a basis for my problems.

2) Seasonality assumptions are percentages we have set based on rental history.


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%

Problem 1:
TU % per month becomes higher than 100% when using my formula (which I don't know is correct):
Average TU % * Seasonality * 12 months

Lets say that Average TU % are the following, and we want to calculate TU % for June (high seasonality):
50% for Machine A
65% for Machine B
90% for Machine C

When using my formulas, we get:
Machine B: TU 65 % * 10,625% (seasonality) * 12 months = 82,875 % TU in June
Machine C: TU 90 % * 10,625% (seasonality) * 12 months = 114,75 % TU in June

As you can see, Machine B is within maximum TU % per month because the Average TU % that we set was only 65%. Machine C, however, is utilized 114% of the available time (21 days) in June, i.e. 24 days (114,75% * 21 days), which is not possible.

My question for problem 1 is, how do I calculate correct TU percentages per month based on the seasonality assumptions and the Average TU % (for the whole year) that I've provided for Machine C, without TU % in some months becoming higher than 100% when Average TU % is set high (as for Machine C)?

Problem 2:
Lets also say:
Daily rate: EUR 100
Number of days per month: 21
Maximum revenue per month: 21 days * EUR 100 = 2.100 EUR

Machine C:
TU 90 % * 10,625% (seasonality) * 12 months = 114,75 % TU in June
114,75% * 21 days * EUR 100 = EUR 2.341

Revenue in June for Machine C becomes EUR 2.341 with my formula, but it is only possible to earn EUR 100 * 21 days = EUR 2.100 per month. The amount over EUR 2.100, I want to be distributed to other months, based on the same seasonality assumptions. I.e. I want to have a constraint on Time Utilization (and revenue) per month of 100% and EUR 2.100.

My questions:
a) Are my formula calculations incorrect?
b) Are there formulas for distribution (e.g. statistical or normal formulas) that will distribute revenue to other months when maximum TU% or revenue per month is reached when setting Average TU % of 90 % for Machine C? Then, there need to formulas checking those other months as well, so that TU % and revenue don't go over the mentioned constraints.

Hope this makes my problems clearer.

Any help would be much appreciated.

Kenneth
 
See attachment for my last reply, where calculated Machine Utilization (TU %) and revenue for some months exceeds what is actually possible - see red fill.
 

Attachments

  • Machine C - TU % per month exceeds limits.xlsx
    35 KB · Views: 7
One quick question

Say for Jan (Month 1) seasonality is 5% and day are 21 so machine can be available for 5% of 21 days i.e. 1 day. Is it?

Regards,
 
Hi,

See the attached file, Yellow cells.

Just advise if any issue.
Regards,
 

Attachments

  • Machine_C_-_TU_%_per_month_exceeds_limits(1).xlsx
    10.4 KB · Views: 9
Thanks, Somendra. I will take a look at your file.

To answer your question about seasonality of 5% i January:
The seasonality assumptions are estimated and were used in our previous forecast (budget). In the budget we assumed that 5% of total budgeted revenue (for the whole year) would come in January. I have then used these assumptions for the forecast that I'm working on now, but want to apply them in calculations of machine utilization % and revenues per month.

Seasonality of 5% in January then means that, overall, 5% of all forecasted revenue should be in January. The calculated machine utiliziation based on seasonality could then be used to say how many days the machine were rented to customers. E.g. TU % January of 50 % * 21 days = 10,5 days.
 
Somendra, I think you are onto something with the formulas in the yellow cells.

However the guestimate of Average TU % for the whole year (if set to 100% for Machine C) does not equal the average of calculated TU percentages per month.

This is because your formulas in the yellow cells says that TU % for each month should be 100% if the calculations equals to or exceeds 21 days. But what about the months where number of days exceeds 21 days? The days over 21 days should be distributed to other months (also based on seasonality - as a ratio based on remaining months where TU % are not 100%), so that the average of all 12 months' TU percentages equals the same as the guestimate of Avg TU % for the whole year.

I tried uploading a new file, but I get an error. Please let me know if you have any questions.
 
Hi Kenneth,

If that is the case, Say For Jan seasonality 5% so 5% of the total revenue (25200) i.e. 1260 should come in Jan. For getting 1260 in Jan with the day rate of 100 machine should work for 12.6 days to TU% in jan wit 21 days will come to 60%.

Just see if this is correct.

Regards,
 
This is correct. But using your calculation for June, with a seasonality of 10,625%, the machine will work for over 21 days (which isn't possible), and thus TU % will be over 120%.

The revenue in June would be 10,625% * total revenue (25200) = 2.677,50. Divided by day rate EUR 100 gives 26,77 days (> 21 days which is the total possible days to have the machine on rent per month). Total possible revenue per month EUR 100 * 21 = 2100. So the 2677,50 - 2100 = 577,50 should be distributed to other months where TU % and revenue have not yet maxed out - still using the seasonality percentages for re-distributing the amount over 2100 (if possible). Is there a way to do this?
 
Wait ... Wait

But if we distribute it to other months than seasonality % will increase. Are you looking for an adjusting seasonality.

Regards,
 
Remember that seasonality percentages were used as assumptions for our previous forecast, and used to distribute total revenue for ALL machines to each month. Seasonality is now a guideline for distribution, and amounts each month should not equal seasonality exactly. This was used for total revenue for all machines.

Also remember that we guestimate an average TU% per machine. So let's say that we believe to utilize this machine for 85% of its time in our fleet. 85% for the whole year should then be the basis for calculating TU % for each month (and revenue). I hope that seasonality assumptions can be used to do so, instead of guestimating a percentage for each month for each and every machine separately (a lot of work).

So with an assumption of Average TU % of 85% for this machine:
January could have TU% of 65%, Feb 70%, Mar 78%, Apr 80%, May 90%, Jun 95%, Jul 90%, and so on… The average of all the calculated TU percentages for each month should then amount to the guestimated Average for the whole year (85%). You see?

The problem, however, arises when Average TU % (guestimate) is high and we use seasonality for distribution. Some months will have TU% over 100% (and revenue over 2100). This must be distributed to other months.

Lets say we guestimate 85% as Average TU% (whole year) for this machine. I would then like TU% to stop at 100% (and revenue at 2100) for months with high seasonality. Revenue over 2100 must be distributed to other months so that the total revenue for all months will add up to 85% * EUR 100 * 252 days.

I don't know if this is possible, but I know that redistributing the amounts over 2100 for high months must be done somehow.
 
Hi,

I would like to request you to upload a sample file with full work out for one machine with data entered manually as you explained above.

Regards,
 
Hi Kenneth ,

I am not sure that we are discussing the problem !

As I have understood the problem , there are some givens :

1. The maximum number of days per month is restricted to 21. Is this correct ?

2. Based on the seasonality figures , the maximum number of days is again restricted ; is this correct ? You are converting the seasonal index to cost , but since the cost per day is restricted to 100 , the fundamental constraint remains the number of days per month.

3. Some months , the maximum according to the seasonal index is less than 21 ; in other months , it is greater than 21.

4. We cannot exceed 21 because that is the ultimate constraint. Can we exceed the maximum as calculated on the basis of the seasonal index ?

This has nothing to do with the average TU , since the average TU can be any value from 0 through 100 ; the whole point is if we cannot exceed 21 , can we exceed what ever is the calculation based on the seasonal index ?

If we can , then and only then redistribution is possible ; if not , redistribution is not possible.

Please correct me where ever I am wrong. Let us first understand the issues involved before we talk of Excel formulae.

Narayan
 
Narayan,

1. Correct! Maximum number of days per month = 21

2. Correct! Maximum number of days per month = 21 even though seasonality is higher. Keep in mind that seasonality assumptions where used to distribute total revenue for all machines to months in our previous budget. I would like to have similar variations when calculation TU % per month, but when Average TU% for the whole year for one machine is high, the calculated number of days per month will be higher than 21 (because of the seasonality assumptions). Where this is the case, the number of days (or revenue for that matter) over 21 must be distributed to other months. So yes, the fundamental constriction is 21 days per month in any case.

3. Correct, based on the reason above.

4. We can never exceed the maximum of 21 days (100% TU) per month, even though we are using seasonality assumptions as a basis for creating variation in revenue per month.

Correct. Average TU% is an assumption (a best guestimate that we decide for each machine). In our previous budget we phased revenue with equal rate per month based on Average TU %. Now I want to used seasonality to create variations in monthly revenue, by recalculating guestimated TU % per machine into TU% per month (within constraints of 21 days and 100% per month of course).

My question is therefore:
Is there a way to distribute the days (and revenue) over 21 days (EUR 2100) to other months where high seasonality assumptions causes machines with high guestimate TU % (for the whole year) makes TU% and days exceed limitations of 100% and 21 days?

Is there a specific distribution formula where we can set constraints?
 
Hi Kenneth ,

I am still at a loss to understand the importance of revenue in all this !

The simple fact is 21 days a month over 12 months equals 252 ; this equates to 8.3333 % each month. If the seasonality index goes beyond 8.3333 % in any month , that month has a problem ! Redistribution is just a matter of taking the excess , adding all such excess and then redistributing it to the other months which have seasonal indices less than 8.3333 %. Where does revenue come into this ?

Narayan
 
Narayan Sir,

What I understood Seasonality % is the % of Total revenue/month. So a Seanality of 5% in Jan mean 5% of total revenue should come in Jan.

I may be wrong. Kenneth correct me If I am wrong.
And if we distrribute days which changes TU% will also change seasonality %.
Regards,
 
That is correct.

The reason for calculation is TU% per month is twofold:
1) To have an overview of what TU% per months is for, say, Excavators on top-level
2) To use TU% per month for calculating revenue per month

As you say, average is 8,3333% of total revenue each month, but reality is that in some months we earn more, some months we earn less.

It is time consuming to guestimate TU% for each month for every machine, so we set a guestimate for average TU % per machine for the whole year. From this I want to calculate TU% per month, and from TU% per month I want to calculate revenue per month.

Furthermore, you should know that we might buy a machine April 1 and sell it May 31. Instead of the revenue being 8,333% * 2 months * total guestimated revenue (based on Average TU%) for a machine, I want the forecasted to be more realistic and take seasonality into account. Forecasted revenue could then be (7,500% + 9,375%) * total guestimated revenue for this machine.
 
Hi Kenneth ,

I think there is some misunderstanding ; the revenue in any month cannot exceed 2100 , which is why you are redistributing.

Narayan
 
Somendra, you are correct.
5% seasonality means 5% of TOTAL revenue for the machine should come in January, but when guestimated Average TU % is 95% for instance, then redistribution must take place because of the constraints of 21 days per month (100% TU per month). It doesn't matter that seasonality then changes, because this is just an assumption for the total fleet, not for each machine specifically.

Please let me know if this was unclear.

Revenue in january for machine C do not have to be 5% of total revenue for the machine when the Average TU% is set high (say 95%). Seasonality is just a basis for creating variation instead of phasing revenue flat (8,333% per month - which is not inline with reality).
 
Hi Kenneth ,

So we come back to what I posted earlier ; all we need to do is consider only the time percentages ; where the percentage exceeds 8.3333 % , the excess needs to go somewhere else ; where the excess goes can be done in various ways ; first do we agree that we add up all the excesses above 8.3333 % , and then , if this excess is say 3.15 % , we redistribute this 3.15 % among those months where the utilization is below 8.3333 %.

Narayan
 
Hi Kenneth ,

So if the average TU considered is 95 % , we have room for accommodating only another 5 % ; no matter what you do , you cannot accommodate anything more than the 5 % which is 100 % - 95 %.

Narayan
 
Back
Top