Adaptive Planning

Workday Adaptive Planning Tips & Tricks: Crosstabs – The Significance of and How to Build

October 12, 2022 by Alex Goldman

Crosstabs in Workday Adaptive Planning are a great way to visualize and analyze data. This type of chart is great for comparing data sets and their relationships. Data in a Crosstab is visualized in a tabular format. Crosstab allows you to compare change over time, based on one or more accounts. Crosstabs also allow you to add different dimensions to view variances throughout the company. For example, a Crosstab can display a company’s revenue and net profit from different years, as well as filter by office or location.

How to Build a Crosstab

There are a few rules to follow in order to successfully build a Crosstab. At least one Account is needed to be selected to build a Crosstab. While Time is typically used in Crosstabs, it is not required when making the visualization. The Time Dimension will allow you to compare data results from previous time periods, which can allow businesses to analyze whether they are going in the right direction. Here is a step-by-step development of a Crosstab to view Expenses for a company on a quarterly basis and by Supervisor Organization (Operations, Corporate, or Other).

The First Step is selecting the Crosstab option and dragging it onto the plain space. Then we can edit the chart name. This is done by selecting the A in the right corner of your screen and then entering the name that you would like to use. For the sake of this example, the name used is “Expense Crosstab.”

Graphical user interface, application, Word

Description automatically generated

Graphical user interface, application, box and whisker chart

Description automatically generated

Graphical user interface, text, application

Description automatically generated

The next step is adding the required accounts and dimensions. In this chart, Columns will be just time, while rows will be both Account and Supervisory Organization. To do this, click the stack of coins below the A on the right side of the screen. Under Columns, select Time, and under rows: Accounts 6100 Payroll, 6200 Taxes and Benefits, 6300 Office Expenses, 6400 Travel and Entertainment, 6500 Marketing, 6999 Other Expenses. You will also Add Supervisory Organization to rows as well.

Graphical user interface, application

Description automatically generated

This is what the account will look like:

Graphical user interface, application, Teams

Description automatically generated

You can edit the time to be monthly, quarterly, or yearly. You can do this in the bottom right corner where it says “Quarter” by clicking and changing it to month or year. As of now, we have just one time period. Let’s make the dashboard a time range with multiple data points. To do this we will click the line with two small circles.

Now that we have selected this, you can select the time range you would like your Crosstab to display. To adjust the time, click on the time period and make your selection.

For this example, the range Q1 2021 to Q1 2022 was selected. Below is the final product for the Crosstab.

As shown, the Crosstab is a simple but effective way to paint and visualize numbers that any company can use for an easy way to track and analyze finances.

Workday Adaptive Planning Tips & Tricks: Creating an Excel File Data Source

September 14, 2022 by Dave Miersch

Did you know you can create an Excel file spreadsheet data source for Workday Adaptive Planning?

Watch Dave Miersch, Revelwood’s Practice Leader for Workday Adaptive Planning, demonstrate how to create and use an Excel file as a data source for Adaptive Planning. Dave shows you how to:

Use any data source, such as from an ERP system or data warehouse
Create a new data source
Find and select the spreadsheet option
Name the data source
Import the spreadsheet

Adaptive Planning makes it very easy to import and export an Excel spreadsheet!

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:

A picture containing table

Description automatically generated

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.

Table

Description automatically generated

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.

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

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.

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

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

Revenue per product/customer by month

Modeled Sheet
- With timespan

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.

FP&A Done Right: 5 Ways Dashboards Empower The Office of Finance

April 23, 2021 by Revelwood Leave a Comment

This is a guest blog post from our partner Workday Adaptive Planning, exploring how to unlock hidden opportunities with dashboards & analytics.

Visualizing data is often the fastest way to identify trends and patterns that lead to insights and better decision making. That’s because the simple clarity of visualizing data via interactive dashboards can reveal hidden opportunities that likely would have been missed in more traditional analysis and sharing of data.

Here are five ways dashboards can help identify valuable insights that may have been overlooked in the past.

1. Dashboards encourage company-wide planning (or xP&A)

Simply making dashboards accessible to stakeholders throughout the organization represents a huge win in itself—and a significant step toward breaking down silos. Yet beyond that, increasing the number of people who have access to data presented in digestible formats exponentially increases the chances of those aha moments occurring. The production floor manager will surely have a different perspective than the CFO. When that perspective is informed with accessible data delivered via a dashboard, the stage is set for new efficiencies and improved productivity.

2. Dashboards show instead of tell

There’s a reason the phrases “go through the numbers” and “eyes glaze over” are often uttered in the same sentence. Traditionally, delivering financial information has largely been a one-way conversation with the finance team presenting mundane reports and data downloads. With the exception of the number crunchers in finance and accounting, many business partners get lost or disinterested when presented with a number or data overload. Dashboards avoid this challenge by elevating the data to the next level and using graphics and visualizations to clearly show data in formats that provide key context and clarity. When data gets presented in highly visual and familiar formats, business users can often quickly see challenges and opportunities that otherwise might have been missed.

3. Dashboards offer customized views for different thinkers

Different people consume information in a wide range of ways. Some may be more comfortable viewing data presented in standard bar, column, gauge, area, and doughnut charts. Yet others benefit from data presented in more engaging or interactive formats. Workday Adaptive Planning dashboards feature data visualization that includes funnels, dials, waterfalls, bubbles, histograms, radars, and Pareto charts. Users across locations and on any device can view data in the formats that connect with their unique way of learning and thinking.

4. Dashboards are ever-present

Even finance pros and business leaders who are adept at extracting insights from traditional reporting face the challenge of locating reports once they are filed away. And once people find the report, they have the time-consuming process of checking if the data is still accurate. Conversely, dashboards are continuously available via a wide range of devices with data updated in real time, assuring users that they are working with the latest available information. So if conditions change or a new opportunity arises, easy-to-access data visualization is there to support decision-making and reveal how an opportunity may be quickly leveraged.

5. Dashboards are inviting and simple to use

The simple power of dashboards is that they are easy to use and invite users to experiment, explore, and discover. By eliminating the complexity barrier, the odds of uncovering hidden opportunities expand dramatically. Ultimately, dashboards create the opportunity for self-service analysis for everyone. That allows any user to perform drilldown analytics, create period-to-period comparisons, and explore iterative what-if analyses that can effectively identify issues that need immediate attention while also identifying trends that could be leveraged through sales and targeted marketing efforts.

This blog post was originally published on the Workday Adaptive Planning blog.

Workday Adaptive Planning Tips & Tricks: Crosstabs – The Significance of and How to Build

How to Build a Crosstab

Read more Workday Adaptive Planning Tips & Tricks:

Workday Adaptive Planning Tips & Tricks: Creating an Excel File 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

Read more Workday Adaptive Planning Tips & Tricks:

FP&A Done Right: 5 Ways Dashboards Empower The Office of Finance

1. Dashboards encourage company-wide planning (or xP&A)

2. Dashboards show instead of tell

3. Dashboards offer customized views for different thinkers

4. Dashboards are ever-present

5. Dashboards are inviting and simple to use

Revelwood Overview

EXPERTISE

ABOUT

CONNECT

Adaptive Planning

How to Build a Crosstab

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:

1. Dashboards encourage company-wide planning (or xP&A)

2. Dashboards show instead of tell

3. Dashboards offer customized views for different thinkers

4. Dashboards are ever-present

5. Dashboards are inviting and simple to use

Footer

Revelwood Overview

EXPERTISE

ABOUT

CONNECT