Budgeting

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.

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.

FP&A Done Right: The Role of KPIs in Driver-Based Budgets

February 12, 2021 by Revelwood Leave a Comment

This is a guest blog post from our partner Workday Adaptive Planning, on why finance teams should focus on KPIs and business drivers.

Gone are the days when business leaders were narrowly focused on just the net cash flow or balance sheet. Modern finance pros are asked to track not only a lengthy list of metrics, but also KPIs beyond the traditional finance arena. The old adage is, “You get what you measure.” When managers are held accountable for attaining or exceeding KPI targets defined by the executive team, their actions and decisions become aligned to the executives’ strategy.

Why the focus on KPIs and business drivers? Well, more leaders are realizing that to stay competitive and agile, their formulated strategies need to be managed and executed and that traditional budgets are no longer cutting it. Creating detailed, upfront financial projections for the next year and allocating budgets and planning inventory to cost centers no longer makes sense in today’s rapidly changing business environment. Instead, staying nimble and strategically savvy means embracing active planning and driver-based budgets. These driver-based budgets are categorized by:

Budgeting and planning in smaller batches with horizon-adjusted precision
Allocating resources in a fast and flexible manner
Tying budgets and planning inventory to outcomes rather than cost centers

Identify the KPIs that drive your financial results

Most organizations have a company-wide set of standardized and consistent metrics they track. But what are the KPIs that truly impact the company’s financial performance?

The answer will vary from industry to industry and company to company, but identifying the right drivers means considering the entire operational arc of the business. For instance, you might have drivers from:

Pipeline or funnel: prospective customer leads, first meetings, opportunities, sales generated pipeline, add-on revenue, channel sourced pipeline
Sales: total customers, ramped representatives, average revenue per deal, current quarter pipeline, new logos, future quarter pipeline
Customer success: customer satisfaction score, at-risk customer retention, cancellations, billable hours, time to value, average hold time
Finance: manufacturing costs, freight and distribution, free net cash flow, revenue per headcount, cost per headcount
Marketing: website visits, event attendees, new vs. returning leads, social followers, database size, earned media

Shift the conversation

“Did you hit your numbers?” That’s a question anyone who’s worked in a static planning environment has probably encountered. But with a driver-based budget and active planning, the psychology around targets actually shifts. Instead of thinking in operational silos and individual or department wins, the conversation becomes more integrated thinking about end-to-end business processes across the silos. Rather than hitting a static target, people are working to make sure certain drivers are meeting or exceeding expectations, to help fuel future success and growth at the organization.

Sometimes, that subtle but powerful shift can be hard for executives to wrap their heads around. But once the C-suite is sold on driver-based budgeting, the results usually speak for themselves. And many execs find that the forward-looking approach of driver-based budgets and KPIs actually aligns better with how they operate: with an eye toward the future, rather than toward the past.

Stop forecasting to the end of year

With an annual plan or budget, fiscal year end Dec. 31 is the end line. But business doesn’t actually come screeching to a halt at the end of the fiscal calendar year, and all of the effort required to put together an annual budget can be so onerous and complicated that it might take months in advance to assemble. That means some teams are racing toward an artificial deadline with little to no visibility into what the budget will bring even a few months into the future. Sounds like a nightmare, right?

We’re not arguing to do away with the annual plan. But with a driver-based approach, it’s easier to also create a rolling financial forecast—a roadmap for the next quarter or six months or 12 months, no matter what point you’re at in the fiscal calendar. Creating a rolling financial forecast isn’t nearly as time-intensive or intimidating as you might think when you have the input variables and parameter supported by an automated system. Because this forecasting happens more frequently and because it’s based on drivers and KPIs—rather than every single granular data point at the finance team’s fingertips—a rolling financial forecast can be both quick and sophisticated.

Are you ready to dramatically increase the agility, alignment, and accuracy of your company’s budget? It starts with shaking free of the status quo—static planning, traditional budgets, myriad metrics—and focusing on the business drivers and KPIs that will actually shape the future financial performance.

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

Check out more FP&A Done Right posts here:

FP&A Done Right: Predictions of “Extraordinary” Growth This Year

FP&A Done Right: Collaborate More When Planning

FP&A Done Right: Achieve More Reliable Financial Forecasting

IBM Planning Analytics Tips & Tricks: Convert Existing View Directly to Reports

November 24, 2020 by Nina Inverso Leave a Comment

Did you know that Planning Analytics for Excel (PAx) provides the ability to convert an existing view directly to a report in Excel? Without opening the view as an Exploration, you can start building your report directly from the Task Pane.

Just right-click on the view in the Task Pane to display the available options.

