Formula for blended company sales forecast Current / Next Year

Models
1

Hi P123 and community,

I was using CurFYsalesMean in one of my custom universe. Then I realized that it might introduce some distortions depending on where we are in the company year.

I then created blended Current Year / Next Year Sales forecast depending on where we are in the company year.
i.e. similar to #SPEPSCNY as created by P123 (https://www.portfolio123.com/doc/doc_detail.jsp?factor=%23SPEPSCNY&popUpFullDesc=1)

Here is my attempt. It runs OK. I would appreciate feedback / correction from P123 or the community in case I missed something on the methodology.

Methodology:

  • Use WeeksToY.

  • When between:

  • [0-13] → Q4 of company yearly cycle → 75% NextYear + 25% Current Y

  • [14-26] → Q3 → 50% NY+50% CY

  • [27-39] → Q2 → 25% NY + 75% CY

  • [40 - 52+] → Q1 → 100% CY

  • Formula: $BlendCNYsales = eval(trunc(WeeksToY/13)<=1,0.75nextFYsalesmean+0.25curFYsalesmean,eval(trunc(WeeksToY/13)<=2,0.5nextFYsalesmean+0.5curFYsalesmean,eval(trunc(WeeksToY/13)<=3,0.25nextFYsalesmean+0.75curFYsalesmean,curFYsalesmean)))

Feedback appreciated.
Thanks,

Jerome

2

How about using QtrComplete which reports the latest quarter closed by a SEC filing? It counts from 1 to 4. That should simplify some of the formula. And I like the idea.

Walter

EDIT
Maybe something like;

$BQ1=Eval(QtrComplete=1, 0.25*nextFYsalesmean+0.75*curFYsalesmean,0)
$BQ2=Eval(QtrComplete=2, 0.50*nextFYsalesmean+0.50*curFYsalesmean,0)
$BQ3=Eval(QtrComplete=3, 0.75*nextFYsalesmean+0.25*curFYsalesmean,0)
$BQ4=Eval(QtrComplete=4, 1.00*nextFYsalesmean+0.00*curFYsalesmean,0)

$BlendCNYSales=$B1Q+$B2Q+$B3Q+$B4Q

Or

$BlendCNYSales=QtrComplete*0.25*nextFYsalesmean+((4-QtrComplete)*0.25)*curFYsalesmean
3

We could use a Switch() function like Excel already has. This is one example of where it’s helpful.

In Excel:

=SWITCH(A3,1,"Sunday",2,"Monday",3,"Tuesday")

That’s much better than:

=IF(A3=1,"Sunday",IF(A3=2,"Monday",IF(A3=3,"Tuesday",NA()))
4

Thank you wwasilev. You are probably right and it is likely better to use QtrComplete.

However I think that $BQ4 as written is off. After the 4th quarter results are published, we are into a new current year, no?

So should be → $BQ4=Eval(QtrComplete=4, 0.00nextFYsalesmean+1.00curFYsalesmean,0)

Thoughts?

Edit: just checked using MSFT. This seems to confirm the proposed amended version above

Thank you

Jerome
NB: of note, on 20 Aug 2019, there is 1 N/A in the PRussell1000 for QtrComplete

5

Yes, Jerome, I think you’re right with regards to $BQ4.

I think this is now correct;

$BlendCNYSales=Eval(QtrComplete=4,0,QtrComplete)*0.25*nextFYsalesmean+((4-Eval(QtrComplete=4,0,QtrComplete))*0.25)*curFYsalesmean

or

$BlendCNYSales=mod(QtrComplete,4)*0.25*nextFYsalesmean+(4-mod(QtrComplete,4))*0.25*curFYsalesmean

The code is untested, so let me know if you spot a problem.

Thanks, Walter