Question

Excel Online Structured Activity: Cash Budgeting Dorothy Koehl recently leased space in the Southside Mall and opened a new business, Koehl's Doll Shop. Business has been good, but Koehl frequently runs out of cash. This has necessitated late payment on certain orders, which is beginning to cause a problem with suppliers. Koehl plans to borrow from the bank to have cash ready as needed, but first she needs a forecast of just how much she must borrow. Accordingly, she has asked you to prepare a cash budget for the critical period around Christmas, when needs will be especially high. Sales are made on a cash basis only. Koehl's purchases must be paid for during the following month. Koehl pays herself a salary of $4,600 per month, and the rent is $2,200 per month. In addition, she must make a tax payment of $14,000 in December. The current cash on hand (on December 1) is $500, but Koehl has agreed to maintain an average bank balance of $6,500 - this is her target cash balance. (Disregard the amount in the cash register, which is insignificant because Koehl keeps only a small amount on hand in order to lessen the chances of robbery.) The estimated sales and purchases for December, January, and February are shown below. Purchases during November amounted to $100,000. Sales Purchases December $180,000 $35,000 January 44,000 35,000 February 54,000 35,000 The data has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the question below.

please solve the question for me.thank you

Answer 1

Prepare the cash budget for the month December, January, and February: Cash Budget of "D" for the Months December, January and February Particulars December January February Collections and Purchases Sales $180,000 $44,000 $54,000 Purchases Payments for purchases Salaries Rent Taxes $35,000 $35,000 $35,000 $100,000 $35,000 $35,000 $4,600 $4,600 $4,600 $2,200 $2,200 $2,200 $14.000 $155,800 $41.800 $41,800 $500 $23.700 $2,200 $12,200 $23.700 $25,900 $38,100 $6,500 $6,500 $6,500 Total Payments Cash at start of forecast Net Cash flow Cumulative NCF Target Cash Balance Surplus Cash or Loans needed $17,200 $19,400 $31,600 Working notes: Calculation is given below:

Cash Budget of "D" for the Months December, January and February Particulars December January February Collections and Purchases 180000 44000 54000 Sales Purchases 35000 35000 35000 Payments for purchases 100000 35000 35000 Salaries 4600 4600 4600 Rent 2200 2200 2200 Taxes 14000 Total Payments =SUM(C8:C12) =SUM(D9-D11) I=SUM(E9:E11) Cash at start of forecast 500 Net Cash flow =C6-C13-C14 =D6-D13 =E6-E13 Cumulative NCF 23700 =C16+D15 =D16+E15 Target Cash Balance 6500 6500 6500 Surplus Cash or Loans needed =C16-C17 =D16-017 =E16-E17 NCF

Prepare the cash budget for the month December, January, and February: Cash Budget of "D" for the Months December, January and February Particulars December January February Collections and Purchases Sales SO $180,000 $44.000 Purchases $35,000 $35,000 $35.000 Payments for $100,000 $35,000 $35,000 Salaries $4,600 $4,600 $4,600 Rent $2,200 $2,200 $2,200 Taxes $14.000 Total Payments $155,800 $41,800 $41,800 Cash at start of $500 Net Cash flow ($156,300) $138,200 $2,200 Cumulative NCF ($156,300)| ($18,100)|($15.900) Target Cash $6,500 $6,500 $6,500 Balance Surplus Cash or $162,800) (S24,600) (S22,400) Loans needed Working notes: Calculation is given below:

Cash Budget of "D" for the Months December, January and February Particulars December January February Collections and Purchases Sales 180000 44000 Purchases 35000 35000 35000 Payments for purchases 100000 35000 35000 Salaries 4600 4600 4600 Rent 2200 2200 2200 Taxes 14000 Total Payments =SUM(C7:C11) =SUMD8-D10) =SUM(E8:E10) Cash at start of forecast 500 Net Cash flow =C5-C12-C13 =D6-D12 =E6-E12 Cumulative NCF |-156300 =C15+D14 =D15+E14 6500 6500 Target Cash Balance 6500 Surplus Cash or Loans needed =C15-C16 =D15-D16 =E15-E16