This blog post provides a comprehensive guide on building a 12-month rolling cash flow forecast in Excel, covering best practices, assumptions, income statement calculations, and the creation of professional charts and graphs to enhance financial modeling skills.
Welcome to the Corporate Finance Institute's course on 12-month rolling cash flow forecasts. This course is designed to equip you with the skills necessary to build a robust cash flow forecasting model in Excel. We will cover modeling best practices, step-by-step calculations, and professional tips to enhance your financial models.
By the end of this course, you will be able to:
This course is ideal for individuals working in or aspiring to work in financial planning and analysis (FP&A), treasury management, financial reporting, and financial accounting.
The first step in building our model is to create the assumptions section, which contains all the inputs or drivers for our model. This section will help us fill in the income statement, balance sheet, and other sections of the model. Here are the key components we will include:
To set up our model, we will create formulas for each of the assumptions. For example, the number of stores will be calculated as:
Number of Stores = Last Month's Number of Stores + New Stores - Closed Stores
We will also calculate sales per square foot based on historical data and set conservative estimates for future projections.
Next, we will analyze historical data to establish realistic assumptions for receivable days, inventory days, and payable days. This analysis will help us create a solid foundation for our forecast.
Once the assumptions are in place, we can begin filling in the income statement. The revenue will be calculated using the formula:
Revenue = Sales per Square Foot * Square Feet per Store * Number of Stores / 12
We will also calculate the cost of goods sold (COGS) and gross profit, followed by SGA expenses. The income statement will flow from our assumptions, allowing us to see how changes in assumptions affect overall financial performance.
To present our findings effectively, we will create a summary or dashboard section that includes charts and graphs. This section will convey key information quickly, allowing stakeholders to grasp the financial outlook without delving into the details of the model.
In this course, we have built a comprehensive and dynamic financial model that forecasts 12 months of cash flow. We started with assumptions, constructed the income statement, and created supporting schedules, leading to a complete cash flow statement. The final step involved summarizing our findings through professional charts and graphs, which are essential for communicating our results to executive management.
Thank you for participating in this course with the Corporate Finance Institute. We hope you feel empowered to apply these skills in your career and enhance your financial modeling capabilities.
