Transcription

The Cash BudgetCHAPTER 3After studying this chapter, you should be able to:1.2.3.4.Explain the purpose of the cash budget and how it differs from an income statement.Calculate a firm’s expected total cash collections and disbursements for aparticular month.Calculate a firm’s expected ending cash balance and short-term borrowing needs.Demonstrate how Excel can be used to determine the optimal timing of majorcash expenditures.5.Use the Scenario Manager to evaluate different assumptions in a model.6.Use the debugging tools that Excel provides to find and fix errors in formulas.Of all the topics covered in this book, perhaps no other task benefits so much from the use ofspreadsheets as the cash budget. As we’ll see, the cash budget can be a complex documentwith many interrelated entries. Manually updating a cash budget, especially for a large firm,is not a chore for which one volunteers. However, once the initial cash budget is set up in aspreadsheet, updating and playing “what if” becomes very easy.A cash budget is simply a listing of the firm’s anticipated cash inflows and outflows over aspecified period. Unlike a pro forma income statement (discussed in Chapter 5), the cashbudget includes only actual cash flows. For example, depreciation expense (a noncashexpense) does not appear on the cash budget, but principal payments on debt obligations69

CHAPTER 3: The Cash Budget(which are not on the income statement) do. Because of its emphasis on cash income andexpenditures, the cash budget is particularly useful for planning short-term borrowing andthe timing of expenditures. As with all budgets, another important benefit of the cash budgetcomes from reconciling actual after-the-fact cash flows with those from the forecast.We’ll see that a cash budget is composed of three parts:1.The worksheet area, where we will do some preliminary calculations;2.A listing of each of the cash inflows (collections) and outflows(disbursements); and3.Calculation of the ending cash balance and short-term borrowing needs.We are simplifying things somewhat. In reality, many of the given variables in this chapterwould come from other budgets. For example, a firm would usually have at least a salesbudget from which the sales forecasts are taken, a salary budget, a capital expenditurebudget, and so on. All of these different budgets would be created before the final cashbudget and require a great deal of thought and research. The cash budget worksheet wouldthen pull values from those other budgeting worksheets.Throughout the chapter, we will create a complete cash budget for June to September 2012for Bithlo Barbecues, a small manufacturer of barbecue grills. The financial staff of the firmhas compiled the following set of assumptions and forecasts to be used in the cash budgetingprocess:701.Actual and expected sales through October are as given in Table 3-1.2.40% of sales are for cash. Of the remaining 60% of sales, 75% is collectedin the following month and 25% is collected two months after the sale.3.Raw materials inventory purchases are equal to 50% of the followingmonth’s sales (e.g., June purchases are 50% of expected July sales). 60%of purchases are paid for in the month following the purchase, and theremainder are paid in the following month.4.Wages are forecasted to be equal to 20% of expected sales.5.Payments on leases for equipment are 10,000 per month.6.Interest payments of 30,000 on long-term debt are due in June andSeptember.7.A 50,000 dividend will be paid to shareholders in June.8.Tax prepayments of 25,000 will be paid in June and September.9. 200,000 is scheduled to be paid in July for a capital investment, butmanagement is flexible on the scheduling of this outlay.10.Bithlo Barbecues must keep a minimum cash balance of 15,000 byagreement with its bank. Its cash balance at the end of May was 20,000.

