Walking Through the Financial Forecast
By Philip Campbell
The previous installment of this article series, which appeared in the November/December 2015 issue, detailed the step-by-step process for planning, creating, and presenting a reliable and repeatable financial forecast. To help you better understand each step and overcome potential obstacles and resistance, this article will walk you through a sample GC’s first time creating a financial forecast.
In this scenario, ABC Construction’s CEO/owner plans to recruit a number of respected and experienced entrepreneurs, executives, and savvy members of the financial community to join his Board of Directors. He wants to take his company to the next level and believes that having people who have “been there and done that” will be critical to his success in growing the company. Current annual revenues are about $25 million and he wants to double that number over the next three years.
One of the tools the CEO and Board of Directors will use to plan and monitor the pace and progress of ABC’s growth strategies is a reliable financial forecast. And, the CEO wants the forecasting process in place before recruiting board members.
In this example, ABC’s CFM and I worked through the process to plan, create, and present the company’s first financial forecast. The goal was to make the process a “top-down” rather than “bottom-up” exercise – one that required very little input or effort from others in the company.
Building the Forecast
Like a construction project, the three distinct phases of building a forecast are Plan, Create, and Present. (For more on these phases, refer to page 34 of the November/December 2015 issue.)
Since the CFM was a spreadsheet “power user,” we decided to create the financial model in Excel so that it could be updated monthly and would track the company’s existing financial statement format.
Set the Objectives
The objectives were heavily influenced by the CEO’s desire to create a tool to support a robust financial planning and monitoring process for the Board. The objectives were documented as:
1) Implement a reliable financial forecasting/projection process to provide a clear view of likely financial results in order to evaluate various growth plans and strategies.
2) Incorporate the forecast into the monthly financial reporting process for the Board.
These two objectives guided the implementation. At the end of the project, we referred back to them to ensure we accomplished the original intent of the forecast.
Decide on the Historical & Future Periods to Present
The most recent financial statements are from June 30 of the current year. Eighteen months of actual financial data and 18 months of forecast data were included in the model to provide a solid view of recent monthly financial performance and extend the forecast through the end of the next calendar year.
Exhibits 1, 2, and 3 at left show the income statement, balance sheet, and statement of cash flows for the prior year and the six months ending June 30 of the current year. Exhibit 4 on page 20 shows the WIP schedule at June 30 of the current year.
The full Excel financial model for this article example, including the article exhibits, monthly historical financial statements, and monthly forecast financial statements, are available at www.cfma.org/jf16forecast to help your company adopt a similar process.
Identify Financial & Nonfinancial Key Drivers
Exhibit 5 at right shows the format for the assumptions sheet where most of the forecast assumptions will be entered. (The actual monthly results for the first six months of the current year are shown.) The assumptions sheet brings the relevant information and metrics together in one place. It will be used to review actual results for each driver or metric and to enter assumptions for the forecast periods. The shaded rows are the primary drivers that will be used to forecast. Operating expenses are not shown here; those estimates will be entered directly into the income statement.
Gather Financial & Nonfinancial Data
ABC’s accounting system exported 18 months of income statements and balance sheets, with each month shown side by side. The Statement of Cash Flows logic was created in the financial model to generate that statement for both the historical and forecast periods.
The number of open projects per month came from ABC’s project management system and prior month WIP schedules, which were manually entered for each of the 18 months of actual data.
Discuss Where the Business Is Going
Luckily, ABC’s CFM was very involved in the business, had a good handle on the market, and knew what kinds of projects were in progress. Most of our discussions about the future were with the company’s CEO. We talked about the general trend in gross margins for new projects and his existing plans for bidding on projects outside of ABC’s specialty area (with much larger customers than with which it had previously worked).
Create the Forecast
Since this was a new process for ABC, the first few iterations of the forecast focused on the next six months. Once we were comfortable with the results and insights, we could then forecast the following year by month.
The key drivers used to create the forecast for the next six months are on page 23. (For a better understanding of each aspect, refer to page 38 in the November/December 2015 issue.) Exhibit 6 on page 24 shows the key assumptions for each of the next six months.
As you create the forecast, it’s natural to want to begin sharing it. However, try to keep it “for your eyes only” – at least to start. That means don’t sell the CEO or the leadership team on the value of having a forecast, don’t talk to them about the assumptions or specifics as you create the forecast, and don’t send the results of the forecast to them – yet.
Instead, spend a few months beta testing the forecast to experiment with and learn from the process before rolling it out to the management team. Create assumptions at the highest level possible to prove to yourself that you don’t need to forecast at the project level. Create the forecast for the next three months, then compare the actual results each month. What worked well? Are you surprised at the difference between your forecast and the actual results?
After running the forecast process for three months, you will become more confident and knowledgeable about the benefits of forecasting and how best to create and present the results. You will learn firsthand where the land mines are to avoid. And, you will develop a better sense of the kinds of monthly and strategic decisions that the forecast can help answer and influence.
Create a Two-Minute Summary
Here is the summary of the key insights and the primary drivers/assumptions in the forecast created for the CEO:
We have completed the financial forecast that covers the next 18 months of results. While profitability is expected to be generally aligned with our expectations, there are two important insights that we need to discuss. Each one is influenced by our plans to win projects with a number of new, and very large, customers over the next six months.
Here are the key insights to focus on in the forecast, which shows taxable income of $1.5 to $1.7 million in the current year and $2.2 to $2.5 million for next year:
1) We have adjusted the distributions to owners from $100,000 per month down to zero beginning in August. We restart the owner distributions in the forecast in March and assume a larger distribution in December of next year.
2) We assume that the previous plan to begin aggressively paying down debt is put on hold until July of next year. The assumption is we will pay debt down to zero by December of next year.
The negative assumptions about owner distributions and debt are driven by the temporary cash shortfall created by the new customers. The new customers are large and we have not done business with them in the past. From our experience, large customers typically take longer to pay than our traditional customers. And the problem is usually more pronounced in the early months of a project. As a result, we have increased the DSO assumption and assumed that we will be paid slower than normal between October and April.
To prepare for follow-up questions from the CEO, a summary of cash flow was created to highlight the monthly impact on cash. Exhibit 7 on page 25 is the Summary Cash Flow for each of the next six months. It shows that the primary driver of negative net cash flow is the increase in A/R associated with the assumption that DSO will go up near the end of the year as ABC brings on new, larger customers. This temporary slowdown in cash created the need to reduce the usual level of owner distributions and only pay the normal debt service requirements.
Exhibit 8 on page 26 shows a graph of Days Sales Outstanding (DSO) for the next six months of the current year. The move up to, and above, the 50-day level in the fourth quarter is the key driver of the negative cash flow in the forecast (and the corresponding weaker cash balances).
Once the CEO was informed on what was likely to happen after contracting with the new (and much larger) customers, the discussion shifted to ways to mitigate the potential negative impact on cash and handle a cash shortage if the actuals came in worse than the forecast.
That discussion focused on three steps:
1) Do everything possible to influence the speed of payment so invoices are paid in accordance with the contract terms. The primary insight here was that the company would focus on expediting rather than collecting A/R with the new customers. Management and accounting would meet with new customers long before the first invoice was issued in order to learn exactly how the customer’s approval and payment process worked. The goal was to adhere to the new, larger customers’ established A/P process and provide everything they needed and in the format required; thus, ABC could help invoices move through the A/P process without delay.
2) Meet with the bank in the coming weeks to walk the lenders through the forecast and make them aware that ABC may need to draw on the bank line between November and February. If ABC did draw on the line, it expected to pay the loan back in full by April.
3) Closely monitor financial results (especially A/R and cash) in the coming months and meet regularly to review the steps being taken to mitigate the negative impact of a higher DSO.
Show Historical & Forecast Results Side by Side
The financial model was set up to easily present the monthly forecast next to the monthly actual results. It could be shown on a calendar year basis or a trailing (or forward-looking) 12-month basis. This is an impactful way to make trends and direction obvious to the reader. One of the added benefits of this approach is that management becomes more knowledgeable about the financial statements and therefore more likely to pay closer attention to the numbers and trends each month.
Make the Forecast Part of Your Monthly Financial Rhythm
This final step in the process is what creates lasting value in the company. An updated forecast was included in the monthly reporting package every month going forward. The process was created so the last actual month was loaded and the forecast months updated with new information.
The cover memo in the monthly reporting package included a paragraph on key changes in the forecast. And, specific comments were included each month about how the company was doing on the two key focus areas with respect to cash: the impact of a higher DSO on owner distributions and ABC’s ability to pay down debt faster. Shortly after receiving the monthly reporting package, the CEO and the Board could quickly see what mattered most.
Overcoming Obstacles & Resistance
It is smart to prepare for potential obstacles – especially if you are implementing the process for the first time. Common challenges and questions include:
• I have a hard time getting timely and reliable information from PMs and management that I need to create and update the forecast each month.
• What happens to my credibility if my forecast is wrong?
• My CEO doesn’t see the value in having a forecast.
Here are some tips to combat such resistance:
1) The secret to a successful start is to begin the forecasting process “for your eyes only” for the first three months. You will be surprised how this one step will knock down almost every obstacle you might encounter.
2) Avoid the tendency to gather assumptions at the lowest level and roll them up. That way you won’t need to ask PMs to provide specific data. Create your own assumptions at the highest possible level based on your bigger picture discussions with management, your intuition, and your knowledge of the business.
3) Summarize the results of your forecast at a high level – and use a range to guide the forecast user away from a single number. For example, “I expect pre-tax income for the year to come in between $1.7 and $2.0 million.” Consistently speak in terms of the strategic impact of forecast results and where the company is headed financially.
4) If your CEO is leery about the value of forecasting, gradually begin talking about the financial future of the business with him or her. Ask how he or she defines financial success for the company and for its owners. Engage his or her curiosity (and knowledge) about what is about to happen financially.
Playing a More Strategic Role as CFM
A CFM has an opportunity to play a more strategic role in creating a bigger and brighter financial future for the company. Building a reliable and repeatable forecast will help you and your leadership team:
• Define where your company is going financially (and where the CEO wants it to go);
• Expose the dangers and opportunities that lie ahead;
• Create a road map to get the company there successfully and on time; and
• Monitor the financial pace and progress on the journey to success.
Providing a clear view of the business is an important step in adding value as a CFM. And it all starts with a reliable financial forecast.
Note: The full Excel financial model for this article example, including the article exhibits, monthly historical financial statements, and monthly forecast financial statements, are available at www.cfma.org/jf16forecast to help your company adopt a similar process.
PHILIP CAMPBELL is a CPA and financial consultant based in Austin, TX. He has been working closely with CEOs and owners for more than 30 years.
Philip has served as a financial officer in a number of growing companies with revenues ranging from $10 million to more than $1 billion. He has been involved in the acquisition or sale of 33 companies and an IPO on the New York Stock Exchange.
He is the author of the book Never Run Out of Cash and the free report A 3-Part Plan to Breathe Financial Life Back into Your Business.
Copyright © 2016 by the Construction Financial Management Association (CFMA). All rights reserved. This article first appeared in CFMA Building Profits and is reprinted with permission. CFMA Building Profits is a member-only benefit; join CFMA to receive the magazine.
Contact email@example.com for reprinting information.
Click here to view a PDF of this article.