View the step-by-step solution to:

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


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 [email protected] 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
Cash Collections from Sales Revenue
Total Cash Available
Salaries & Wages


Distribution & shipping

Payment of chip purchases

Selling Expenses

National Sales Meeting

Total Cash disbursements
Excess or (Deficit) of Cash $8,000


Total financing

Ending Cash balance

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 Jul-10 Aug-10 Sep-10 $4,000  $8,000 $922,500 Total Cash Available $926,500 Disbursements Salaries & Wages Benefits Distribution & shipping Selling Expenses National Sales Meeting $918,500  $8,000  Financing Borrowing Interest Total financing Ending Cash balance $8,000  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? Revised June  projection 3 rd  Quarter 2010 Beginning Cash  Balance "Pink Cells" enter a cell reference  formula Cash Collections from  Sales Revenue "Green Cells" enter a formula using  the cell addition function Payment of chip  purchases Total Cash  disbursements "Yellow Shaded Cells" enter a  formula using the Sum function Excess or (Deficit) of  Cash Purple Shaded cells, enter a formula  using the using the cell  addition/subtraction function "Blue Shaded Cells" enter a formula  using the cell addition/subtraction  function
Background image of page 1

Top Answer

The way to answer this question is ... View the full answer

Solution of 532292_ACC.xls

CBC Company ­ 3rd quarter 2010 Cash Budget
Revised June projection Jul­10 Aug­10 Sep­10 3rd Quarter 2010 Beginning Cash Balance $4,000 $8,000 $41,000 $98,500 $122,500 Cash Collections from...

Sign up to view the full answer

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.


Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question
Ask a homework question - tutors are online