Skip to ContentGo to accessibility pageKeyboard shortcuts menu
OpenStax Logo
Principles of Finance

18.6 Using Excel to Create the Long-Term Forecast

Principles of Finance18.6 Using Excel to Create the Long-Term Forecast

Menu
Table of contents
  1. Preface
  2. 1 Introduction to Finance
    1. Why It Matters
    2. 1.1 What Is Finance?
    3. 1.2 The Role of Finance in an Organization
    4. 1.3 Importance of Data and Technology
    5. 1.4 Careers in Finance
    6. 1.5 Markets and Participants
    7. 1.6 Microeconomic and Macroeconomic Matters
    8. 1.7 Financial Instruments
    9. 1.8 Concepts of Time and Value
    10. Summary
    11. Key Terms
    12. Multiple Choice
    13. Review Questions
    14. Video Activity
  3. 2 Corporate Structure and Governance
    1. Why It Matters
    2. 2.1 Business Structures
    3. 2.2 Relationship between Shareholders and Company Management
    4. 2.3 Role of the Board of Directors
    5. 2.4 Agency Issues: Shareholders and Corporate Boards
    6. 2.5 Interacting with Investors, Intermediaries, and Other Market Participants
    7. 2.6 Companies in Domestic and Global Markets
    8. Summary
    9. Key Terms
    10. CFA Institute
    11. Multiple Choice
    12. Review Questions
    13. Video Activity
  4. 3 Economic Foundations: Money and Rates
    1. Why It Matters
    2. 3.1 Microeconomics
    3. 3.2 Macroeconomics
    4. 3.3 Business Cycles and Economic Activity
    5. 3.4 Interest Rates
    6. 3.5 Foreign Exchange Rates
    7. 3.6 Sources and Characteristics of Economic Data
    8. Summary
    9. Key Terms
    10. CFA Institute
    11. Multiple Choice
    12. Review Questions
    13. Problems
    14. Video Activity
  5. 4 Accrual Accounting Process
    1. Why It Matters
    2. 4.1 Cash versus Accrual Accounting
    3. 4.2 Economic Basis for Accrual Accounting
    4. 4.3 How Does a Company Recognize a Sale and an Expense?
    5. 4.4 When Should a Company Capitalize or Expense an Item?
    6. 4.5 What Is “Profit” versus “Loss” for the Company?
    7. Summary
    8. Key Terms
    9. Multiple Choice
    10. Review Questions
    11. Problems
    12. Video Activity
  6. 5 Financial Statements
    1. Why It Matters
    2. 5.1 The Income Statement
    3. 5.2 The Balance Sheet
    4. 5.3 The Relationship between the Balance Sheet and the Income Statement
    5. 5.4 The Statement of Owner’s Equity
    6. 5.5 The Statement of Cash Flows
    7. 5.6 Operating Cash Flow and Free Cash Flow to the Firm (FCFF)
    8. 5.7 Common-Size Statements
    9. 5.8 Reporting Financial Activity
    10. Summary
    11. Key Terms
    12. CFA Institute
    13. Multiple Choice
    14. Review Questions
    15. Problems
    16. Video Activity
  7. 6 Measures of Financial Health
    1. Why It Matters
    2. 6.1 Ratios: Condensing Information into Smaller Pieces
    3. 6.2 Operating Efficiency Ratios
    4. 6.3 Liquidity Ratios
    5. 6.4 Solvency Ratios
    6. 6.5 Market Value Ratios
    7. 6.6 Profitability Ratios and the DuPont Method
    8. Summary
    9. Key Terms
    10. CFA Institute
    11. Multiple Choice
    12. Review Questions
    13. Problems
    14. Video Activity
  8. 7 Time Value of Money I: Single Payment Value
    1. Why It Matters
    2. 7.1 Now versus Later Concepts
    3. 7.2 Time Value of Money (TVM) Basics
    4. 7.3 Methods for Solving Time Value of Money Problems
    5. 7.4 Applications of TVM in Finance
    6. Summary
    7. Key Terms
    8. CFA Institute
    9. Multiple Choice
    10. Review Questions
    11. Problems
    12. Video Activity
  9. 8 Time Value of Money II: Equal Multiple Payments
    1. Why It Matters
    2. 8.1 Perpetuities
    3. 8.2 Annuities
    4. 8.3 Loan Amortization
    5. 8.4 Stated versus Effective Rates
    6. 8.5 Equal Payments with a Financial Calculator and Excel
    7. Summary
    8. Key Terms
    9. CFA Institute
    10. Multiple Choice
    11. Problems
    12. Video Activity
  10. 9 Time Value of Money III: Unequal Multiple Payment Values
    1. Why It Matters
    2. 9.1 Timing of Cash Flows
    3. 9.2 Unequal Payments Using a Financial Calculator or Microsoft Excel
    4. Summary
    5. Key Terms
    6. CFA Institute
    7. Multiple Choice
    8. Review Questions
    9. Problems
    10. Video Activity
  11. 10 Bonds and Bond Valuation
    1. Why It Matters
    2. 10.1 Characteristics of Bonds
    3. 10.2 Bond Valuation
    4. 10.3 Using the Yield Curve
    5. 10.4 Risks of Interest Rates and Default
    6. 10.5 Using Spreadsheets to Solve Bond Problems
    7. Summary
    8. Key Terms
    9. CFA Institute
    10. Multiple Choice
    11. Review Questions
    12. Problems
    13. Video Activity
  12. 11 Stocks and Stock Valuation
    1. Why It Matters
    2. 11.1 Multiple Approaches to Stock Valuation
    3. 11.2 Dividend Discount Models (DDMs)
    4. 11.3 Discounted Cash Flow (DCF) Model
    5. 11.4 Preferred Stock
    6. 11.5 Efficient Markets
    7. Summary
    8. Key Terms
    9. CFA Institute
    10. Multiple Choice
    11. Review Questions
    12. Problems
    13. Video Activity
  13. 12 Historical Performance of US Markets
    1. Why It Matters
    2. 12.1 Overview of US Financial Markets
    3. 12.2 Historical Picture of Inflation
    4. 12.3 Historical Picture of Returns to Bonds
    5. 12.4 Historical Picture of Returns to Stocks
    6. Summary
    7. Key Terms
    8. Multiple Choice
    9. Review Questions
    10. Video Activity
  14. 13 Statistical Analysis in Finance
    1. Why It Matters
    2. 13.1 Measures of Center
    3. 13.2 Measures of Spread
    4. 13.3 Measures of Position
    5. 13.4 Statistical Distributions
    6. 13.5 Probability Distributions
    7. 13.6 Data Visualization and Graphical Displays
    8. 13.7 The R Statistical Analysis Tool
    9. Summary
    10. Key Terms
    11. CFA Institute
    12. Multiple Choice
    13. Review Questions
    14. Problems
    15. Video Activity
  15. 14 Regression Analysis in Finance
    1. Why It Matters
    2. 14.1 Correlation Analysis
    3. 14.2 Linear Regression Analysis
    4. 14.3 Best-Fit Linear Model
    5. 14.4 Regression Applications in Finance
    6. 14.5 Predictions and Prediction Intervals
    7. 14.6 Use of R Statistical Analysis Tool for Regression Analysis
    8. Summary
    9. Key Terms
    10. Multiple Choice
    11. Review Questions
    12. Problems
    13. Video Activity
  16. 15 How to Think about Investing
    1. Why It Matters
    2. 15.1 Risk and Return to an Individual Asset
    3. 15.2 Risk and Return to Multiple Assets
    4. 15.3 The Capital Asset Pricing Model (CAPM)
    5. 15.4 Applications in Performance Measurement
    6. 15.5 Using Excel to Make Investment Decisions
    7. Summary
    8. Key Terms
    9. CFA Institute
    10. Multiple Choice
    11. Review Questions
    12. Problems
    13. Video Activity
  17. 16 How Companies Think about Investing
    1. Why It Matters
    2. 16.1 Payback Period Method
    3. 16.2 Net Present Value (NPV) Method
    4. 16.3 Internal Rate of Return (IRR) Method
    5. 16.4 Alternative Methods
    6. 16.5 Choosing between Projects
    7. 16.6 Using Excel to Make Company Investment Decisions
    8. Summary
    9. Key Terms
    10. CFA Institute
    11. Multiple Choice
    12. Review Questions
    13. Problems
    14. Video Activity
  18. 17 How Firms Raise Capital
    1. Why It Matters
    2. 17.1 The Concept of Capital Structure
    3. 17.2 The Costs of Debt and Equity Capital
    4. 17.3 Calculating the Weighted Average Cost of Capital
    5. 17.4 Capital Structure Choices
    6. 17.5 Optimal Capital Structure
    7. 17.6 Alternative Sources of Funds
    8. Summary
    9. Key Terms
    10. CFA Institute
    11. Multiple Choice
    12. Review Questions
    13. Problems
    14. Video Activity
  19. 18 Financial Forecasting
    1. Why It Matters
    2. 18.1 The Importance of Forecasting
    3. 18.2 Forecasting Sales
    4. 18.3 Pro Forma Financials
    5. 18.4 Generating the Complete Forecast
    6. 18.5 Forecasting Cash Flow and Assessing the Value of Growth
    7. 18.6 Using Excel to Create the Long-Term Forecast
    8. Summary
    9. Key Terms
    10. Multiple Choice
    11. Review Questions
    12. Problems
    13. Video Activity
  20. 19 The Importance of Trade Credit and Working Capital in Planning
    1. Why It Matters
    2. 19.1 What Is Working Capital?
    3. 19.2 What Is Trade Credit?
    4. 19.3 Cash Management
    5. 19.4 Receivables Management
    6. 19.5 Inventory Management
    7. 19.6 Using Excel to Create the Short-Term Plan
    8. Summary
    9. Key Terms
    10. Multiple Choice
    11. Review Questions
    12. Video Activity
  21. 20 Risk Management and the Financial Manager
    1. Why It Matters
    2. 20.1 The Importance of Risk Management
    3. 20.2 Commodity Price Risk
    4. 20.3 Exchange Rates and Risk
    5. 20.4 Interest Rate Risk
    6. Summary
    7. Key Terms
    8. CFA Institute
    9. Multiple Choice
    10. Review Questions
    11. Problems
    12. Video Activity
  22. Index

