Workday Adaptive Planning Tips & Tricks

Workday Adaptive Planning Tips & Tricks: Data Integration and the Excel Spreadsheet Data Source

July 20, 2022 by Marc Assenza Leave a Comment

Did you know that Workday Adaptive Planning can use Microsoft Excel Workbooks as Data Sources for Data Integration?

It’s true! The process is surprisingly simple if a few basic rules and steps are followed:

Your column names, positions, and formatting should remain consistent once you decide upon the layout. In the example below, Period, Value, Account and Level will always be in the same order with the same consistent formatting on a moving forward basis.

The name of your worksheet within the workbook will need to remain the same. Within the Workday Adaptive integration, the worksheet name becomes the table name in the Design Integrations task pane. As a side note, if you have multiple worksheets as part of a workbook, each worksheet becomes an available table to use for that spreadsheet data source. See screenshot below:

ALL row and column values must be exactly that, values. There cannot be any formulas or summed totals on the spreadsheet

Import your Spreadsheet data source through the easy-to-use Actions Pane link named “Import Spreadsheet.”

Follow the automated prompting.

Ensure your workbook was uploaded by viewing it in the Data Sources data pane.

Open your Data Source and locate your table from the Data Components Pane.

Drag your table into the Staging area to review and query the data.

That’s all it takes to import an Excel Workbook as an Excel Spreadsheet Data Source. Be on the lookout for more Workday Adaptive Data Integration tips and tricks from me in the future!

Visit Revelwood’s Knowledge Center for our Workday Adaptive Planning Tips & Tricks or sign up here to get our Workday Adaptive Planning Tips & Tricks delivered directly to your inbox. Not sure where to start with Workday Adaptive Planning? Our team here at Revelwood can help! Contact us info@revelwood.com for more information.

Workday Adaptive Planning Tips & Tricks: Loan Amortization

July 6, 2022 by Robert Nordhagen Leave a Comment

Loan amortization schedules in Workday Adaptive Planning are slightly different than those done in Excel.

In Excel the typical amortization schedule looks like this:

In Workday Adaptive Planning most versions only go 1-5 years into the future. Creating a complete 10-25 year amortization schedule is not the best practice. Here is an example of an Amortization Schedule in Adaptive:

In Adaptive we show inputs in rows 2-8. Rows 10 through 14 are the formulas that calculate the interest per period, the principle and the declining principal balance.

Row 2: Input the initial loan balance for every month the loan will be active. If you are keying it in, as opposed to uploading a file from Excel, you can use Copy Forward rather than key it in every month.

Row 3: Input Balance (if acquiring the loan). Instead of the initial balance, put the balance as of assumption date.

Row 4: Principal Payment. This is used for variable rate loans

Row 5: The months will be the same just like the Initial Balance for every month that the loan is active.

Row 6: Amort Month is a counter that increases by 1 each month until the loan matures, then the field should go blank. In most cases the version will max out before the loan matures.

Row 7: Interest Rate %. This will be the same every month for fixed rate loans. For variable rate, forecast the expected rate by month.

Row 8: Draw Down. This is for additional payments.

The formula rows will calculate as follows:

Row 10: Beginning balance is equal to prior month ending balance.

Row 11: Total Payment. This is the most complicated formula and takes many scenarios into account using nested ifs. The basis of the formula is the payment formula; ie, Balance x i / ( (1+i) * (1- 1/( 1+I )^n ))

Row 12: Interest Payment. This takes the Beginning Balance * the Interest rate (monthly rate: rate/12)

Row 13: Principal Payment. This takes Total Payment – Interest Payment

Row 14: Ending Balance. This takes the Beginning Balance – Principal Payment – Draw Down

Follow up to Amortization is Conversion to Straight Line

Previously, we showed how to do a loan amortization in Adaptive Planning. Now we will go to the next step: Straight-line the interest according to GAAP in certain situations. Below is a loan amortization that takes the loan payments to maturity. The interest payments are summed. That sum is then divided evenly among the payments and the amortization schedule is redone with constant interest every month as shown in the second schedule.

