Using the template, how do I solve the problem?

Cash Budget Case Study Leonard & Associates Financial Planners have forecasted revenues for the first six months of 20x2, as shown in the following table: Month November 20X1 December 20x1 January 20x2 February 20x2 March 20x2 April 20x2 May 20x2 June 20x2 Revenue $48,000 45,000 25,000 27,000 30,000 38,000 40,000 45,000 The firm collects 60% of its sans immediately, 40% one month after the sale. Wages and benefits paid to clerical personnel are expected to be $7,000 per month while commissions to sales associates are 25% of total cash receipts each month. The sales commissions are paid one month after the cash is actually collected, based on total cash receipts. The two key partners are paid $5,000 per month Rent expense for their office space is $3,500 per month, and monthly lease expenses for their office equipment is $800. Utilities average $250 per month, except in May and June when they average only $150. The ending cash balance in December 20x1 was $12,000. The firm is required to maintain a minimum cash balance of $10,000 every month 1) Create a Cash Budget for January to June 20x2. 2) The firm is considering obtaining a line of credit from their bank. Based on the Cash Budget, what is the minimum amount that would be necessary in the line of credit or the maximum amount of the need? K May $40,000 June $45,000 SO $0 $0 $0 $0 8 G H 1 Leonard & Associates Financial Planners 2 Cash Budget 3 For the Period January to June 20x1 4 5 WORKSHEET November December January February March April 6 Sales $ 48,000 45.000 $25,000 $27.000 $30,000 $38,000 7 Cash Collections 8 Cash Sale 60% 9 First Month 40% 10 TOTAL Collections $0 $0 $0 SO 12 13 CASH BUDGET 14 TOTAL Cash Receipts $o $0 SO 15 Cash Disbursements 16 Wages 17 Leonard 18 Partner 2 19 Sales Associates 25% 20 Clerical 21 Rent 22 Equip Lease 23 Utilities 24 TOTAL Cash Disbursements $0 $0 $ $0 25 26 Monthly Cash Flow $0 $0 $0 $0 27 28 SUMMARY 29 Beginning Cash Balance $12,000 $12,000 $12,000 $12,000 30 Unadjusted Cash Position 12,000 12,000 12,000 12,000 31 Minimum Cash Required 10,000 10,000 10,000 10,000 32 Funding Need (Repmt) 33 Cummulative Borrowing 0 0 0 0 34 Ending Cash Balance $ 12,000 $12.000 $12.000 $12,000 $12,000 35 $0 $0 $0 $0 $12,000 12,000 10,000 $12,000 12,000 10,000 $12,000 $12,000