Example: Create a Cash Forecast with Worksheets

Administrator Guide

This example illustrates how to create a cash forecast using Workday operational data, custom reports, and spreadsheet formulas to model the projections in Worksheets.

As the cash manager at Global Modern Services, Inc., you want to evaluate the forecasted cash position to ensure funding is available for supplier payments due in the next 5 days. You create a cash forecast and include all revenue, expenses, and opening bank account balances for this period. To populate the cash forecast data, you:

Create a data source set to automatically accumulate operational data.

Extract operational data from custom reports and apply mathematical formulas to create projections.

Manually enter forecasted values in the cash forecast entry area.

Security: These domains in the Cash Management functional area:

Process: Cash Forecast Data Automation

Process: Cash Forecast Reporting

Reports: Cash Forecast Reporting

Set Up: Cash Forecasting

Security: These domains in the System functional area:

Aliases

Drive

Worksheets

Access the

Create Cash Forecast Outline 2.0

task.

Create a cash forecast outline structure named Daily Cash Forecasting
with these cash inflow, outflow, and balance outline rows:

Outline Row	Outline Row Type
1. Opening Cash Balance	Accumulation
1.1 Bank Statement Balance	Balance
1.2 Carry Forward Balance	Balance
1.3 Manual Adjustments	Amount
2. Cash Inflows	Accumulation
2.1 Customer Payments	Amount
2.2 External Cash Activities	Amount
2.3 Incoming Bank Account Transfers	Amount
2.4 Incoming Intercompany Payments	Amount
3. Cash Outflows	Accumulation
3.1 Supplier Payments	Amount
3.2 Expense Reports	Amount
3.3 Outgoing Bank Account Transfers	Amount

Click

Create Data Source Set

Create a data source set named 5 Day Daily
with these values to populate cash forecast outline rows:

Outline Row	Data Source	Source Date	Source Date Modifier	Source Amount
1.1 Bank Statement Balance	Prior Day Bank Account Balances for Cash Forecasting	Bank Statement Date for Cash Forecasting	1	Balance Amount for Cash Forecasting
2.1 Customer Payments	Customer Invoices for Cash Forecasting	Invoice Due Date For Cash Forecasting	0	Invoice Net Amount Due for Cash Forecasting
3.1 Supplier Payments	Supplier Invoices for Cash Forecasting	Invoice Due Date For Cash Forecasting	0	Invoice Net Amount Due for Cash Forecasting
3.2 Expense Reports	Expense Reports for Cash Forecasting	Expense Report Accounting Date for Cash Forecasting	10	Expense Report Total Amount for Cash Forecasting

In Drive, create a model workbook and add these worksheets to link your custom reports:

Worksheet Name	Description
External Cash Activities	Provides incoming and outgoing cash activities.
Budget Lines	Provides budgeted cash activities in the current period.
Intercompany Settlements	Provides open intercompany settlements in the next 5 days.
Incoming Bank Account Transfers	Provides incoming bank account transfers in the next 5 days.
Outgoing Bank Account Transfers	Provides outgoing bank account transfers in the next 5 days.

Create these custom reports using report data sources to link to each of your worksheets in the workbook:

You can select the report data source filters that are applicable to your use case.

Report Name	Report Fields	Report Data Source
External Cash Activities	Company Pay Group Cash Activity Date Cash Activity Amount Currency	External Cash Activity
Budget Lines	Company Period Ledger Account Budget Balance Amount	Plan Lines for Financial Reporting
Intercompany Settlements	Company Open Item Transaction Date Open Item Currency Open Item Amount in Preferred Currency	Open Items For Settlement
Incoming Bank Account Transfers	Company Transaction Date Currency Transaction Amount	All Bank Account Transfers
Outgoing Bank Account Transfers	Company Transaction Date Currency Transaction Amount	All Bank Account Transfers

Enable the custom reports for:

Web services

Worksheets

Add live data from the custom reports to the corresponding worksheets.

Worksheet Name	Report Name
External Cash Activities	External Cash Activities
Budget Lines	Budget Lines
Intercompany Settlements	Intercompany Settlements
Incoming Bank Account Transfers	Incoming Bank Account Transfers
Outgoing Bank Account Transfers	Outgoing Bank Account Transfers