This is tricky in Adaptive Planning because a typical Excel schedule is run out to term since there are almost always sufficient columns in an Excel sheet to handle all the monthly payments. However, in Adaptive Planning, a typical version will be only 3 to 5 years into the future so only loans within 60 months of maturity could follow the same pattern.

For any longer maturities we have to apply the following logic. For loans with a constant monthly payment (which is most loans), calculate the payment. Then multiply the payment by the number of payments such as $9541.10 * 120 months, which is equal to $1,144,932.00 (slightly different by $0.37 due to rounding). Subtract the initial balance $1,144,932 – $1,000,000 = $144,932 which is the total of the interest. Now the Interest Payment will be equal to the total interest divided by the number of payments as seen below.

Now the GL account for Interest Expense can be linked to SL_Interest_Pmt and the GAAP reporting of Straight Line interest will be in the P&L.

Workday Adaptive Planning Tips & Tricks: Choose the Correct TIME Syntax

June 22, 2022 by Michelle Song Leave a Comment

Are you familiar with all the TIME syntax in Workday Adaptive Planning? Do you know what is the best practice for each of them? This blog post will tell you the difference between them.

Below are the most common TIME syntax in Adaptive Planning.

Month(this)
Versionmonth(this) – returns the
This.year.positionof(this.month)
This.version.positionof(this.month)

In the following example, this instance has a custom calendar which is from July – June. The example is created using Month as the stratum. The same logics are applied to Quarter and Year.

Graphical user interface, application, table

Description automatically generated

In July 2021, Month(this) returns the month in the standard calendar, which is month 7. This syntax also assumes that July is from July 1^st to July 31^st. For example, if 7/30/2021 in a custom calendar is actually a date in Aug 2021, then versionmonth(July-2021) and versionmonth(Aug-2021) will return the same number. This same situation also applies to Versionmonth(this).

Versionmonth(this) returns the month of the version, which also follows the standard calendar and assumes July is from July 1^st to July 31^st.

Graphical user interface, table

Description automatically generated

This.year.positionof(this.month) returns the fiscal month of the year, which is month 1 for July-2021. This syntax references the custom calendar. For example, if 7/30/2021 in a custom calendar is actually a date in Aug 2021, then This.year.positionof(this.month) returns month 2 in the year, which is Aug 2021. Same situation applies to This.version.positionof(this.month).

Graphical user interface, text, application

Description automatically generated

This.version.positionof(this.month) returns the month in the version since the Start of Version. In this case, the Start of Version is July 2021, therefore it is month 1 in the version.

Other use cases and helpful formulas related to time.

Personnel Sheet:

For Headcount calculation that has a Custom Calendar and Start Date before the system date
- this.Year.positionof(this.Month) – correct result, but shown as RED (error) on Income Statement because the date is outside of the system date.
- versionmonth(this) – might result in incorrect result due to date overlaps in two months.
- Use iff(ROW.PartialHeadcount>0, 1,0) as a workaround.
Cumulative Salary
- Standard calendar:

iff(month(this)>1, ROW.Salary+ROW.CumulativeSalary[time=this-1], ROW.Salary)

Customized calendar:

iff(this.Year.positionof(this.Month)>1,ROW.Salary+ROW.CumulativeSalary[time=this-1], ROW.Salary)

Workday Adaptive Planning Tips & Tricks: Revenue Cohort Modeling

June 8, 2022 by Ailenette Cruz Leave a Comment

Do you know about cohort modeling in Workday Adaptive Planning?

A cohort model provides meaningful insight of data grouped into subsets based on any characteristic important to the company. The groups are called “cohorts.” Cohorts can be based on time (i.e. season, monthly, yearly), segment (i.e. commercial, residential), or size (i.e. low, medium, high), just to name a few. Cohorts follow a pattern of behavior that helps analysts project future trends. This can help companies focus efforts on lowering churn and optimizing revenue.

Cohort model vs. Regular model

