This is about cash budget
Please help me answer the cash budget case:
I have my answer but I really don't know if I got it right:
Here is the problem:
CASE:
Alpha Tech, a rapidly growing distributor of electronic components, is formulating its plans for 2013. Carol, the firms marketing director, has completed the following sales forecast:
Alpha Tech
Forecasted Sales
(in thousands)
Month Sales Month Sales
January P 9,000 July P 15,000
February 10,000 August 15,000
March 9,000 September 16,000
April 11,500 October 16,000
May 12,500 November 16,000
June 14,000 December 17,000
Philip, an accountant in the Planning and Budgeting Department is responsible for preparing the cash flow projection. The following information will be use in preparing the cash flow projection.
• Alpha Tech’s excellent record in accounts receivable collection is expected to continue. Sixty percent of billings are collected the month after the sale, and the remaining 40%, two months after.
• The purchase of electronic components is Alpha Tech’s largest expenditure, and each month’s cost of goods sold is estimated to be 40% of sales. Seventy percent of the parts are received by Alpha Tech one month prior to sale, and 30% are received during the month of sale.
• Historically, 75% of accounts payable has been paid one month after receipt of the purchased components, and the remaining 25% has been paid two months after receipt.
• Hourly wages and fringe benefits estimated to be 30% of the current month’s sales are paid in the month incurred.
• General and administrative expenses are projected to be P15,620 for the year. The breakdown of these expenses is presented in the following schedule. All cash expenditures are paid uniformly throughout the year, except the property taxes, which are paid in four equal installments at the end of each quarter.
Forecasted General and Administrative Expenses
(in thousands)
Salaries and fringe benefits P 3,200
Promotion 3,800
Property Taxes 1,360
Insurance 2,000
Utilities 1,800
Depreciation 3,460
Total P15,620
=======
• Ignore income taxes computation
• Alpha Tech maintains a minimum cash balance of P 500,000. If the cash balance is less than P500,000 at the end of each month, the company borrows amounts necessary to maintain the balance. All amounts borrowed are repaid out of the subsequent positive cash flow. The projected April 2013 opening balance is P500,000.
• Alpha Tech has no short term debt as of April 2013.
• Alpha Tech use a calendar year for both financial reporting and tax purposes.
Required: Based on the above data, prepare a cash budget for Alpha Tech by month for the second quarter. Ignore interest expense associated with borrowing
What I did was:
I. Worksheet Total
January February March April May June July August September October November December
Sales 9,000 10,000 9,000 11,500 12,500 14,000 15,000 15,000 16,000 16,000 16,000 17,000
Collections
60% in the month after the sale 0.6 5,400 6,000 5,400 6,900 7,500 8,400 9,000 9,000 9,600 9,600 9,600
40% two months after the sale 0.4 3,600 4,000 3,600 4,600 5,000 5,600 6,000 6,000 6,400 6,400
Total collections - 5,400 9,600 9,400 10,500 12,100 13,400 14,600 15,000 15,600 16,000 16,000 137,600
COGS: 60% of sale 0.6 5,400 6,000 5,400 6,900 7,500 8,400 9,000 9,000 9,600 9,600 9,600 10,200 96,600
Purchases:
70% prior to sale 0.7 - 6,300 7,000 6,300 8,050 8,750 9,800 10,500 10,500 11,200 11,200 11,200 100,800
30% during the month of sale 0.3 2,700 3,000 2,700 3,450 3,750 4,200 4,500 4,500 4,800 4,800 4,800 5,100 48,300
Total Purchases 2,700 9,300 9,700 9,750 11,800 12,950 14,300 15,000 15,300 16,000 16,000 16,300 149,100
Total Inventory 8,100 15,300 15,100 16,650 19,300 21,350 23,300 24,000 24,900 25,600 25,600 26,500 245,700
Less Beg Inventory 0 2,700 9,300 9,700 9,750 11,800 12,950 14,300 15,000 15,300 16,000 16,000 132,800
Total Budgeted Purchases 8,100 12,600 5,800 6,950 9,550 9,550 10,350 9,700 9,900 10,300 9,600 10,500 112,900
Cash Payments
75% one month after 0.75 - 2,025 6,975 7,275 7,313 8,850 9,713 10,725 11,250 11,475 12,000 12,000 99,600
25% two months after 0.25 - - 675 2,325 2,425 2,438 2,950 3,238 3,575 3,750 3,825 4,000 29,200
Total Cash Payments - 2,025 7,650 9,600 9,738 11,288 12,663 13,963 14,825 15,225 15,825 16,000 128,800
Other Cash Payments
Hourly Wages - 30% current m/s 0.3 2,700 3,000 2,700 3,450 3,750 4,200 4,500 4,500 4,800 4,800 4,800 5,100 48,300
Genral and Admin Expense:
Salaries and Fringe Benefits 266.67 266.67 266.67 266.67 266.67 266.67 266.67 266.67 266.67 266.67 266.67 266.67 3,200
Promotion 316.67 316.67 316.67 316.67 316.67 316.67 316.67 316.67 316.67 316.67 316.67 316.67 3,800
Property Taxes 340.00 340.00 340.00 340.00 1,360
Insurance 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 166.67 2,000
Utilities 150.00 150.00 150.00 150.00 150.00 150.00 150.00 150.00 150.00 150.00 150.00 150.00 1,800
Depreciation 288.33 288.33 288.33 288.33 288.33 288.33 288.33 288.33 288.33 288.33 288.33 288.33 3,460
Total Gen and Admin Expense 1,188.33 1,188.33 1,528.33 1,188.33 1,188.33 1,528.33 1,188.33 1,188.33 1,528.33 1,188.33 1,188.33 1,528.33 15,620.00
Total Cash Payments 3,888.33 6,213.33 11,878.33 14,238.33 14,675.83 17,015.83 18,350.83 19,650.83 21,153.33 21,213.33 21,813.33 22,628.33 192,720.00
Cash Budget
Desired ending balance 500,000.00
Beg Cash balance 500,000.00 500,000.00 500,000.00 500,000.00 500,000.00 500,000.00 500,000.00 500,000.00 500,000.00 500,000.00 500,000.00 500,000.00 6,000,000.00
Cash Receipts - 5,400.00 9,600.00 9,400.00 10,500.00 12,100.00 13,400.00 14,600.00 15,000.00 15,600.00 16,000.00 16,000.00 137,600.00
Total cash 500,000.00 505,400.00 509,600.00 509,400.00 510,500.00 512,100.00 513,400.00 514,600.00 515,000.00 515,600.00 516,000.00 516,000.00 6,137,600.00
Cash Payments 3,888.33 6,213.33 11,878.33 14,238.33 14,675.83 17,015.83 18,350.83 19,650.83 21,153.33 21,213.33 21,813.33 22,628.33 192,720.00
Less: Depreciation 288.33 288.33 288.33 288.33 288.33 288.33 288.33 288.33 288.33 288.33 288.33 288.33 3,460.00
Total Cash Payments 3,600.00 5,925.00 11,590.00 13,950.00 14,387.50 16,727.50 18,062.50 19,362.50 20,865.00 20,925.00 21,525.00 22,340.00 189,260.00
Additional Payment - 3,600.00 4,125.00 6,115.00 10,665.00 14,552.50 19,180.00 23,842.50 28,605.00 34,470.00 39,795.00 45,320.00 230,270.00
Total Cash Payment - 9,525.00 15,715.00 20,065.00 25,052.50 31,280.00 37,242.50 43,205.00 49,470.00 55,395.00 61,320.00 67,660.00 415,930.00
Net Cash Inflow 496,400.00 495,875.00 493,885.00 489,335.00 485,447.50 480,820.00 476,157.50 471,395.00 465,530.00 460,205.00 454,680.00 448,340.00 5,718,070.00
Financing 3,600.00 4,125.00 6,115.00 10,665.00 14,552.50 19,180.00 23,842.50 28,605.00 34,470.00 39,795.00 45,320.00 51,660.00 281,930.00
Ending Balance after financing 500,000.00 500,000.00 500,000.00 500,000.00 500,000.00 500,000.00 500,000.00 500,000.00 500,000.00 500,000.00 500,000.00 500,000.00 6,000,000.00