The Worksheet AreaThe Worksheet AreaThe worksheet area is not necessarily a part of the cash budget. However, it is useful becauseit summarizes some of the most important calculations in the budget. This section includes abreakdown of expected sales, collections on accounts receivable, and payments for materials(inventory) purchases. This section could, perhaps should, be included on a separateworksheet along with all of the assumptions. Alternatively, the values could be drawn fromseparate budget worksheets (e.g., the expected sales figure could be linked to the salesbudget worksheet, which would include a sales forecast for each product line). It might alsoinclude some other preliminary calculations. Because our model is small, we will keep all ofthe assumptions and preliminary calculations on one worksheet.Open a new workbook and rename Sheet1 to Cash Budget. Like any other financialstatement, we begin the cash budget with the titles. In A1 enter: Bithlo Barbecues; inA2 type: Cash Budget; and in A3 enter: For the Period June to September2012. Center these titles across columns A to I. Next, enter the names of the months fromTable 3-1 in C4:I4 using the AutoFill feature (see page 11).Using Date FunctionsAs we will see, a cash budget spreadsheet is ideally suited for reuse in future budget cycles.After all, why should you recreate the entire worksheet just because the dates and numberswill be different in the future? With a little bit of planning, we can set up the worksheet tomake it easy to use for future budgets.Let’s start by reconsidering how we enter the dates into row 4. Instead of typing the names ofthe months, we can mostly automate them with formulas. In particular, we would like to beable to change the date in C4 and have the other dates automatically update. To do so, wewill need to use the DATE function in combination with the YEAR, MONTH, and DAYfunctions.Recall that Excel treats dates as the number of days that have elapsed since January 1, 1900.The DATE function calculates the serial number for any date and is defined as:DATE(YEAR, MONTH, DAY)For example, enter the formula Date(2012,2,4) into a blank cell (say, K4). This willreturn 40,943, which is the serial number for February 4, 2012. This number can beformatted using any built-in or custom date format to be displayed as a date instead of aninteger.71

CHAPTER 3: The Cash BudgetWe can also reference a cell that contains a date and extract the year, month, or day using theappropriately named functions:YEAR(SERIAL NUMBER)MONTH(SERIAL NUMBER)DAY(SERIAL NUMBER)In each case, SERIAL NUMBER represents a date serial number. For example, type Year(K4) into K5 and the result will be 2012. Similarly, Month(K4) would return 2,and Day(K4) would return 4.With that as background, enter 4/1/2012 into C4. This is the date that will control theothers. In D4 enter the formula: DATE(YEAR(C4),MONTH(C4) 1,DAY(C4)). Thatformula looks at the date in the cell C4 and returns a date that is exactly one month later.Now copy the formula from D4 to E4:I4.If you now change the date in C4, the others will update automatically. Note also that,because we are using dates instead of text, we can use these cells as the basis forcalculations. For example, an entry in the budget might vary depending on the month of theyear. We can now calculate that automatically so that it is always correct, no matter how thedates change. Now apply the custom number format “mmmm” to the values in C4:I4 so thatonly the month names are displayed.Calculating Text StringsIt is often useful to calculate text results, just as we calculate numeric results. For example, itwould be helpful if the heading in A3, which shows the relevant period for the cash budget,was updated when the date in C4 is changed. We can accomplish this by using stringconcatenation and the TEXT function.Concatenation is the process of joining two or more text strings into one. Excel has a built-infunction to perform this task:CONCATENATE(TEXT1, TEXT2, )but it is rarely used. Instead, the & operator is used because it performs the same task and ismuch more economical to type. For example, type Hello into K8 and World into K9. InK10 enter the formula: K8&" "&K9 and the result will be the string Hello World. Notethat to produce a space between the words, we had to include an empty string.The TEXT function takes a number (or the result of a formula) as an argument and converts itto text with a particular number format. It is defined as:TEXT(VALUE, FORMAT TEXT)72

The Worksheet Areawhere VALUE is the number and FORMAT TEXT is a custom number format mask (seepage 51).Finally, enter the formula: "For the Period "&TEXT(E4,"mmmm")&" to"&TEXT(H4,"mmmm")&" "&TEXT(M1,"#") into A4. Now change the date in C4 afew times to understand how it works.Sales and CollectionsThe starting point for a cash budget is the sales forecast. Many of the other forecasts in thecash budget are driven (at least indirectly) by this forecast. The sales forecast has beenprovided for us by Bithlo’s marketing department in Table 3-1. In A5 enter the label Sales,and then copy the expected sales from the table into C5:I5 in your worksheet.TABLE 3-1BITHLO BARBECUES ACTUAL AND EXPECTED SALES FOR 0* April and May sales are actual.Note that sales have a strong seasonal component. In this case, barbecuing is mostly asummer phenomenon, and we expect that sales will peak in June before falling dramaticallyin the fall and winter months. Such seasonality is important in many types of business: forexample, sales in the fourth quarter may be 30% or more of annual sales for many retailers.1Seasonal patterns must be included in your sales forecast if your cash budget is to beaccurate.For most firms, at least a portion of sales are made on credit. It is therefore important toknow how quickly the sales can be collected. In the case of Bithlo Barbecues, experience has1. As an example, at Target Corp. fourth-quarter revenues averaged about 31% of full year sales infiscal years 2007 to 2011. The comparable first quarter average was only about 22%.73