Compared to a regular model, a cohort model provides a more granular view of the data. Projecting revenue at the cohort level helps companies understand outliers that would otherwise be missed in a regular revenue model.

For example, in a regular model, projected revenue shows overall revenue for the year and may show growth. However, if you dig further into the segments, you will see that one cohort, such as small businesses in the manufacturing industry, had a large decline in sales.

If this information was available early in the year, further decline could have been prevented and management could have strategized to avoid a blow to the bottom line in the following year. Maintaining and analyzing multiple cohort models on a regular basis is crucial for successful strategic planning.

Where to use cohort modeling

Subscription base revenue – tech, gaming, food, streaming, service, etc.
Contract billing – by milestone, period, ASC 606 amortization, etc
Seasonality base revenue
Freemium – lead conversion
Sales Rep capacity or conversion

How to build cohort models in Workday Adaptive Planning

Cohort model in Adaptive may comprise of multiple sheets. Below is an example of a cohort model structure.

1. Planned # of Customers/Cohort

Modeled sheet
Create number data entry column to input number of customers
Create calculated account and link the data entry column
Input by cohort dimension – this can be by month/period, type of cohort, etc (ex. Jan, Feb, Mar – the month new customers are acquired)
No timespan

2. Cohort Drivers/Curves

Modeled sheet
Create number data entry columns, display as % (ex. 12 columns representing 12 months of the year) – this is where to input cascading percentages (aka curve)
Create calculated accounts for each data entry column and link
No timespan

3. Revenue per product/customer by month

Modeled Sheet
- With timespan

4. Cohort Waterfall

Cube sheet
Create standard account as a trigger – in this case using active month designation
- The active month designation to trigger what month the cohort waterfall calculates for each acquisition month
Create calculated account with formula using iff and switch (this serves as an index match)

iff(isblank(ACCT.Cohort_Waterfall.Cohort_ActiveMonths),blank(),

iff(this.version.isactuals,blank(),

ACCT.NewCustomers_byCohort.No_Customers[Sales Region=this, Sales Channel=this, Order Type=this, Products=this, Cohort Acquisition Month=this]

switch(ACCT.Cohort_Waterfall.Cohort_ActiveMonths,

1,ACCT.Cohort_Drivers.M1[Sales Region=this, Sales Channel=this, Order Type=this, Products=this],

2,ACCT.Cohort_Drivers.M2[Sales Region=this, Sales Channel=this, Order Type=this, Products=this],

3,ACCT.Cohort_Drivers.M3[Sales Region=this, Sales Channel=this, Order Type=this, Products=this],

4,ACCT.Cohort_Drivers.M4[Sales Region=this, Sales Channel=this, Order Type=this, Products=this],

5,ACCT.Cohort_Drivers.M5[Sales Region=this, Sales Channel=this, Order Type=this, Products=this],

6,ACCT.Cohort_Drivers.M6[Sales Region=this, Sales Channel=this, Order Type=this, Products=this],

7,ACCT.Cohort_Drivers.M7[Sales Region=this, Sales Channel=this, Order Type=this, Products=this],

8,ACCT.Cohort_Drivers.M8[Sales Region=this, Sales Channel=this, Order Type=this, Products=this],

9,ACCT.Cohort_Drivers.M9[Sales Region=this, Sales Channel=this, Order Type=this, Products=this],

10,ACCT.Cohort_Drivers.M10[Sales Region=this, Sales Channel=this, Order Type=this, Products=this],

11,ACCT.Cohort_Drivers.M11[Sales Region=this, Sales Channel=this, Order Type=this, Products=this],

12,ACCT.Cohort_Drivers.M12[Sales Region=this, Sales Channel=this, Order Type=this, Products=this],

0)))

Cohort modeling is a powerful feature in Workday Adaptive Planning. Try it out and see what insights you can discover.

Workday Adaptive Planning Tips & Tricks: Where Did My Parameters Go?

June 16, 2021 by Michelle Song Leave a Comment

