CFOs are prioritizing improvements in the financial planning and analysis capabilities of their businesses in 2022. In a recent Deloitte survey, financial planning and analysis (FP&A) topped the list of the finance functions North American CFOs are focused on improving. Financial leaders also want to strengthen the data analytics and forecasting capabilities of their finance teams.
As FP&A skills become a must-have for all finance professionals, it’s worth revisiting a core analytics discipline they need to understand and can continually improve upon: financial projections and the modeling techniques used to produce them. Enhancing these skills has grown more important to remaining agile, resilient and profitable in the wake of unpredictable business, economic and supply chain events, as well as to inform strategies for future growth as the economy starts to expand.
Financial projections and the modeling techniques that shape them are in sharp focus because they help companies input assumptions, see the projected outcomes and choose the most favorable ones so they can take the best steps to move the organization forward.
Businesses of any size can take advantage of financial projections and modeling. What’s more, while much of the basic work can be done in spreadsheet applications, more sophisticated tools are now accessible for smaller organizations. This ebook will explain the value of financial projections, what information you need to get started, a few popular models and how your business can leverage these techniques to thrive and grow.
The term financial projection is often used interchangeably with forecast, but there are key differences. Financial projections and financial forecasts are alike in that they both aim to predict outcomes based on specific assumptions. But where forecasts present the assumptions that reflect what the business expects to happen, financial projections apply a financial model to play out various assumptions to see what might happen. The output, projections, is a forecast of what might happen as a result of those assumptions along a spectrum of best-case and worst-case scenarios, what ifs and more. All that information allows the business to plan and think about its actions in the event that any of these outcomes—good, bad or neutral—occur.
In the past, forecasts provided a sort of internal map to guide business decision-making, and projections were used to show outside investors how their money would be used. For instance, if a bank gives you a loan for $200,000, what will you do with it? How do you plan to pay it back?
More recently, projections have become essential business tools to recalibrate strategies and build contingency plans in light of unanticipated supply chain disruptions, changing expectations from the workforce (like a growing preference for working remotely) and more.
A financial projection predicts a likely outcome based on one or more hypothetical assumptions. Those assumptions are the inputs of the financial model, which is a statistical tool used to predict what might happen as a result of different business decisions, market scenarios and more. With that information, the model can then show the expected financial position, operational results and cash flow.
Calculating several financial projections simultaneously in order to evaluate multiple scenarios and the possible responses is a common practice. Projections help a company better plan for risks, like a natural disaster that prevents it from getting a key part from a supplier or a sudden drop in demand after a competitor launches a superior product. They also help prepare for positive outcomes, like a spike in demand for a certain product or the increased capacity from opening a third warehouse. They give the business a much better idea of how external factors will impact its financials and the adjustments and investments it needs to make to achieve its goals.
Projections cover multiple financial metrics. Revenue projections can give investors and lenders a sense of how, for example, a 20% increase in sales could impact operating expenses on the income statement. Cash flow projections show where you will get that money—from receivables, loans, a line of credit or another source. A balance sheet projection can suggest the anticipated rate of return for someone who invests in your company.
Financial projections are useful for:
Both financial forecasts and financial projections rely on current and historical transactional data, coupled with information on market conditions and industry trends.
Financial projections take the analysis a step further by applying a financial model to determine how certain variables will change the assumptions. Financial projections use information from current and past financial statements and external market information from government agencies, such as the U.S. Department of Labor, or market and industry projections from leading research and advisory firms.
Many finance leaders complete their first models in a spreadsheet program like Microsoft Excel, and the formulas allow you to model assumptions you’ve made. To build a financial model, you will ideally have at least three years of historical data, and the Journal of Accountancy provides a comprehensive list of financial data and metrics to include, such as:
Oftentimes, the hardest part for startups and younger small businesses is getting these numbers in the first place.
Startups often run into the problem of trying to figure out what data to use for the foundation of their financial models since they have little to no sales history or metrics to work from. One source is industry and market research, like Standard and Poor’s (S&P) or Dun & Bradstreet, which provide national averages for businesses in different markets.
Newer companies can then pare down that information into the numbers most relevant to their organization. These figures can include the standard costs of revenue in every industry, the percentage of revenue attributed to the direct cost of sales or what percentage of revenue goes to overhead.
Another approach is to base calculations on the market share you want to capture in a certain time frame (top-down) or based on the resources at hand and the current data (bottom- up). Bottom-up forecasts are usually best for the shorter term (one to two years) and top-down for the longer term (three to five years). Used together, these two strategies offer a healthy balance between ambition and the reality of how much market share you can capture.
Once you have these inputs, you can build and apply a financial model.
True to its namesake, it takes the three core financial statements—income statement, balance sheet and cash flow statement—and uses assumptions and formulas to create a forecast for a given time period. The forecast starts with revenue and can also calculate expenses, debtors, creditors, fixed assets and more. Once the three-statement financial model is in place, employees can apply other financial models to conduct scenario planning and sensitivity analysis.
An employee building a financial model in a spreadsheet will create tabs for the income statement (showing revenue and expenses), balance sheet (detailing assets and liabilities), cash flow statement (money in versus money out), capital expenses and depreciation costs to establish a clear picture of the current financial health of its business. A finance professional then uses the assumptions as inputs and applies formulas to drive outputs that are projections. On the income statement, for example, assumptions can include revenue, average order value, refunds as a percentage of revenue, discounts as a percentage of revenue, cost of goods sold (COGS) as a percentage of revenue and operating expenses as a percentage of revenue.
Once the three-statement financial model is in place, other financial models can be applied to further understand the impact of various assumptions. These include:
While many finance departments rely on customized spreadsheets to do financial modeling and impact analysis for these models, there are several issues with this approach. Spreadsheets are challenging to manage, lack data transparency and integrity, do not integrate with operational plans, and are generally not efficient at handling financial modeling activities.
With planning and budgeting software, models don’t have to be created from scratch every time an executive asks for a projection. Cloud-based planning solutions enable users to create and compare multiple models that incorporate real- time financial and operational data. Since there’s less legwork required, the business can scale and apply this type of analysis to more business decisions, leading to faster insights for decision-makers. Despite those clear benefits, only 11% of small businesses surveyed by Robert Half said they have automated their financial modeling practices.
Small businesses just getting started with financial planning can create a basic model in Excel by following these steps below.
1. Create a new spreadsheet and label the first row “Assumptions.” Label columns B, C, and D with three future fiscal In these columns, you’ll make predictions about your performance in each of those years for a variety of metrics.
2. Label rows with revenue, units, price, COGS and operating expenses like marketing, labor or other categories of expenses that are relevant to your business.
3. Fill in the values with your assumptions, based on financial forecasts and your current financial Use a font color that is easily distinguished from black in the corresponding cells (like blue). Remember to format any dollar figures with the accounting number format by right- clicking the cells, going to “format cells” and selecting “accounting” in the “numbers” tab.
4. This is a good time to freeze panes. To do this, highlight cell B4 or its equivalent in your worksheet, go to “View” in the task bar, and click “Freeze panes.” This keeps your labels visible as you toggle around in the If you pay attention to the row numbers as we move on, you’ll see how this improves readability.
5. Next, move down a couple of rows to start calculating projections for your income statement, beginning with net The font for these values will be black. You will run the numbers in your assumptions through a formula to calculate net revenues by multiplying units by price (=B6*B7 in the sample from Figure 1). Copy and paste the formula to the next two cells to the right. The spreadsheet should automatically apply the formula appropriately for the year you’re working in, but it’s a good idea to make sure that it did by double-clicking the cell to view the formula. Ensure that the letters match the column for that cell. Make sure your number formatting is set to “accounting.”
6. Calculate COGS by multiplying units by unit cost (=B6*B10 in our example). Just as before, copy and paste that formula in the two cells to the right, checking to make sure it was applied appropriately for each column. Check your number formatting to ensure that it’s “accounting.”
Now, the format of your worksheet should resemble Figure 2.
7. Still within the income statement area, calculate gross profit by subtracting COGS from net revenue (=B18–B20 in our example). Copy and paste the formula to the next two cells to the Check the formulas and the number formatting for the correct column and “accounting.”
8. If you would like to see margins, you can divide the gross profit by revenue (=B22/B18 in Figure 3) and change the number format to percentage so you get the gross profit percentage.
9. While still within the income statement, calculate operating For our sample, this included labor and marketing in our assumptions—you may want to include additional costs—and we will reflect those same expenses in the income statement. Remember that you don’t want to enter the same value in two cells, so fill in these cells by using a formula to pull numbers from the source in the assumptions. In this example, we would compute labor for 2020 by entering =B13 because we got that number from the assumptions. That way, if we change the assumption later, the income statement will adjust along with it. Repeat this step for the remaining five cells in the operating expenses section of the income statement. Check your number formatting.
10. Calculate the total for operating expenses by adding all of the costs in this section together. In our case, we can simply add labor and marketing, but if you have more than two items here, you can use the sum formula. As always, check the number formatting.
11. Calculate operating income by subtracting operating expenses from gross profit (=B22–B28 in Figure 5, see next page). Copy and paste the formula in the cells to the Check your formula and number formatting.
12. Determine operating income margins by dividing operating income by revenue (=B30/B18 in Figure 5, see next page). Again, copy and paste the formula for the next two years, and check your formulas and change the number format to “Percentage.”
Now you have built a dynamic model using best practices that automatically calculates gross and operating margins as you change assumptions. While this is a basic model, it’s one of many that can help you set goals and make decisions for your company. It quickly gives you an idea of what your finances would look like based on projected revenue and expenses, and you could create multiple versions based on expected, high or low sales estimates.
There are clear advantages to automating financial modeling capabilities with a planning and budgeting tool. Software can process more data and create visualizations to make projections easier to consume and glean information from.
The automation enabled by these systems means less time spent finding and entering data, and more time using the outputs of the model to inform and shape business decisions.
But any investment in automated financial modeling or manual calculations will be for naught without reliable, accurate inputs. That’s why it’s so important to start any journey to financial analysis by first automating as many accounting and finance processes as possible, since all other activities will use those numbers. As noted earlier, accurate financial statements are the baseline for solid financial analysis. In that sense, an ERP platform with robust financial planning capabilities like NetSuite often serves as the foundation for financial modeling and projections. Having a central database that updates in real time assures decision-makers that they’re using trustworthy numbers across all departments.
That single source of information helps financial planning and analysis teams overcome their chief challenge: data collection.
One survey found that the teams spend 75% of their time gathering and processing data. A system like NetSuite Planning and Budgeting eliminates that work by automatically pulling in historical data and applying it to pre-built, industry-specific statistical models that include revenue and expense modeling. That allows businesses to more accurately model what-if scenarios and predict potential results. NetSuite’s planning and budgeting application then generates a dashboard summarizing those predictions, and users can apply predicted values directly into their plan or forecast. Finally, the module eases the presentation of those results with drag-and-drop capabilities to build reports for different audiences and make it easier to share them with various stakeholders.
Financial models and projections have become increasingly crucial for finance teams. As companies become more aware of the various risks that could have a dramatic impact on their operations and revenue, they’re doing everything they can to plan for such events and avoid scrambling. Although financial modeling may sound daunting, anyone with a basic understanding of your finances should be able to perform basic projections. An ERP solution and planning and budgeting software will take your efforts to the next level and make more complicated and frequent projections feasible.
In the early stages of a company, a common pitfall is a vague or ambiguous mission. A plethora of ideas, initiatives, and projects can pull
Today’s business environment is fast paced, hypercompetitive and constantly changing. It’s no place for the rearview-mirror finance and accounting processes of old. Instead, companies are
Collaboration tools are the utensils of the workplace. SharePoint and Teams, for example, each bring unique features to the table. One provides a central location
“I don’t remember where that file is.” “They said that all of the project details are in last week’s conversation. But which conversation?!” “There are
