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

19.6 Using Excel to Create the Short-Term Plan

Principles of Finance19.6 Using Excel to Create the Short-Term Plan

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

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

  • Create a one-year budget.
  • Create a cash budget.

A cash budget is a tool of cash management and therefore assists financial managers in the planning and control of a critical asset. The cash budget, like any other budget, looks to the future. It projects the cash flows into and out of the company. The budgeting process of a company is a really an integrated process—it links a series of budgets together so that company objectives can be achieved. For example, in a manufacturing company, a series of budgets such as those for sales, production, purchases, materials, overhead, selling and administrative costs, and planned capital expenditures would need to be prepared before cash needs (cash budget) can be predicted.

Just as you might budget your earnings (salary, business income, investment income, etc.) to see if you will be able to cover your expected living expenses and planned savings amounts, to be successful and to increase the odds that sufficient cash will be available in the months ahead, financial managers prepare cash budgets to

  • meet payrolls;
  • allocate dollars for contingencies and emergencies;
  • analyze if planned collections and disbursements policies and procedures result in adequate cash balances; and
  • plan for borrowings on lines of credit and short-term loans that might be needed to balance the cash budget.

A cash budget is a model that often goes through several iterations before managers can approve it as the plan going forward. Changes in any of the “upstream” budgets—budgets that are prepared before the cash budget, such as the sales, purchases, and production budgets—may need to be revised because of changing assumptions. New economic forecasts and even cost-cutting measures will require a revision of the cash budget.

Although a budget might be prepared for each month of a future 12-month period, such as the upcoming fiscal year, a rolling budget is often used. A rolling budget changes often as the planning period (e.g., a fiscal year) plays out. When one month ends, another month is added to the end (the next column) of the budget. For example, if in your budget January is the first month of the planning period, once January is over, next January’s cash budget column would be added—right after December’s column (at the far right of the budget).

Sample One-Year (Annual) Operating Budget

Preparing an annual operating budget can be a complex task. In essence, a company budget is a series of budgets, many of which are interrelated.

The sales budget is prepared first and has an impact on many other budgets. Take the example of a production budget of a manufacturer. The sales budget impacts what needs to be produced (production budget), and the production budget influences planned purchases of material (purchases budget), overhead resources (overhead budget), and the amount of labor costs for the year ahead (direct labor budget.)

For a merchant (such as a wholesaler or retailer), the annual budget would be less complex than that of a manufacturing firm but would still require an inventory purchases budget and an operating expense budget (such as selling and administrative expenses). For a service firm, a purchase budget for inventory would not be necessary, but an operating budget would be. All businesses need a cash budget, which is the topic of the next section of this chapter.

The example operating budget presented here is of a merchandising company. Budgets are prepared following a process that begins with a sales (or revenue) forecast. The sales forecast is normally based on information obtained from both internal and external sources and predicts the amount of units to be sold in the planning period—usually one year into the future.

A company’s management, in consultation with its marketing and sales executives, would prepare a sales budget by making assumptions about the number of units that are expected to be sold and the prices that will be charged. From the sales budget, projections are made as to cash receipts each month, and therefore assumptions have to be made as to how much of each month’s sales will be cash sales and how much cash will flow into the company from the collection credit sales (including cash flow in from the prior month’s sales). Figure 19.7 provides an example of a sales budget and projected accounts receivable collections and cash sales for the months of January through December. Keep in mind that projected monthly sales amounts are not equal to cash collected from sales. Because of sales on credit, some cash from sales lags credit sales as collections can extend beyond the month of sale. Credit terms such terms such as net 30 (net amount owed to be paid in 30 days) have to be considered when developing a forecasted cash collection pattern.

A screenshot of excel sheet shows the total sales and projected accounts receivable collections and cash sales for January through December.
Figure 19.7 Example of a Sales and Collections Budget

Download the spreadsheet file containing key Chapter 19 Excel exhibits.

Sales budgets “drive” the preparation of other budgets. If sales are expected to increase, purchases of inventory and some operating expenses would also increase. To meet the demand for goods and services (as defined in the sales budget), a purchases (inventory) budget would be prepared. In this example (Figure 19.8), the purchases budget shows projected purchases of inventory (merchandise) and the projected payments (also called disbursements) for each month.

Cash outflows as a result of purchases often do not equal the projected purchase amount. That is because payments for purchases are usually on credit (accounts payable), and so purchases for one month typically get spread out over a period of time that encompasses the current month and the month (or months) thereafter. To keep this example simple, the assumption is that the purchases are paid for in the following month (an average days payable outstanding of 30 days). However, in other cases, payment patterns may be based on other payment periods such as 45, 60, or even 90 days, depending on the trade credit terms.

A screenshot of an Excel sheet shows the purchases budget for January through December. In this budget, the payments are equal to the prior months purchases.
Figure 19.8 Purchases Budget

An operating expense budget is prepared next and is basically a prediction of the selling and administrative expenditures of the company. Notice in Figure 19.9 that in the operating expense budget, cost of goods sold (an expense) is not included, nor are noncash expenses such as depreciation. The cash outlays related to goods sold, at least in a merchandising operation, are accounted for in the purchases budget (payments for purchases of inventory.)

