File Home Insert Draw Formulas Data Review View Help 329 Calibri 11 B A D20 fx A 12345 S&P Enterprises needs a cash budget for March. The following information is available. Data 5 Actual January and February and expected March sales: 6 Cash sales 7 Sales on account 8 Total Sales 9 10 Accounts Receivable Collections: 11 Month of sale 12 Month following sale 13 Second month following sale 14 Uncollectible 15 Accounts payable for inventory purchases, March 1 balance 16 Budgeted inventory purchases in March 17 Inventory payments: General B C D E January February March $1,600 $3,750 $5,100 25,000 30,000 40,000 $26,600 $33,750 $45,100 15% 60% 22% 3% $10,500 $23,500 60% 40% 18 Month of purchase 19 Month following purchase 20 21 Total budgeted selling & administrative expenses in March (including depreciation) 22 Budgeted depreciation in March (included in total selling and admin exp) $12,500 $3,200 23 24 Other budgeted cash disbursements in March Data Schedules and Cash Budget + Calculation Mode: Automatic Workbook Statistics Excel McGraw Hill Excel Question - Saved Search (Option + Q) File Home Insert Draw Formulas Data Review View Help: Calibri 11 B Av ... D20 XV fx A B C 23 24 Other budgeted cash disbursements in March 25 Equipment purchases 26 Dividends to be paid 27 28 Minimum cash balance to be maintained 29 March 1 cash balance 30 March 1 outstanding borrowings 31 March 1 interest due 32 $14,000 $2,000 $10,000 $11,500 $0 $0 33 The company has a line of credit available to bolster the cash balance as needed. 34 35 When preparing budgets, the company maintains their data on a separate sheet from the actual budget and schedules. 36 37 Required: 38 Click the Schedules and Cash Budget tab to prepare the following: 39 40 41 1. Schedule of expected cash collections for March. 2. Schedule of expected cash disbursements for inventory purchases for March. 3. Cash budget for March. Indicate in the financing section any borrowing that will be needed in March. Assume that any interest will not be paid until the following month. 42 43 44 Calibri 11 B Av F8 Xv fx A B 1 (Use cells A4 to B31 from the Data Sheet to complete this question.) 2 5 6 3 Expected cash collections: 4 March cash collections January sales $5,100 March collections on account: 7 February sales 18,000 8 March sales 9 Total cash collections 10 11 Payments to suppliers: 12 Accounts payable for inventory purchases, beginning balance $10,500 13 March purchases 14,100 14 Total cash payments $24,600 15 16 17 S&P Enterprises Cash Budget 18 19 Cash balance, March 1 20 Add cash receipts: 21 Collections from customers 22 Total cash available 23 Less disbursements: 24 Payments to suppliers For the Month of March Data Schedules and Cash Budget + Calculation Mode: Automatic Workbook Statistics C Hint: To copy information from th For example, to find January sales $11,500 File Home Insert Draw Formulas Data Review View Help Calibri 11 B 4 Dv ab F8 XV fx 14 Total cash payments 15 16 5698 17 18 19 Cash balance, March 1 20 Add cash receipts: A 21 Collections from customers 22 Total cash available 23 24 25 26 27 Less disbursements: Payments to suppliers Selling and administrative expenses Equipment purchases Dividends paid 28 Total disbursements 29 Preliminary cash balance 36 37 38 2222222222233333 30 Financing: Additional loan 31 Loan repayments Interest 34 Total financing 35 Cash balance, March 31 S&P Enterprises Cash Budget For the Month of March = Data Schedules and Cash Budget + Calculation Mode: Automatic Workbook Statistics B D $24,600 14,000 2,000 $11,500 + (Hint HIH >