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.

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

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

4 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 the CHAPTER , we will create a complete cash Budget for June to September 2012for Bithlo Barbecues, a small manufacturer of barbecue grills.

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

6 Butmanagement is flexible on the scheduling of this Barbecues must keep a minimum cash balance of $15,000 byagreement with its bank. 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.

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

8 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?

9 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. 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).

10 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. For example, type=Year(K4) into K5 and the result will be 2012.