1. Prepare a sales budget, including a schedule of expected cash collections. 2. Prepare a direct materlals budget, including a schedule of expected cash disbursements for purchases of materlals 3. Prepare a cash budget Cash Budget with Supporting Cash Collections and Disbursements Schedules - Excel Sign In FILE OME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW Calibri 11 A A Conditional Format as Cell Paste Alignment Number Cells Editing A U Formatting Styles Table T Clipboard Styles Font fox S&P Enterprises needs a cash budget for March. The following information is Al A C E S& P Enterprises needs a cash budget for March. The following information is available 1 2 3 Data Jaary February March 4 Actual January and February and expected March sales: 3,750 5,100 S 5 Cash sales 1,600 Sales on account 25,000 30,000 6 40,000 S 26,600 33,750 S 45,100 7 Total Sales Accounts Receivable Collections: Month of sale 15% 10 Month following sale Second month following sale 60% 11 22% 12 Uncollectible 3% 13 14 $10,500 15 Accounts payable for inventory purchases, March 1 balance $23.500 16 Budgeted inventorv purchases in March IT 1. Prepare a sales budget, including a schedule of expected cash collections. 2. Prepare a direct materlals budget, Including a schedule of expected cash disbursements for purchases of materlals 3. Prepare a cash budget. X Cash Budget with Supporting Cash Collections and Disbursements Schedules Excel X = FILE REVIEW Sign In HOME INSERT PAGE LAYOUT FORMULAS DATA VIEW A A Calibri % 11 Conditional Format as Cell Alignment Number Paste Cells Editing I U Formatting Table Styles Clipboard Styles Font S&P Enterprises needs a cash budget for March. The following information is Al C E 16 Budgeted inventory purchases in March 17 Inventory payments: $23,500 Month of purchase 60% 18 Month following purchase 40% 19 20 $12,500 21 Total budgeted selling & administrative expenses in March $3,200 Budgeted selling & administrative depreciation in March 22 23 24 Other budgeted cash disbursements in March Equipment purchases Dividends to be paid $14,000 25 $2,000 26 27 28 Minimum cash balance to be maintained 29 March 1 cash balance 30 March 1 outstanding borrowings $10,000 $11,500 $0 31 March 1 interest due $0 . T Calibri % 11 A A Conditional Format as Formatting Cell Alignment Number Editing Paste Cells U T Styles Table Clipboard Styles Font S&P Enterprises needs a cash budget for March. The following information is Al A C E 20 $12,500 21 Total budgeted selling & administrative expenses in March $3,200 22 Budgeted selling & administrative depreciation in March 23 24 Other budgeted cash disbursements in March $14,000 Equipment purchases 25 Dividends to be paid $2,000 26 27 $10,000 28 Minimum cash balance to be maintained 29 March 1 cash balance $11,500 $0 30 March 1 outstanding borrowings 31 March 1 interest due 32 33 The company has a line of credit available to bolster the cash balance as needed 34 separate sheet from the actual budget and schedules 35 When preparing budgets, the company maintains their data on a 36 37 Click the Schedules and Cash Budget tab to prepare the following 1. Schedule of expected cash collections for March. 38 2. Schedule of expected cash disbursements for inventory purchases for March 39 3. Cash budget for March. Indicate in the financing section any borrowing that will be needed in March. Assume that 40 any interest will not be paid until the following month 41 42 43 44 Schedules and Cash Budget Budget Data READY 100% T Clipboard Font Styles Expected cash collections Al A B C D 1 Expected cash collections: 2 March cash collections 3 March collections on account: January sales February sales 4 5 March sales 6 7 Total cash collections 9 Payments to suppliers: 10 Accounts payable for inventory purchases, beginning balance 11 March purchases 12 Total cash payments 13 S& P Enterprises 14 Cash Budget 15 For the Month of March 16 17 18 Cash balance, March 1 19 Add cash receipts Collections from customers 20 21 Total cash available 22 Less disbursements Payments to suppliers 23 Selling and administrative expenses 24 Schedules and Cash Budget Budget Data (+ READY 100% AI ected casn collecuOns A C D 13 S & P Enterprises Cash Budget 14 15 For the Month of March 16 17 18 Cash balance, March 1 19 Add cash receipts: Collections from customers 20 21 Total cash available 22 Less disbursements: Payments to suppliers 23 Selling and administrative expenses 24 Equipment purchases 25 Dividends paid 27 Total disbursements 28 Preliminary cash balance 26 29 Financing: Additional loan 30 Loan repayments 31 Interest 32 33 Total financing 34 Cash balance, March 31 35 36 Schedules and Cash Budget Budget Data (+ READY 1008%