Learning Outcomes

By the end of this section, you will be able to:

  • Generate a financial statement forecast using spreadsheet tools.
  • Connect the balance sheet and income statement using appropriate formula referencing.
  • Use spreadsheet functions to generate appropriate iterations that balance financial forecasts.

Throughout this chapter, we have seen forecasted financial statements for Clear Lake Sporting Goods along with its forecasted cash flow. These statements could all have been generated by hand, of course, but that wouldn’t be an effective use of time. As mentioned in prior sections, several different types of software can be quite effective in making the forecasting process faster and more flexible. In this section, we will review just one common option, Microsoft Excel.

Download the spreadsheet file containing key Chapter 18 Excel exhibits.

Using the “Sheet”

Creating a budget in Excel can be very simple or extremely complex, depending on the size and complexity of the business and the number of formulas and dependencies that are written into the Excel workbook.

Creating the forecast in Excel follows the same steps and flow we just explored in this chapter but with the power of a software program to do the math for you. We begin with the sales forecast, which uses several key formulas in Excel.

  1. First, sales are projected to be 18% higher than the prior year. Thus, a total projection for the year is calculated using a simple link and multiplication function tied to last year’s total sales. In Figure 18.19, you can see the formula in cell O4 is “='Figure 18.12'!N4*1.18”. This formula simply does the math to increase the prior year’s sales by 18%.
  2. Next, the sales are distributed by month. In Figure 18.19, we see in cell B5 that the forecasted income statement sheet is linked to the percent of annual sales from the Prior Year Income Statement (Figure 18.12) sheet. Then, in cell B4, January sales are estimated with a formula that multiplies the total forecasted sales in O4 by the percent of annual sales for January of the prior year. Notice that the formula then multiplies that product by 0.98. This is because Clear Lake discontinued a product line in the last quarter of the prior year, and management feels that this will reduce sales in the first quarter of the new year by roughly 2%.
    A screenshot of an excel sheet calculates the Forecasted monthly income statements for Clear Lake Sporting Goods. The formula to determine the percent of annual sales from the prior year is equals sign single quotation Figure 18.12 single quotation exclamation mark B5. The formula to determine the gross sales for January is equals sign open parenthesis dollar sign O4 times B5 closed parenthesis times 0.98.
    Figure 18.19 Forecasted Sales Formulas in Excel
  3. As Clear Lake continues to fill out its forecasted income statement, the next formula we see is a simple sum formula to calculate net sales in B8 (see Figure 18.20). It’s a simple formula that subtracts sales returns and allowances in B7 from gross sales in B4. Similar formulas are also found in B10 for gross margin and B18 for net income.
  4. In cell B9, we see a multiplication formula that multiplies sales from B4 by 0.5, or 50%. This is because management feels that cost of goods sold will remain the same as last year, in most quarters at least, and last year’s percentage was 50%.
  5. Rent, depreciation, and salaries are all simply typed in, as they are fixed expenses that remain the same as last year.
  6. The utilities calculation, found in cell B14, is somewhat similar to the sales calculation. The total utilities expense from O14 is multiplied by the current month’s sales in B4 divided by the total annual sales in O4. This spreads out the utility cost by month based on the percentage of annual sales.
