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

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
Citation/Attribution

This book may not be used in the training of large language models or otherwise be ingested into large language models or generative AI offerings without OpenStax's permission.

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

© Jan 8, 2024 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.