Access the

Create Time Span Profile

task.

Create a time span profile with these values:

Name	Time Type	Processing Duration	Quantity
5 Day Daily	Day	Specific Quantity	5

Access the

Generate Cash Forecast 2.0

task.

Generate the cash forecast using these values:

Field	Value	Description
Name	5 Day Daily Cash Forecast	You can enter a name with a maximum of 31 characters.
Description	5 Day Daily Cash Forecast
Cash Forecast Type	Version 1	Any values to describe your cash forecast. Example: Version number , Passive , or Aggressive .
Cash Forecast Outline	Daily Cash Forecasting
Cash Forecast Outline Data Source Set	5 Day Daily
Time Span Profile	5 Day Daily
Start Date	Today's date	Select the first day of the cash forecast.
Companies	Your company name	Select 1 or more companies for your cash forecast.
Currency	Use Company Currency	Select the company currency or cash forecast currency.
Currency Rate Type	Current	Select the rate type for translating foreign currency into company currency.

Workday populates the cash forecast entry area with data generated from the data sources for each day.

Merge your model workbook with your cash forecast.

Manually add live data from your model workbook to your cash forecast workbook.

Extract operational data from the cells of your model workbook to link to these cash activities:

Outline Row	Cell Formula	Description
2.2 External Cash Activities	='External Cash Activities'!E2	Link each amount field from the External Cash Activities worksheet to the entry area. The formula will contain your specific sheet name and source cell.
2.3 Incoming Bank Account Transfers	='Incoming Bank Account Transfers'!D9	Link each amount field from the Incoming Bank Account Transfers worksheet to the entry area. The formula will contain your specific sheet name and source cell.
2.4 Incoming Intercompany Payments	='Intercompany Settlements'!F15	Link each amount field from the Intercompany Settlements worksheet to the entry area. The formula will contain your specific sheet name and source cell.
3.3 Outgoing Bank Account Transfers	='Outgoing Bank Account Transfers'!D5	Link each amount field from the Outgoing Bank Account Transfers worksheet to the entry area. The formula will contain your specific sheet name and source cell.

To calculate forecasted amounts, apply spreadsheet formulas to these cash activity values:

Cash Activity Value	Formula	Description
Calculated Closing Balance	ROUND(SUM(F2:F9)-SUM(F10:F13),2)	You can use this formula to calculate the closing balance on Day 1.
Carry Forward Balance	Calculated closing balance for the previous day	Copy the closing balance from Day 1 to Day 2, and then replicate the formula for the remaining days.

You can explore other spreadsheet formulas to create projected amounts for specific cash activities.

In the cash forecast entry area, enter any manual adjustments directly on the 1.3 Manual Adjustments
outline row.

Access the

Map Standard Aliases

task.

From the

Business Object

prompt, select Cash Forecast Outline Row
.

Map these standard aliases to the associated rows in your outline structure:

Standard Alias	Cash Forecast Outline Row
_FC_Outline_Top_Level	Daily Cash Forecasting
_FC_Opening Balance	1. Opening Cash Balance
_FC_Inflows	2. Cash Inflows
_FC_Outflows	3. Cash Outflows

This enables you to view the generated cash forecast data in cash forecast standard reports.

Run the

Cash Forecast by Transaction Currency 2.0

report using these values to view and assess the results:

Field	Value
View By	Timespan
Start Date	Today's date
Cash Forecast Types	Version 1
Cash Forecast Status	Draft
Cash Forecast	5 Day Daily Cash Forecast

You view the cash forecast report results and determine that:

The ending balance on Day 3 is out of balance.

To fund the account for the supplier payment, you can initiate a bank account transfer payment for settlement equivalent to the out-of-balance amount.

You have sufficient funds for making the other supplier payments.

Concept: Managing Workbooks

Concept: Editing Workbooks

Setup Considerations: Cash Forecasting with Worksheets

Steps: Set Up Cash Forecasting with Worksheets

Steps: Use Cash Forecasting with Worksheets

Steps: Perform Bank Account Transfers for Settlement

The Next Level: Cash Forecasting with Worksheets

The Next Level: Cash Management — Dashboard, Reporting, and Analytics

The Next Level: Cash Forecasting with Worksheets — Model Templates and Custom Report Definitions