Excel: Learning Cash Flow Forecasting

Course details

Explore the intricate world of cash flow forecasting with Microsoft Excel in this comprehensive course. Carl Seidman—a FP&A advisor, fractional CFO, adjunct professor, and Microsoft MVP in Excel—shows you how to translate numbers into actionable strategies as you identify potential cash flow crises and master the art of building accurate cash flow forecasts. Discover dynamic Excel functions such as SUMIFS and XLOOKUP to create robust financial models. Examine the impacts of various business scenarios and perform detailed sensitivity analyses to understand potential financial outcomes. Level up your understanding of business interactions and how they affect liquidity, so you can navigate your company's growth and sustainability confidently. Whether you’re dealing with investor relations, financial health analysis, or optimizing resource allocations, this course can help you ensure sound financial decisions and operational excellence.

Learning objectives
Explain the difference between direct and indirect cash flow methods, including their respective benefits and applications in financial forecasting.
Identify key drivers influencing cash inflows and outflows, and translate these into actionable operating initiatives for influencing cash flow forecasts.
Apply Excel formulas, functions, and dynamic ranges to construct efficient and scalable monthly cash flow forecasting models.
Conduct scenario analysis by toggling between various business cases to evaluate potential impacts on cash flow.
Evaluate liquidity risks based on forecasted cash flow trends and develop operational strategies to mitigate these risks and build forecasting confidence.

Concepts

0. Introduction

01 - Excel-based cash flows for financial planning and analysis

1. Introduction to Cash Flow Forecasting and Modeling

02 - Importance of Excel-based cash flow forecasting in financial planning and analysis
03 - Direct, indirect, weekly, and monthly cash flows
04 - Overview of an Excel-based cash flow forecasting process

2. Gathering Data and Preparing Assumptions

05 - Identifying sources of historical financial data
06 - Building assumptions about future performance
07 - What happens when you lack good financial information
08 - Building an information request and status log in Excel

3. The Monthly Profit and Loss Forecast

09 - Building a sales forecast in Excel
10 - Building and forecasting a payroll expense schedule
11 - Forecasting operating and non-operating expenses
12 - Challenge - Forecasting the profit and loss and scenarios in Excel
13 - Solution - Forecasting the profit and loss and scenarios in Excel

4. The Working Capital Forecast

14 - Forecasting accounts receivable collections with SUMIFS
15 - Forecasting payroll payments with XLOOKUP
16 - Forecasting accounts payable payments with SUMIFS
17 - Monitoring working capital changes with Excel's watch window

5. Long-Term Assets and Liabilities Forecast

18 - Forecasting capital expenditures (CapEx) with on off Excel toggles
19 - Forecasting debt service automatically with Excel lookups
20 - Challenge - Forecasting debt service with INDEX XMATCH
21 - Solution - Forecasting debt service with INDEX XMATCH

6. Scenario Analysis and Sensitivity Testing

22 - Developing business scenarios with Excel's form controls
23 - Conducting sensitivity analysis of key variables in Excel
24 - Challenge - Building a dynamic scenario manager tool in Excel
25 - Solution - Building a dynamic scenario manager tool in Excel

7. Advanced Considerations in Cash Flow Forecasting and Modeling

26 - Using data mapping to update actuals in the financial model
27 - Dynamic analysis of variances in the cash flow model
28 - Challenge - Rolling forward and Excel-based cash flow model
29 - Solution - Rolling forward and Excel-based cash flow model

8. Finalizing, Communicating, and Iterating the Forecast

30 - Designing the cash flow forecasting process in Excel
31 - Business partners and the cash flow forecasting process
32 - Implications of cash flow forecasts on the business

Conclusion

33 - Elevating your cash flow forecasting and modeling in Excel