IBM Planning Analytics Tips & Tricks: Convert Existing View to Reports

You will see the following options by hovering over the Quick report, Dynamic report, and Custom report items:

How to convert existing views into reports in Planning Analytics

In this example, each of the items does the following:

On this sheet: This will open the existing Planning Analytics view as a Quick report on the current Excel sheet at cell $A$1.
On new sheet: This will open the view as a Quick report on a new sheet at cell $A$1.
At current location: This will open the view as a Quick report at the cell that is currently active.
At specified location: This will open a pop-up window that allows you to input the cell at which you would like the Quick report to appear. You can then enter the cell address and click OK to generate the Quick report, as shown in the following image.

Learn how to convert existing views to reports in Planning Analytics

IBM Planning Analytics, which TM1 is the engine for, is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters! You can also sign up to get our Planning Analytics Tips & Tricks sent directly to your inbox!

IBM Planning Analytics Tips & Tricks: PAx Task Pane Workbook Tab

November 17, 2020 by Nina Inverso Leave a Comment

Did you know that Planning Analytics for Excel (PAx) automatically keeps track of the Planning Analytics objects you have open in an Excel workbook?

Simply click on the Workbook tab of your Task Pane to view the list of Dynamic reports, Action buttons, Explorations, and Quick reports that are currently in use.

IBM Planning Analytics Tips & Tricks: PAx Task Pane Workbook Tab

These folders will be populated as you work with your Planning Analytics environment. Whether you manually add these items to a worksheet, or they are included in an existing view or report, they will appear here.

For example, clicking the Exploration button in the PAx ribbon will create an Exploration on a new worksheet.

The PAx task pane workbook tab in IBM Planning Analytics

This will also add an item to the Explorations folder on the Workbook tab of the Task Pane. When you right-click on the name of the new Exploration, you will see the following options:

Learn about the PAx task pane workbook tab in IBM Planning Analytics

Similar options appear for Dynamic reports, Action buttons, and Quick reports.

FP&A Done Right: Five Tips for Budgeting in the Age of COVID

November 13, 2020 by Revelwood Leave a Comment

This is a guest blog post from our partner Workday Adaptive Planning, written by Gary Cokins. Cokins explains why traditional budgeting is not a fit for the volatility, complexity and uncertain times businesses face today.

The pandemic is causing boards of directors and C-suite executives to take a new look at net cash flow. Traditional budgeting is simply too slow and too rigid to keep up with the rapidly changing business environment caused by COVID-19. There is too much volatility, complexity, and uncertainty right now.

Gone are the days when budgets could be one-and-done—tied to a fixed point in time and too inflexible to adjust to quickly changing business opportunities and challenges. In today’s world, a startup can be up and running and profitable in three months and disrupt its competitors. Consider Uber and Airbnb as examples. If your company takes nearly as long to create an annual budget, which is typically out-of-date a few months later, it will be extremely difficult to fight off the upstarts or keep up with your established competitors.

The solution? A flexible and continuous budgeting and forecasting process, often referred to as a rolling financial forecast, that helps you anticipate change and focus on outcomes rather than outputs and that is derived from the drivers to determine planned spending.

Here are five tips to modernize your budget process:

1. Just say no to one-and-done

Now more than ever, December’s fiscal year-end numbers often bear little resemblance to July’s realities—meaning budgets and forecasts must become more streamlined, accurate, and responsive. Annual budgeting won’t go away, but spending weeks and months processing data and reconciling spreadsheets that are out of date soon after the consolidated master budget is published doesn’t cut it anymore.

Modern budget solution:

Increase the frequency of budgets and forecasts to reflect shifting business conditions
Make decisions and plans based on data-backed insights rather than old and stale information
Change how resources, employees, and assets are allocated throughout the year and how the budget incorporates real-time opportunities and challenges

2. Focus on business drivers, not cost centers

Traditional budgeting focuses on allocating resources to cost centers, but business objectives (projects, products, and service lines) result from end-to-end cross-functional processes across the org chart. So if you determine the level of resources and spend based on forecast demand, then budgets and rolling forecasts can reflect performance that is company-wide rather than specific to a cost-center department.

Modern budget solution:

Enable organization-wide access to reports and data, allowing everyone to have visibility into the enterprise’s performance, including into individual departments
Review forecasts against budgets to eliminate confusion among competing departments
Provide real-time information for the needed insights to support better decision-making at all levels of the organization
Use drivers to determine the level of needed capacity (i.e., types and numbers of employees) to match your supply of capacity with demand

3. Create rolling financial forecasts

More than ever, fluctuating market conditions make accurate forecasts of future demand load (e.g., customer orders and sales) extremely challenging. Rolling financial forecasts help manage investments or financing determined by cash flow. They provide visibility into business performance using time horizons that reflect the speed of your business.