Did you ever wonder where did your parameter go after you added it to a report in Workday Adaptive Planning? This post will help you find your parameters.

A parameter is helpful in matrix reports and can be used to filter your data for a specific interaction. Any report element can be added as a parameter in matrix reports. See the example below.

You can add “Timespan” as a parameter by Modified Report – Drag the Timespan element and drop it in the Parameters section. Then run the report.

After you run the report, you will not see the Timespan parameter right away. This is because a report will only show two parameters at time. You can click on the “Change Parameters” icon and it will bring you to all parameters. The orders of the parameters in the modify report mode will define the orders of the parameters displayed in the report.

Incorporating parameters into your Workday Adaptive Planning reports make them more useful and user-friendly.

Visit Revelwood’s Knowledge Center for our Workday Adaptive Planning Tips & Tricks or sign up here to get our Workday Adaptive Planning Tips & Tricks delivered directly to your inbox. Not sure where to start? Our team here at Revelwood can help! Contact us info@revelwood.com for more information.

Workday Adaptive Planning Tips & Tricks: Save Personal Views on Sheets with Dashboard

April 21, 2021 by Michelle Song Leave a Comment

If you open any sheets via the Sheet tab in Workday Adaptive Planning, you can only save one view per sheet per version per user. Prior to the 2020 R2 Release, the only workaround to save the same sheet with multiple views is using EIP, Excel Interface for Planning, and open the sheet in multiple tabs or workbooks.

With the 2020 R2 Release, you now can save multiple views for the same sheet in the same tab per version in Dashboard. This function is extremely helpful to users that manage multiple departments, or anyone who wants to view the same data with different views in one tab.

In the example below, I opened the Product Revenue sheet twice in the same dashboard. The top sheet is showing the Gross Revenue account in the Product Revenue sheet for Customer 1 by Product values. The bottom sheet is showing the same Product Revenue sheet but by Accounts.

Workday Adaptive Planning Tips & Tricks: Save Personal Views in Dashboard

Once the Display Option is applied to the sheet in Dashboard, it is automatically saved for the selected version and there is no need to click the Save icon. If the dashboard is a shared dashboard, the latest published changes will become the new view of the sheets in that dashboard.

Here is another example. The top one has a filter to show New York employees and the second one has a filter to show just the employees in Canada.

Visit Revelwood’s Knowledge Center for our Workday Adaptive Planning Tips & Tricks or sign up here to get our Workday Adaptive Planning Tips & Tricks delivered directly to your inbox. Not sure where to start? Our team here at Revelwood can help! Contact us info@revelwood.com for more information.

Workday Adaptive Planning Tips & Tricks: Excel Substitute

March 3, 2021 by Michelle Song Leave a Comment

We have something different for today’s Workday Adaptive Planning Tips & Tricks post – an Excel tip! Our Adaptive Planning users also use Excel, so we thought they’d find this helpful.

Have you ever wanted to get rid of spaces in some of your Excel cells? Maybe you do not want a cell to have separate words and instead want to use an underscore character. Or maybe you are in a situation where you want to adjust a prefix for a certain range of cells. You can use Excel’s find and replace functionality, but this approach could lead to a time consuming effort if you want to pick and choose the cells where it applies.

Excel’s SUBSTITUTE function can help you solve this problem. The SUBSTITUTE function is used to find a specific set of characters and replace it with something else while also giving you the ability to define details within cells.

The syntax of the function is:

=SUBSTITUTE (text, old_text, new_text)

text
- This is the source that will be changed; this is typically a cell reference.
old_text
- This is the subtext that will be replaced.
new_text
- This is what will replace the old subtext

If the cell in A5 consists of “Happy Birthday” then it can be updated to “Happy_Birthday” via the following:

=SUBSTITUTE (A5, “ “, “_”)

In addition, the parameter that defines the new text can also be a cell reference. This gives you the ability to quickly change the results of a large set of data by simply updating a single cell.

This approach will allow you to quickly find and replace characters within specific cells instead of having to manually go through a set of cells via Excel’s find and replace functionality.