A screenshot of an Excel sheet shows the formulas used for a Forecasted Income Statement. It shows the formulae for calculating various figures such as gross sales, percentage change from January baseline, net sales, cost of goods sold, gross margins, utility expense, operating income, income tax expense, and net income.
Figure 18.20 Forecasted Income Statement Formulas

Clear Lake’s forecasted balance sheet ties very closely to both the forecasted income statement and the prior year’s income statement. In Figure 18.21, we see in C7 an addition formula using the sum of the current month and three months of prior sales as an estimate of the ending accounts receivable balance. The formula for inventory is similar but forward looking. In C8, inventory is estimated by adding the cost of goods sold for the current month and next three months from the forecasted income statement.

Total current assets in C10 is calculated with a SUM formula that adds together the values in all the selected cells. Amounts such as short-term investments and common stock that are not anticipated to change are simply typed as a number in the cell. Much like in the income statement, subtotals are found in C13 for total assets, C17 for current liabilities, C24 for total equity, and C25 for total liabilities and equity. Retained earnings in C23 pulls the ending retained earnings balance from the end of last year (hidden in column B) and adds the net income for January in the forecasted income statement to get the current month’s ending balance.

A screenshot of an Excel sheet shows the Forecasted Balance Sheet Formulas. It shows formulae for calculating accounts receivables, inventory, accounts payables, unearned revenue, and ending retained earnings.
Figure 18.21 Forecasted Balance Sheet Formulas

