Skip to main content
Adaptive Insights
Knowledge @ Adaptive Insights LLC, a Workday company.

Spreading an Amount Over Time with Start Date and Number of Months

The following shows the formulas needed for a modeled sheet that allows the user to:

  • Enter a total amount
  • Select a start date (only the month and year are used, no partial months)
  • Select the number of months from a pick list (small sample used but it can easily be expanded)
  • The system then spreads the total amount evenly from the start date forward based on the selected number of months. Other options commonly used include:
    • Build in a lag of x months before starting the spread
    • Weight each month differently (front loading or back loading)
    • Every other month

The following Modeled sheet is the basis for the examples:

clipboard_e333ffc845fe4648f8a2cef3776ae8234.png

There are 3 columns that will be referenced in formulas/spreads:

  • ROW.Amount
  • ROW.Start_Date
  • ROW.Num_of_Months (text selector with a spread table associated with it)

This is a basic example that can easily be expanded on by adding dimensions.  The example sheet is included in this article so that you can optionally import it into your model to view it in action.

Steps for importing a modeled sheet

  1. Extract the XML file from the attached ZIP file   Sample_-_Spread_Amount.zip
  2. Navigate to Modeling -> Level Assigned Sheets (or User Assigned Sheets)
  3. Click Upload New Modeled Sheet
  4. Browse for the file and click Upload (this will create a sheet called Formula Sampels)

Below are the accounts, their description, and their formulas:

Total_Amount

Description:  An account that used the Amount and Start_Date columns to put the data into the correct month

Formula: 

iff(

  # Check that the month is the start date, otherwise 0

  this.version.positionof(this.month) = this.version.positionof(ROW.Start_Date.month),

  ROW.Amount,

  0

)

Result:

clipboard_e31e629169dc40cb654f373d57024c567.png

Number_of_Months

Description:  This is a text selector that is a column on the sheet and allows users to select the number of months to spread the amount over.  Whichever item in the list is first is the default selection.  The steps for adding this to your sheet are

  1. Drag over a Text Selector from the Data Entry Columns element
  2. Give it a name and a code
  3. Under the Text selector settings area add your values (6, 9, 12, 24 months for this example)
  4. Save
  5. Expand the Spread Lookups settings area and click Add new spread lookup
  6. Give it a name and click Save
  7. Click the “Edit lookup values” button next to the name of your table
  8. Enter your spread patterns here (sample below)
  9. Save

Setup:

clipboard_e8430919fcdc48e1963bfab161043f4e4.png

Spread table setup (this example is for even spreads):

clipboard_e2b8ded1d4ba17a312249598f490ffa5c.png

Monthly_Amount

Description:  This account will receive the Total_Amount spread across the months based on the users selected Num_of_Months

Setup:

clipboard_e7a390f1a398608773732ce14ea8531fd.png

Result:

clipboard_ebb0e25a4c831e467ec4190c589fecfaf.png