The team at Revelwood has been recognized by Adaptive for its thought leadership in the space, commitment to its Adaptive Insights practice, and its rapid achievements of milestones. Visit Revelwood’s Knowledge Center for our Adaptive Insights Tips & Tricks or sign up here to get our Adaptive Insights Tips & Tricks delivered directly to your inbox. Not sure where to start with Adaptive Insights? Our team here at Revelwood can help! Contact us info@revelwood.com for more information.

Workday Adaptive Planning Tips & Tricks: Interactive Dashboards – Dynamic Planning with Embedded Sheets

February 24, 2021 by Dave Miersch Leave a Comment

More often than not, clients of Revelwood will make use of multiple sheets in Workday Adaptive Planning to support different aspects of their planning process(es). When using several sheets to make changes to key drivers, assumptions and scenario drivers, clients often ask the question, “How can I quickly and easily see how the changes I make on one sheet impact the rest of my budget?” One tried and true method was using multiple screens or windows with multiple sheets or reports open to see how the model changes. While this method works, it can be cumbersome and as many of us are working remotely for the foreseeable future, it likely becomes problematic if we do not have access to all those great monitors at home.

Another more dynamic solution is using the newer functionality of embedded sheets within Dashboards.

Within Dashboards, users now have the ability to add any Standard, Modeled, or Cube sheet you have created within Adaptive Planning to enter data without ever having to navigate away from our dashboard view(s). These sheets act as usable copies of your existing sheets allowing changes to be made and instantly reflected on related charts and dashboards.

In the below example, we have three Dashboards. Sales Volume & Margin by Month, budgeted Sales Revenue vs Prior Year Actuals and budgeted Sales Volume vs Prior Year Actuals.

Interactive dashboards in Workday Adaptive Planning

While in edit mode, navigate to the dashboard selector and simply drag and drop the “Sheet” option into your dashboard window. An additional drop-down menu will then be available to select all available sheet options to choose from.

Learn about interactive dashboards in Workday Adaptive Planning

Select the appropriate sheet you want to use to make changes and analyze the overall model impact and you’re all set! You now have your previously created and defined sheet embedded within your dashboard.

In my example, we have added our “Bottle Release Schedule” sheet which is essentially our sales unit planning sheet.

Understanding interactive dashboards in Workday Adaptive Planning

Making any changes and saving them within the Dashboard will automatically funnel through to dependent charts and dashboards. We are going to add 1,000 units sold in the month of April 2021 in rows 1 and 3, click save in the sheet, and then see how those changes impact our entire model in real time.

Dynamic planning with embedded sheets in Workday Adaptive Planning

We can quickly see that making those changes has added 2,000 units to our total sales volume as well as $300,000 in additional revenue.

Any sheet created within Adaptive can be pulled into dashboards for more fluent and flexible planning and reporting in one view. While some functionality is limited vs using the sheet itself, having the ability to make changes and immediately see the impact without managing multiple windows can be a valuable tool in the heart of budget season.

The team at Revelwood has been recognized by Workday Adaptive Planning for our thought leadership in the space, commitment to our Workday Adaptive Planning practice, and our rapid achievements of milestones. Visit Revelwood’s Knowledge Center for our Workday Adaptive Planning Tips & Tricks or sign up here to get our Workday Adaptive Planning Tips & Tricks delivered directly to your inbox. Not sure where to start with Workday Adaptive Planning? Our team here at Revelwood can help! Contact us info@revelwood.com for more information.

Workday Adaptive Planning Tips & Tricks: Excel Reporting Using a Report Template

February 17, 2021 by Revelwood Leave a Comment

Many users of Workday Adaptive Planning utilize OfficeConnect for their Excel reporting. But not all users will have access to OfficeConnect, depending on your organization. But there is another great option for Excel reporting called a Report Template.

What is the difference between OfficeConnect and a Report Template?

Templates are Excel files which can be added to regular reports, including repeating reports. Instead of running an HTML report, you can have a custom Excel report. Below are some nice features, as once you use it, it will become a favorite for reporting.

