Driver formula help

Walker32
Walker32
in Modeling
Hi, 
 
I am hoping someone can help explain / walk me through what this driver formula is doing. 
 
IF
    Driver Method <> 'SYS002 - Model Lookups'.'Flat to PY - 70% Owned Sales'
THEN
    0
ELSE
    LAG('Total Output ($) [Staging]', 12, 0) * 0.3 + LAG('Total Output ($) [Staging]', 12, 0) * 0.7 * 'DAT002 - Sales Data'.Owned Sales / ('DAT002 - Sales Data'.Owned Sales - LAG('DAT002 - Sales Data'.Owned Sales, 12, 0))
 
My thought process:
-  Taking prior years month (may-21) and multiplying by .3
LAG('Total Output ($) [Staging]', 12, 0) * 0.3 + LAG('Total Output ($) [Staging]', 12, 0) * 0.7 * 'DAT002 - Sales Data'.Owned Sales / ('DAT002 - Sales Data'.Owned Sales - LAG('DAT002 - Sales Data'.Owned Sales, 12, 0))
 
- adding the value of prior years month (may-21) multiplied by .7 multiplied by Current month's total owned sales 
LAG('Total Output ($) [Staging]', 12, 0) * 0.3 + LAG('Total Output ($) [Staging]', 12, 0) * 0.7 * 'DAT002 - Sales Data'.Owned Sales / ('DAT002 - Sales Data'.Owned Sales - LAG('DAT002 - Sales Data'.Owned Sales, 12, 0))
 
- dividing by the difference of Current month's total owned sales (may22) and Prior month's (May21) total owned sales.
LAG('Total Output ($) [Staging]', 12, 0) * 0.3 + LAG('Total Output ($) [Staging]', 12, 0) * 0.7 * 'DAT002 - Sales Data'.Owned Sales / ('DAT002 - Sales Data'.Owned Sales - LAG('DAT002 - Sales Data'.Owned Sales, 12, 0))
 
However I am not getting the same value that the model is showing (associated ROF). Attached screenshot for reference.
 
Please let me know what I am missing here.
Tagged:

Best Answer

  • ChrisStauffer
    ChrisStauffer
    Answer ✓

    Hi Walker,

     

    Sometimes it helps to build a calc in pieces then consolidate and delete extra line items.

     

    LAG('Total Output ($) [Staging]', 12, 0) * 0.3 = 22,075,640    (73,585,465 * .3)

    +

    LAG('Total Output ($) [Staging]', 12, 0) * 0.7 = 51,509,826    (73,585,465 * .7)

    *

    DAT002 - Sales Data'.Owned Sales / ('DAT002 - Sales Data'.Owned Sales - LAG('DAT002 - Sales Data'.Owned Sales, 12, 0) =  20.    (1,702,202 / 1,702,202 - 1,615746)

     

    then I get 1,014,158,971 when I multiply 51,509,826 * 20

     

    when I add 1,014,158,971 to 22,075,640,  I get 1,036,234,610 which is close to your number so i assume I'm off by rounding.

     

     

Answers

Categories