Cash Budget Problem for Exam #3, Managerial Accounting 201-06 Background CBC Company sells specialty computer chips to various manufacturers. The controller, Mr. Mercken, has instructed you as the senior cost accountant to prepare a detailed cash budget for the upcoming 3rd quarter of 2010 by the June 30, 2010. Mr. Mercken is concerned because his initial analysis of the projected cash flow for June 2010 indicates the following: June 2010 Cash projection Beginning Cash balance $5,000 Cash collections projection $902,500 Expense projection $908,500 May projected Ending cash balance $(1,000) Mr. Mercken stated to use this projection prepared by ?Bob from AccountInterns? and past financial history to prepare a 3rd quarter cash budget. Being the detailed and investigative senior cost accountant, you immediately question the work of the ?Bob?. The first thing you review is the expenses. You find the $908,500 of expenses includes $10,000 of depreciation expenses, a non cash expense. In addition, one invoice in accounts payable totaling $20,000 to the chip supplier is past due by 60 days and must be paid before the end of June to avoid the supplier holding back the flow of computer chips. Finally, ?Bob? deducted $15,000 of bad debt expense from the cash collections for June. You prepare a new June 2010 projection on the attached form which includes all of the above changes. Revenue and cost projections: The next task is to get a good forecast on sales revenue for the 3rd quarter. The sales director cooperates and provides an accurate forecast as follows: Month Sales revenue forecast July $1,100,000 August $1,300,000 September $1,400,000 The sales director stated selling expenses would be at 12% of the sales revenue forecast each month with zero depreciation in the 3rd quarter. He has tentatively planned to a hold a national sales meeting in the 3rd quarter and anticipated the cost to be$75,000. He requests you to see if you can fit this cost into the overall cash budget and still meet CBC cash goals. You make your rounds to the various department directors and they provide accurate cost projections for you to use and you summarize your notes as follows: Department/Area Cost Projection Administration ? $100,000 each month for salaries ? Benefits = 20% of salaries Material Purchases for computer chips with payment made in the last week of the month with no discount taken. ? July = $500,000 ? August = $600,000 ? September = $700,000 Distribution/shipping Monthly costs = 25% of Sales revenue projection. This includes a fixed depreciation amount of $10,000 a month Selling Monthly costs = 10% of Sales revenue projection. There is no fixed depreciation in Selling Costs Accounts receivable area ? Cash collections Past history has shown that 95% of sales revenue are collected in the month of the sale with the remaining 5% uncollectible. After collecting all of the 3rd quarter projections, you prepare a memo to the controller stating unless cash collections are not improved, CBC will not make its? minimum cash balance of $50,000 each month. The controller assigns you with an additional task of improving cash collections and implementing corrective actions in the accounts receivable area ASAP. You are now tasked with improving cash collections and preparing the cash budget. You schedule a mini two day continuous improvement event in accounts receivable to improve cash collections. The results of the event indicate 2 additional clerks are needed to make collections calls at a total cost of an additional $5,000 in salaries plus 20% for benefits per month which are Administration costs. You make a special note of this for your 3rd quarter projection. The revised cash collections rate will improve to: 1. June collections will improve by Additional $5,000, taken into account for the Revised June Projection 2. July: 96.0% of sales revenue 3. August: 96.5% of sales revenue 4. September: 97.0% of sales revenue Before preparing the 3rd quarter CBC Cash Budget, you meet again with Mr. Mercken and review all of the cost factors and the improved collection percentages. Mr. Mercken is pleased with your work and states CBC cannot access their credit line from ?3/5 Bank? because the FDIC is investigating their loan practices and might close them down. CBC has no place to get a loan. In closing, Mr. Mercken states CBC must end the 3rd quarter with $50,000 or more in cash. If the national sales meeting expense can fit into the budget and still make the goal of $50,000 or more in ending cash balance, he will allow you to go as an attendee. You are excited with the challenge because the sales director informed you of the meeting?s location, the Atlantis Resort in the Bahamas. The cash budget work begins: Instructions for completion: 1. June?s revised projection does not need to be changed. 2. None of the Gray shaded cells need any input. 3. Use all of the cost factors presented above taking into account any and all revisions noted to cost factors in the narrative. 4. If one believes any boxes should be zero, a ?0? must be entered in that box or cell. 5. Complete the attached table. 6. For the Excel users, there is an Excel file worksheet attachment for completion of the cash budget. An Extra 6 Bonus Points can be earned if the cash budget is completed in Excel and the correct formulas used where indicated by the various color shaded cells. One point is awarded for correct formulas in all of one color cells. That is, all the pink cells must be correct to receive one point, etc. Other formulas can be used to fill the other blank cells if one desires. In addition, other sheets can be added to supplement your work. Same instructions applied to completion of the Excel file as noted above. 7. Important: Your Excel file must be emailed to me using lpmerkel@gmail.com Save your file in this format, lastname-Exam 3.xls 8. Note: There are three questions on page 5. CBC Company Cash Budget for 3rd Quarter 2010 Worth 60 Points Revised June projection July 2010 August 2010 September 2010 3rd Quarter 2010 Beginning Cash Balance $4000 Cash Collections from Sales Revenue $922,500 Total Cash Available $926,500 Disbursements Salaries & Wages Benefits Distribution & shipping Payment of chip purchases Selling Expenses National Sales Meeting Total Cash disbursements $918,500 Excess or (Deficit) of Cash $8,000 Financing Borrowing Interest Total financing Ending Cash balance $8,000 1. Did ECL make their goal of ending the 3rd quarter with $50,000 in cash? 2. Did the sales department have their national sales meeting in the 3rd quarter? 3. Did the senior cost accountant get to attend the national sales meeting?

CBC Company 3rd quarter 2010 Cash Budget Revised June projection Jul10 Beginning Cash Balance $4,000 $8,000 Cash Collections from Sales Revenue $922,500 Total Cash Available $926,500 "Green Cells" enter a formula using the cell addition function Total Cash disbursements $918,500 "Yellow Shaded Cells" enter a formula using the Sum function Excess or (Deficit) of Cash $8,000 Purple Shaded cells, enter a formula using the using the cell addition/subtraction function $8,000 "Blue Shaded Cells" enter a formula using the cell addition/subtraction function Aug10 Sep10 3rd Quarter 2010 "Pink Cells" enter a cell reference formula Disbursements Salaries & Wages Benefits Distribution & shipping Payment of chip purchases Selling Expenses National Sales Meeting Financing Borrowing Interest Total financing Ending Cash balance Questions to answer: Did ECL make their goal of ending the 3rd quarter with $50,000 in cash? Did the sales department have their national sales meeting in the 3rd quarter? Did the senior cost accountant get to attend the national sales meeting