Much like the balance sheet, the cash forecast also relies heavily on data from the forecasted income statement as well as the forecasted balance sheet. To begin the year, in Figure 18.22, we see that the formula in B4 pulls the cash balance from the forecasted balance sheet. In B6, the formula pulls the sales for the three months prior from the previous year’s income statement. This is because it’s assumed that cash is collected from customers 90 days after the sale. The same approach is used for accounts payable, rent, salaries, and utilities. The formulas pull the expenses from a prior month depending on the assumed timing for payment. Utilities, for example, are assumed to be paid within 30 days, so the cash outflow in February is assumed to be the utilities expense for January from the forecasted income statement. Note that interest payments are assumed to be zero in January and February, but in March, the formula in D14 sums the interest expenses on the forecasted income statement for January, February, and March. This is because interest is paid quarterly.

Finally, note the formula in C4. The beginning cash balance for a given month is the same as the ending cash balance from the prior month; thus, the figure in B18 is linked to C4 to start the new month.

A screenshot of an excel sheet shows formulas used to forecast cash. Formulas for accounts receivables collected, accounts payable paid, salaries, rent, utilities, dividends, and interest payments reference Figures 18.12 and 18.13.
Figure 18.22 Cash Forecast Formulas

Using Excel Functions to Balance

Once we get a draft of the forecasts outlined, then the tinkering starts. Additional information can be used to adjust the formulas, as we saw with the 2% reduction in January sales for the forecasted income statement. Because we have linked most (though not all) of our expenses, subtotals, and statements together using formulas, management can also use the forecast workbook to perform scenario and sensitivity analyses, essentially asking “what if?” and looking at the results. When completed, however, before finalizing the forecast, it’s important that the financial statements are in balance (particularly the balance sheet, just as the name implies).

Notice that throughout, we used formulas to calculate subtotals to ensure they are correct and change as needed. We also linked figures, such as the ending and beginning cash balances, to ensure they are in balance. Perhaps the easiest but most important thing to do is to ensure that the balance sheet balances. We can do this with a simple formula that compares total assets to total liabilities and equity. We can see in Figure 18.23 that subtracting one from the other in cell C27 should result in $0. If there is a difference, the formula will highlight it, forcing us to investigate and correct the sheet so that it balances.

A screenshot of an excel sheet shows the Forecasted Balancing Formula. The final formula in this spreadsheet subtracts the total liabilities and stockholder equity from the total assets
Figure 18.23 Forecasted Balancing Formula
Do you know how you learn best?
Kinetic by OpenStax offers access to innovative study tools designed to help you maximize your learning potential.
Order a print copy

As an Amazon Associate we earn from qualifying purchases.

Citation/Attribution

Want to cite, share, or modify this book? This book uses the Creative Commons Attribution License and you must attribute OpenStax.

Attribution information
  • If you are redistributing all or part of this book in a print format, then you must include on every physical page the following attribution:
    Access for free at https://openstax.org/books/principles-finance/pages/1-why-it-matters
  • If you are redistributing all or part of this book in a digital format, then you must include on every digital page view the following attribution:
    Access for free at https://openstax.org/books/principles-finance/pages/1-why-it-matters
Citation information

© May 20, 2022 OpenStax. Textbook content produced by OpenStax is licensed under a Creative Commons Attribution License . The OpenStax name, OpenStax logo, OpenStax book covers, OpenStax CNX name, and OpenStax CNX logo are not subject to the Creative Commons license and may not be reproduced without the prior and express written consent of Rice University.