Answered You can hire a professional tutor to get the answer.
CBC Company 3rd quarter 2010 Cash Budget Revised June projection Jul10 Beginning Cash Balance $4,000 $8,000 Cash Collections from Sales Revenue
Cash Budget Problem for Exam #3, Managerial Accounting 201-06BackgroundCBC 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 projectionBeginning Cash balance $5,000$902,500$908,500$(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:Sales revenue forecast$1,100,000August $1,300,000$1,400,000The 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$100,000 each month for salariesBenefits = 20% of salariesJuly = $500,000August = $600,000September = $700,000Monthly costs = 25% of Sales revenue projection. This includes a fixed depreciation amount of $10,000 a monthMonthly costs = 10% of Sales revenue projection. There is no fixed depreciation in Selling CostsPast 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:June collections will improve by Additional $5,000, taken into account for the Revised June Projection96.0% of sales revenueAugust: 96.5% of sales revenue97.0% of sales revenueBefore 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:June’s revised projection does not need to be changed.None of the Gray shaded cells need any input.Use all of the cost factors presented above taking into account any and all revisions noted to cost factors in the narrative.If one believes any boxes should be zero, a “0” must be entered in that box or cell.Complete the attached table.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.Your Excel file must be emailed to me using Save your file in this format, lastname-Exam 3.xlsNote: There are three questions on page 5.CBC CompanyCash Budget for 3rd Quarter 2010Worth 60 PointsSeptember 2010 3rd Quarter 2010Beginning Cash Balance$4000Cash Collections from Sales Revenue$922,500Total Cash Available$926,500DisbursementsSalaries & WagesBenefitsDistribution & shippingPayment of chip purchasesSelling ExpensesNational Sales MeetingTotal Cash disbursements$918,500$8,000FinancingBorrowingInterestTotal financingEnding Cash balance$8,000Did 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?