Modern budget solution:

Generate rolling financial forecasts that accommodate real-time shifts in market conditions
Enable self-service reporting so everyone in the organization can measure their performance against company-wide KPIs
Help everyone in the organization understand the downstream effects of their resource allocation decisions

4. Look forward, not back

Most budgets and forecasts are outdated before you push “publish” or soon after. And some factors are impossible to take into account (natural disasters, pandemics, broken supply chains, work stoppages). The rearview-mirror orientation of traditional budgeting (e.g., last year’s actuals create this year’s budgets) often results in increased “actuals” as managers exhibit “use-it-or-lose-it” behavior by spending needlessly to attain their prior fiscal year budget. Traditional budgets can’t keep up with the speed of modern business. One needs to look forward through the windshield.

Modern budget solution:

Respond faster to shifts in market conditions with real-time access to financials
Adjust outdated budgets and forecasts as change occurs
Move leadership discussions toward insight, planning, and action, rather than using the budget as a cost control mechanism to punish those with unfavorable cost variances

5. Use the right tools for the job

Creating a budget process that keeps up with the pace of today’s business requires a comprehensive, collaborative, and continuous planning platform—one that gives you robust, accessible reporting and modeling capabilities; dashboards with indicators and their targets that provide visibility into overall company performance; and automated tools that streamline budgeting and forecasting processes.

Modern budget solution:

Enable comprehensive planning that aligns the actions and priorities of everyone across the organization around common KPIs
Create opportunities for collaboration by giving everyone access to the data they need and deserve
Adjust and update budgets and forecasts on a continuous basis so you can navigate volatile market conditions in real time

Don’t let traditional budgeting lock you into outdated assumptions and fixed targets. Those outdated targets handcuff managers when the organization changes directions. Some managers view the fiscal year budget as a “contract” that they will not deviate from to minimize unfavorable variances from their allotted cost center budget expenses. This short-term focus jeopardizes the longer-term view. The modern FP&A professional knows the truth: Aligning budgets and rolling financial forecasts with comprehensive plans lays the groundwork for proactive rather than reactive planning—a significant strategic advantage in today’s highly competitive environment.

This blog post was originally published by Workday Adaptive Planning and appeared here.

Workday Adaptive Planning Tips & Tricks: CAPEX Planning – Your Tool for Growth

November 11, 2020 by Mary Luchs Leave a Comment

CAPEX (capital expenditures) planning is a key feature in Workday Adaptive Planning and gives the Office of Finance and senior executives greater visibility into the company’s financial risk, while also providing a tool for measuring corporate growth. Before we go into the specifics of CAPEX in Adaptive Planning, let’s take a step back and get an understanding of CAPEX.

Capital Expenditures: Overview

Capital expenditures are expenditures within a company that are not expensed on the income statement. They are considered to be investments into the company. Companies with higher capital expenditures are said to be investing more heavily into the future of their organizations.

CAPEX to Operating Cash Ratio

The CAPEX to operating cash ratio analyzes a company’s ability to acquire long term assets using cash flows. In other words, it indicates how much of a company’s cash flows is going towards capital expenditures. It is a great tool for measuring a company’s emphasis on growth. A higher CAPEX to operating cash ratio is an indicator of high growth in a company. A company whose ratio is too high could be taking on too much financial risk. While it is beneficial to invest in CAPEX, overspending in this area can compromise a company’s ability to pay off debts or cover other operating expenses. It is vital for the Office of Finance to have accurate, up to date visibility into CAPEX data in order to assess the company’s level of risk and make appropriate adjustments.

CAPEX and Depreciation

Depreciation is important to consider for asset management, especially for companies who are putting a lot of money towards their assets in the form of capital expenditures. A company must consider how their CAPEX depreciates when looking at their assets. This is a helpful tool to consider:

CAPEX > Depreciation → Growing assets

CAPEX < Depreciation → Shrinking assets

Accumulated depreciation of capital expenditures is indicated on the Balance Sheet, so it is important to consider how this impacts the net income of the company. Depreciation reduces the taxable income of a company, which is impactful especially for companies in a high growth phase who are investing heavily in capital. In addition to the CAPEX to operating cash ratio, depreciation can also be considered when analyzing a company’s growth.

Capex Planning in Workday Adaptive Planning

In addition to having a basic balance sheet and a P&L sheet, you should create a CAPEX sheet in Adaptive Planning. The CAPEX sheet offers a more specific look at capital expenditures than the balance sheet and the P&L sheet, allowing for more targeted analysis. Generating a CAPEX sheet also allows you to drill into expenses and depreciation for budgeting and forecasting purposes on a more granular level.