CHAPTER 3: The Cash Budgetshown that in the past about 40% of its sales are cash and 60% are on credit. Of the 60%of sales made on credit, about 75% will be collected during the month following the saleand the remaining 25% will be collected two months after the sale. In other words, 45%( 0.60 0.75) of total sales in any month will be collected during the following month, and15% ( 0.60 0.25) will be collected within two months.2Our goal is to determine the total collections in each month. In A6 type: Collections:,and then in A7 enter the label: Cash. This will indicate the cash sales for the month. In A8enter: First Month to indicate collections from the sales made in the previous month. InA9 enter: Second Month to indicate collections on sales made two months earlier.Because our estimates of the collection percentages may change, it is important that they notbe entered directly into formulas. Instead, enter these percentages in B7:B9.Because the budget is for June to September, we will begin our estimates of collections inE7. (April and May sales are included here only because we need to reference sales from thetwo previous months to determine the collections from credit sales.) To calculate the cashcollections for June we multiply the expected June sales by the percentage of cash sales, soenter: E5* B7 into E7. To calculate collections from cash sales for the other months,simply copy this formula to F7:H7.Collections on credit sales can be calculated similarly. In E8, we will calculate Junecollections from May sales with the formula: D5* B8. Copy this formula to F8:H8.Finally, collections from sales two months ago, in E9, can be calculated with the formula: C5* B9. After copying this formula to F9:H9, calculate the total collections in row 10 foreach month by using the SUM function. Check your numbers against those in Exhibit 3-1 andformat your worksheet to match. This is a good time to save your workbook.Purchases and PaymentsIn this section of the worksheet area, we calculate the payments made for inventorypurchases. Bithlo Barbecues purchases inventory (equal to 50% of sales) the month beforethe sale is made. For example, June inventory purchases will be 50% of expected July sales.However, it does not pay for the inventory immediately. Instead, 60% of the purchase priceis paid in the following month, and the other 40% is paid two months after the purchase.We first need to calculate the amount of inventory purchased in each month. As noted, this is50% of the following month’s sales. So in A11 type: Purchases and in B11 enter: 50%.We will calculate April purchases in C11 with the formula: B11*D5. Copying thisformula to D11:H11 completes the calculation of purchases.2. For simplicity, we assume that 100% of sales will be collected. Most firms would include anallowance for “bad debts” or returns based on historical patterns.74

The Worksheet AreaEXHIBIT 3-1CALCULATING COLLECTIONS AND PAYMENTS IN THE WORKSHEET AREACredit purchases are not cash outflows, so we need to calculate the actual cash payments forinventory in each month. This is very similar to the way we calculated total cash collections.First, enter labels. In A12 type: Payments:. In A13 and A14 enter: First Month andSecond Month, respectively, and enter: Total Payments in A15. Now enter 60% inB13 and 40% in B14. In June, Bithlo Barbecues will pay for 60% of purchases made in May.So the formula in E13 is: B13*D11. Copy this to F13:H13 to complete the first month’spayments. To calculate the June payment for April purchases in E14, use the formula: B14*C11. Copy this to F14:H14 and then calculate the total payments for each month inrow 15.At this point, your worksheet should look like the one in Exhibit 3-1. Check your numberscarefully to make sure that they agree with those in the exhibit. To clarify the logic of theseformulas, examine Exhibit 3-2 which is the same as Exhibit 3-1, except it has arrows drawnin to show the references for June.Because this portion of the cash budget contains only preliminary calculations, it isn’tnecessary that it be visible at all times. Therefore, we can hide it using Excel’s group andoutline feature as discussed on page 62. Select rows 5:16 and then go to the Data tab. In theOutline group, click the upper portion of the Group button and then collapse the outline.When it is necessary to view this area we can simply expand the outline.75