Report Templates allow:

Custom formatting data from Adaptive such as background color, font color, and font type.
Calculations on the data, along with including graphs and notes.
Use with repeating reports.
A saved Excel file attached to a report.
Macros can be added to templates.

Here is a quick tutorial on how to create an Excel report using a report template.

Locate the Adaptive Planning HTML report which has the data you want for your Excel report.
a. Here is an HTML Report.
b. Locate the report in the Reports screen.
Right click on the report and choose Run as Excel, and the file will download.
Open the downloaded Excel file. You will see the Excel data on the first sheet.
a. The report is the same as the report in Step 1 but in Excel.
On this sheet, you can change fonts, and add conditional formatting, calculations, etc. If you want to add other sheets for more reporting, you can use formulas to reference data on the first sheet.
a. In this example, we have changed fonts style and added conditional formatting for the % Var column.
b. In this example, we added another sheet and created a graph and ratios using formulas to reference the first sheet (report data). You can add multiple sheets to create a customized report or a report book. You can also hide sheets. For example, the Report Info tab Adaptive includes in all excel reports or the first sheet with the data.
Return to the report in Adaptive Planning.
a. Right click on the same HTML report and select Attach Template.
b. Click Choose File button and select the excel file you just created which has your updated report with formatting, formulas, graphs, etc. Click OK. Once the file is selected click Open.
c. The file icon will change colors from blue to green, so you know the report has been properly attached.
Run the report and it will automatically download as an Excel file. Your existing report has been generated and has applied all applicable prompts. For example, the month of data will change per the prompt option selected.

You now have an Excel report! And can run it anytime!

Workday Adaptive Planning Tips & Tricks: Data Integration and the Excel Spreadsheet Data Source

Read more Workday Adaptive Planning Tips & Tricks:

Workday Adaptive Planning Tips & Tricks: Loan Amortization

Read more Workday Adaptive Planning Tips & Tricks:

Workday Adaptive Planning Tips & Tricks: Choose the Correct TIME Syntax

Read more Workday Adaptive Planning Tips & Tricks:

Workday Adaptive Planning Tips & Tricks: Revenue Cohort Modeling

Cohort model vs. Regular model

Where to use cohort modeling

How to build cohort models in Workday Adaptive Planning

Read more Workday Adaptive Planning Tips & Tricks:

Workday Adaptive Planning Tips & Tricks: Where Did My Parameters Go?

Read more Workday Adaptive Planning Tips & Tricks:

Workday Adaptive Planning Tips & Tricks: Save Personal Views on Sheets with Dashboard

Read more Workday Adaptive Planning Tips & Tricks:

Workday Adaptive Planning Tips & Tricks: Excel Substitute

Read more Workday Adaptive Planning Tips & Tricks:

Workday Adaptive Planning Tips & Tricks: Interactive Dashboards – Dynamic Planning with Embedded Sheets

Read more Workday Adaptive Planning Tips & Tricks:

Workday Adaptive Planning Tips & Tricks: Excel Reporting Using a Report Template

Read more Workday Adaptive Planning Tips & Tricks:

Revelwood Overview

EXPERTISE

ABOUT

CONNECT

Workday Adaptive Planning Tips & Tricks

Read more Workday Adaptive Planning Tips & Tricks:

Read more Workday Adaptive Planning Tips & Tricks:

Read more Workday Adaptive Planning Tips & Tricks:

Cohort model vs. Regular model

Where to use cohort modeling

How to build cohort models in Workday Adaptive Planning

Read more Workday Adaptive Planning Tips & Tricks:

Read more Workday Adaptive Planning Tips & Tricks:

Read more Workday Adaptive Planning Tips & Tricks:

Read more Workday Adaptive Planning Tips & Tricks:

Read more Workday Adaptive Planning Tips & Tricks:

Read more Workday Adaptive Planning Tips & Tricks:

Footer

Revelwood Overview

EXPERTISE

ABOUT

CONNECT