CHAPTER 3 The Cash Budget

1 69 CHAPTER 3 The Cash BudgetOf 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 obligationsAfter studying this CHAPTER , you should be able the purpose of the cash Budget and how it differs from an income a firm s expected total cash collections and disbursements for a particular a firm s expected ending cash balance and short-term borrowing how Excel can be used to determine the optimal timing of majorcash the Scenario Manager to evaluate different assumptions in a the debugging tools that Excel provides to find and fix errors in 3: The Cash Budget70(which are not on the income statement) do.

2 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 ll see that a cash Budget is composed of three worksheet area, where we will do some preliminary calculations; listing of each of the cash inflows (collections) and outflows(disbursements); of the ending cash balance and short-term borrowing 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.

3 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 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 and expected sales through October are as given in Table 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 materials inventory purchases are equal to 50% of the followingmonth s sales ( , 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 are forecasted to be equal to 20% of expected on leases for equipment are $10,000 per payments of $30,000 on long-term debt are due in June $50,000 dividend will be paid to shareholders in prepayments of $25,000 will be paid in June and $200,000 is scheduled to be paid in July for a capital investment, butmanagement is flexible on the scheduling of this Barbecues must keep a minimum cash balance of $15,000 byagreement with its bank.

4 Its cash balance at the end of May was $20, 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 ( , 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 a new workbook and rename Sheet1 to Cash Budget .

5 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 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 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.

6 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, 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 3: The Cash Budget72We can also reference a cell that contains a date and extract the year, month, or day using theappropriately named functions:YEAR(SERIAL_NUMBER)MONTH(SERIA L_NUMBER)DAY(SERIAL_NUMBER)In each case, SERIAL_NUMBER represents a date serial number.

7 For example, type=Year(K4) into K5 and the result will be 2012. Similarly, =Month(K4) would return 2,and =Day(K4) would return 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 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.

8 Now apply the custom number format mmmm to the values in C4:I4 so thatonly the month names are 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 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.

9 Notethat to produce a space between the words, we had to include an empty 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)73 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 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.

10 In A5 enter the label Sales,and then copy the expected sales from the table into C5:I5 in your 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 patterns must be included in your sales forecast if your cash Budget is to 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 hasTABLE 3-1 BITHLO BARBECUES ACTUAL AND EXPECTED SALES FOR 2012** April and May sales are ,000 May365,000 June387,000 July329,000 August238,000 September145,000 October92,0001.