CHAPTER 3: The Cash BudgetEXHIBIT 3-2THE WORKSHEET AREA OF A CASH BUDGETCollections and DisbursementsThis section of the cash budget is the easiest to set up in a spreadsheet because there are nocomplex relationships between the cells as there are in the worksheet area. The collectionsand disbursements area is very much like a cash-based income statement. However, note thatthere are no noncash expenses listed, and certain items (e.g., principal payments) that are noton the income statement will be on the cash budget. We need to list all of the actual cashflows that are expected for each month, whether they are on the income statement or not.We will begin by summarizing the cash collections for each month. Enter the label:Collections in A17. In E17:H17 the formulas simply reference the total collections thatwere calculated in E10:H10. So, for example, the formula in E17 is: E10. Copy thisformula to F17:H17. Had there been other cash inflows expected, for example proceeds froma loan, then they would also be listed in this section.In A18, enter the label: Less Disbursements:. The first cash outflow that we will enteris the inventory payment, which was calculated in the worksheet area. Enter InventoryPayments as the label in A19 and the formula in E19 is: E15. Wages are assumed to beequal to 20% of sales. In A20 add the label: Wages and in B20 type: 20%, which will beused to calculate the expected monthly wage expense. The formula to calculate wages in76

Collections and DisbursementsE20 is: B20*E5. Now copy these formulas to F20:H20. By now, you should be able tofinish this section by entering the remaining labels and numbers as pictured in Exhibit 3-3.EXHIBIT 3-3COLLECTIONS AND DISBURSEMENTSThere are a couple of points to note about this portion of the cash budget. First, we haveassumed that the only cash inflows are from selling the firm’s products. In other cases,however, it is possible that the firm might plan to sell some assets or bonds or stock. Any ofthese actions would bring cash into the firm and should be included under collections.Second, we have included dividend payments, which do not appear on the income statement,on row 23. The reason that they are on the cash budget is that dividends represent a very realcash expenditure for the firm. They don’t appear on the income statement because dividendsare paid from after-tax dollars. In other problems, there may be other similar outlays, such asa principal payment to be made on a loan. Remember, the cash budget is not an incomestatement. For the cash budget, we do not use accrual accounting; we include all cashinflows and outflows when they are expected to occur, whether they will be on the incomestatement or not.Finally, Bithlo Barbecues has scheduled capital outlays of 200,000 in July. Even thoughthey are paying the full cost in July, it is unlikely that they would be allowed to expense thisentire amount during 2012. Instead, the income statement would reflect the depreciation ofthese assets over a longer period of time. Regardless of tax laws or accounting conventions,it is important to include all expected cash inflows and outflows on the cash budget whenthey are scheduled to occur.77

CHAPTER 3: The Cash BudgetCalculating the Ending Cash BalanceThis last section of the cash budget calculates the expected ending cash balance at the end ofeach month. This is an important part of the cash budget because it helps the managerunderstand how the firm’s cash balance will fluctuate and thus its short-term borrowingneeds. Knowing the borrowing requirements in advance allows managers to arrange forfinancing before they need it and provides the time necessary to evaluate possiblealternatives. Managers can also use this information to determine the best timing for majorexpenditures.TABLE 3-2CALCULATING THE ENDING CASH BALANCEBeginning Cash Balance Total Collections–Total Disbursements Unadjusted Cash Balance Current Borrowing Ending Cash BalanceTable 3-2 shows the series of calculations necessary to determine the firm’s ending cashbalance. Essentially, this is the same procedure we saw in Table 2-2 on page 58 with theaddition of short-term borrowing. In the next section we will add a few steps to thiscalculation, but the basic procedure is always as outlined in Table 3-2.We have already made most of the calculations necessary to complete the cash budget.Before we finish this last section, however, we need to add another detail. The managementof Bithlo Barbecues has decided that they would like to keep a minimum cash balance of 15,000 to meet any unexpected expenses. If the projected cash balance falls below thisamount, they will need to borrow to bring the balance back to this minimum. In A32 enterthe label: Notes:. We will use cells below A32 to list important assumptions about ourcash budget. The first of these is the minimum cash balance requirement. In A33 enter thelabel: Minimum Acceptable Cash and in B33 enter: 15,000.In cells A27:A31 enter the labels as shown in Exhibit 3-4. (Notice that this is exactly thesame as was outlined in Table 3-2.) We start with the ending cash balance in May. Enter:20,000 into D31. The ending cash balance for the month is simply the unadjusted cashbalance plus current borrowing, so the formula in E31 is: sum(E29:E30). This formulawill be the same for each month, so copy it across to F31:H31.78

Calculating the Ending Cash BalanceEXHIBIT 3-4ENDING CASH BALANCE CALCULATIONThe beginning cash