With the sales, purchases, and operating expense budgets prepared, the cash budget can be prepared. Some of the “inputs” to the cash budget are from the sales (collections of cash), purchases (payments), and the operating expense budget (cash expenditures for selling and administrative expenses). A sample cash budget and a discussion of its preparation follows in the next section of this chapter.

Sample Cash Budget

A cash budget is the last budget to be prepared and is often part of the financial budget (cash budget, budgeted income statement, and budgeted balance sheet). The purpose of the cash budget is to estimate cash flows, to help ensure sufficient cash balances are maintained during the planning period, and to plan for external financing during periods of cash deficits.

When a budget is prepared in Excel, cash budget analysts can play “what if” with different scenarios to see when cash surpluses and deficits are expected. Cash surpluses means that funds can be invested in marketable securities to earn a rate of return, while cash deficits mean that financing, such as a line of credit, will be necessary (assuming forecasts are accurate).

Although the example shown in Figure 19.10 is a monthly cash budget, a cash budget could be prepared using any useful time elements: weekly, monthly, or quarterly.

One common practice is to use a rolling cash budget. A rolling cash budget is continually updated to add a new budget period, such as a month’s amount of cash flow activity, as the most recent budgeted month expires. For example, assume that a 12-month cash budget is prepared for a period covering January 20X1 to December 20X1. Once the month of January 20X1 has concluded, a 12-month planning period continues by add January 20X2 to the last column of the budget. The rolling cash monthly budget is an extension of the initial cash budget model, adding one month and thereby always extending cash flow projections one year into the future.

A screenshot of an Excel sheet shows an operating expense budget for the 12 months of the year. It consists of wages and salaries, payroll taxes, advertising and marketing, repairs and maintenance, rent, shipping, utilities, insurance, supplies, and taxes for each month. All of the expenses are added together to provide the total operating expenses for the month.
Figure 19.9 Operating Expenses Budget
A screenshot of an excel sheet shows a sample cash budget for 12 months of the year. It consists of beginning cash balance, cash collections, cash disbursements, net cash flow, and preliminary ending cash balance. The minimum cash balance is subtracted from the total of those line items. Any cash surplus or deficiency is added or subtracted, to arrive at the ending cash balance.
Figure 19.10 Sample Cash Budget

Using Figure 19.9 as an example, Table 19.5 shows the formulas that form the skeleton of a monthly cash budget.

Beginning Cash BalanceThis is the amount of cash the company expects to have on the first day of the month. For example, in Figure 19.10, cell B2 is the amount of cash on Jan. 1 to start the year (the planning period). The remaining beginning cash balances for the months February through December are the ending cash balances of the previous month. For example, February’s beginning cash balance (C2) is referenced from cell B9 (ending cash balance for January).
Cash CollectionsThese are the projected cash inflows from collections from customers (accounts receivable), cash sales, and any other significant cash inflows, such as dividends and interest on investments or sale of fixed assets. For example, the Cash Collections shown in the Sample Cash Budget (Figure 19.10) are referenced from the Sales and Collections Budget (Figure 19.7). January’s Cash Collections (cell B3) in the Sample Cash Budget are from cell B12 of the Sales and Collection Budget.
Cash DisbursementsCash disbursements are the projected cash outflows, such as those for operating expenses and payment of payables. For example, Cash Disbursements in the Sample Cash Budget for January (Figure 19.10) are the sum of January’s payments for purchases in the Purchases Budget (Figure 19.8, cell B3) and the January operating expenses (Operating Expenses Budget, Figure 19.9, cell B12).
Net Cash FlowThe formula for net cash flow is For example, in Figure 19.10, the January Net Cash Flow is calculated in cell B5.
Preliminary Ending Cash BalanceBeginning Cash Balance + or - Net Cash Flow. This is the projected cash balance before taking into account the target cash balance to be maintained (minimum cash balance). In the Sample Cash Budget (Figure 19.10), the preliminary ending cash balance formula for January is =B2+B5 (B2 is the Beginning Cash Balance and B5 is the Net Cash Flow for the month).
Less: Minimum Cash BalanceThis is a target cash balance that management sets; it is the minimum amount of cash that should be maintained by the company (in Figure 19.10, cells B2:G7 and B17:G17).
Cash Surplus (Deficiency)A cash surplus means that cash can be invested in marketable securities. A cash deficiency means that some type of financing, such as a line of credit or bank loan, will be needed to provide enough cash for operations and to maintain a minimum cash balance. This number is found by subtracting the minimum cash balance from the preliminary ending cash balance. For example, the cash surplus for January in Figure 19.10 is calculated with this formula: =B6-B7. Notice that all months in the Sample Cash Budget show a surplus except for August’s forecast of a deficit, which may require drawing on a line of credit to provide enough cash to meet obligations in August.
Table 19.5 Excel Formulas for Monthly Cash Budget
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

© Jun 8, 2023 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.