In conjunction with the balance sheet, the CAPEX sheet is important for budgeting cash and analyzing capital investments. In Adaptive Planning, the CAPEX model consists of calculated accounts in a modeled sheet. These calculated accounts include capital values and their coinciding depreciation accounts. Each modeled account is performing the same calculation based on the asset class selected by the user. Asset class is a text selector column based on the categories of capital expenditures that are specific to your business. Companies vary greatly in the ways that they calculate capital value and depreciation, but all businesses can benefit from CAPEX planning.

How to do CAPEX planning in Workday Adaptive Planning

Adding a CAPEX sheet to your Adaptive Planning implementation gives you a powerful tool for understanding the investments in your company. When you can analyze this data at a granular level, you can better assess if your company has too much financial risk, or if you are invested at an appropriate level.

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.

IBM Planning Analytics Tips & Tricks: Alternative Row Formats for Excel Reports

November 10, 2020 by Lee Lazarow Leave a Comment

In the early days of computing, printers often used paper that had alternating white and green areas which allowed the reader to easily follow a line of text across the page. This was known as “green bar” paper. While we no longer use this type of paper, the concept is still applicable for situations where your output contains many columns that are hard to follow.

You can create this approach by using alternate row formats within your Excel reports. This is done by combining two Excel formulas:

The ROW formula returns the row number for a cell reference. For example, ROW(K5) returns the number 5 since the cell is in the 5^th row of the spreadsheet.
The MOD formula returns the remainder of two numbers after division. For example, MOD(9,2) returns the number 1.

When combined, the following formula will result in either a 1 or a 0:

=MOD(ROW(cell),2)

From there, you can use conditional formatting to define the fill colors. If you are doing this in standard Excel, then you can copy the formula throughout the applicable area. If you are doing this within a dynamic report, then you can use it within the formatting area.

This approach allows you to make your wide reports easier to read. This also means that you can recreate the green bar format while you pull out your dot matrix printer and watch classic movies such as Wargames!

Workday Adaptive Planning Tips & Tricks: Override Formulas in Sheets

November 4, 2020 by Michelle Song Leave a Comment

If you want to write a formula in an account in Workday Adaptive Planning, but still want to give your team the ability to override the formula and enter different data, you can use Shared Formulas to achieve that.

The Shared Formulas function of Adaptive Planning allows users to write level-specific and version-specific formulas for GL and custom accounts. Below is a screenshot of the Shared Formula page in Adaptive.

Workday Adaptive Planning: Override Formula

First, you will select the version, account, and levels that you want to write the formula. Then, you will write the formula in the “Set formula” box. Before you save the formula, you can choose to reserve/remove the user edits in the account if any. If you reserve the user edits, the formula will not override cells that already have data and only perform the formula in the cells that are blank on sheets.

Workday Adaptive Planning: How to override formulas in sheets

You can use the “Import Shared Formulas” function to import/update the formulas in multiple accounts and levels at a time.

Overrding formulas in Workday Adaptive Planning

Unlike Master Formulas, shared formula values are not locked in sheets. You can override the formula with different data directly in a sheet cell. In the example below, I overridden the formula for account 1110 Petty Cash in Mar 2019 with a different number.

Workday Adaptive Planning Tips: Override Formulas in Sheets

Workday Adaptive Planning Tricks: Override formulas in sheets

IBM Planning Analytics Tips & Tricks: Show Members

October 27, 2020 by Lee Lazarow Leave a Comment

Did you know that while you are doing dimension maintenance, you can quickly view an element’s parents or children in IBM Planning Analytics Workspace (PAW) via a single click? Here’s how you can do this:

Step 1: Select the dimension to be edited. This is done by either right clicking on a dimension and selecting the option to “Edit Dimension” or by dragging the dimension onto a sheet.

Step 2: Right click on an element and select the option to “Show Member.” You will then see two sub-options: one for Parents and one for Children.

IBM Planning Analytics Tips & Tricks: Show Members

If you select the option for Parents then you will see all of the parents associated with the selected element. In this example, there is only one parent within the dimension structure.

If you select the option for Children then you will see all of the children associated with the selected element. In this example, there are three elements in the consolidation.

How to show members in IBM Planning Analytics

Once defined, you can then perform dimension maintenance to each of the elements on your screen.

This approach allows you to easily traverse up or down your dimension while minimizing the amount of extra elements on the screen.

Workday Adaptive Planning Tips & Tricks: Loan Amortization

Read more Workday Adaptive Planning Tips & Tricks:

FP&A Done Right: The Role of KPIs in Driver-Based Budgets