Excel formulas for cash budget

Al Xf S&P Enterprises needs a cash budget for March. The following information i D E 1 S & P Enterprises needs a cash budget for March. The following information is available. 2 3 Data January February March 4 Actual January and February and expected March sales: 5 Cash sales $ 1,600 $3,750 $ 5,100 30,000 40,000 6 Sales on account 25,000 Total Sales $ 26.600 $ 33.750 $45.100 9 Accounts Receivable Collections: Month of sale 10 15% 11 Month following sale 60% 12 Second month following sale 22% 13 Uncollectible 3% 14 $10,500 15 Accounts payable for inventory purchases, March 1 balance 16 Budgeted inventory purchases in March $23,500 17 Inventory payments: 18 Month of purchase 60% 19 Month following purchase 40% 20 21 Total budgeted selling & administrative expenses in March 22 Budgeted selling & administrative depreciation in March $12,500 $3,200 23 24 Other budgeted cach dichureamente in March. Budget Data Schedules and Cash Budget READY Attemptie) 8 (0) E 22 Budgeted selling & administrative depreciation in March $3,200 23 24 Other budgeted cash disbursements in March 25 Equipment purchases Dividends to be paid $14,000 $2,000 26 27 28 Minimum cash balance to be maintained $10,000 29 March 1 cash balance $11,500 30 March 1 outstanding borrowings $0 31 March 1 interest due $0 32 33 The company has a line of credit available to bolster the cash balance as needed. 34 A 45 1 Expected cash collections: 2 March cash collections 3 March collections on account: January sales February sales 6 March sales 7 Total cash collections 8 9 Payments to suppliers: 10 Accounts payable for inventory purchases, beginning balance 11 March purchases 12 Total cash payments 13 14 S&P Enterprises Cash Budget 15 16 For the Month of March 17 18 Cash balance, March 1 19 Add cash receipts: 20 Collections from customers 21 Total cash available before current financing 22 Less disbursements: 23 Payments to suppliers 24 Selling and administrative exnenses Budget Data B a D E 3 Payments to suppliers 4 Selling and administrative expenses 5 Equipment purchases 6 Dividends paid 27 Total disbursements 8 Excess (deficiency) of cash available over disbursements 29 Financing: 30 Borrowings 31 Repayments 32 Interest 33 Total financing 34 Cash balance, March 31 35 36 B P