Cash Budget with Supporting Cash Collections and Disbursements Schedules 1. Prepare a sales budget, including a schedule of expected cash collections. 2. Prepare a direct materials budget, including a schedule of expected cash disbursements for purchases of materials. 3. Prepare a cash budget. All answers must be entered as a formula. Click OK to begin. OK FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW Sign in Calibri - 11 Paste BIU- - A % Alignment Number Conditional Format as Cell Cells Editing Formatting Table Styles Styles fx S&P Enterprises needs a cash budget for March. The following information is Clipboard Font A1 v 3 E A B D 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 6 Sales on account 25,000 30,000 40,000 7 Total Sales $ 26,600 $ 33,750 $ 45,100 in 8 10 11 15% 60% 22% 3% 9 Accounts Receivable Collections: Month of sale Month following sale 12 Second month following sale 13 Uncollectible 14 15 Accounts payable for inventory purchases, March 1 balance 16 Budgeted inventory purchases in March 17 Inventory payments: Month of purchase 19 Month following purchase 20 $10,500 $23,500 18 60% 40% Paste 3 > FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW Sign in Calibri 11 % M BIU Alignment Number Conditional Format as Cell Cells Editing Formatting Table Styles Clipboard Font Styles A1 for S&P Enterprises needs a cash budget for March. The following information is B D E 14 15 Accounts payable for inventory purchases, March 1 balance $10,500 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 $12,500 22 Budgeted selling & administrative depreciation in March $3,200 23 24 Other budgeted cash disbursements in March 25 Equipment purchases $14.000 26 Dividends to be paid $2,000 27 28 Minimum cash balance to be maintained $10,000 29 March 1 cash balance $11,500 30 March 1 outstanding borrowings SO 31 March 1 interest due $0 32 33 The company has a line of credit available to bolster the cash balance as needed. 24 FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW Sign In X Calibri A % M Paste BIU- Cells Alignment Number Conditional Format as Cell Formatting" Table" Styles Editing Clipboard Font Styles A1 > fo S&P Enterprises needs a cash budget for March. The following information is 5 B D E 22 Budgeted selling & administrative depreciation in March $3,200 23 24 Other budgeted cash disbursements in March 25 Equipment purchases $14,000 26 Dividends to be paid $2.000 27 28 Minimum cash balance to be maintained $10,000 29 March 1 cash balance $11,500 30 March 1 outstanding borrowings SO 31 March 1 interest due SO 32 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. 37 Click the Schedules and Cash Budget tab to prepare the following: 1. Schedule of expected cash collections for March. 39 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 41 any interest will not be paid until the following month. 36 38 40 42 For your Chapter 20 Excel Simulation, you have two excel sheets, Budget Data & Schedules & Cash Budget. You will be required to use data in the Budget Data sheet when developing formulas to create information in the Schedules & Cash Budget. In order to use data in one sheet and transfer the result into another sheet, your formula will need to be modified to include the sheet reference. Let's assume you want to use the information in the Budget Data sheet (take 36*B12 in the Budget Data sheet and include the result in B4 in the Schedules & Cash Budget sheet) the formula to do that in cell B4 in the Schedules & Cash Budget sheet is: -Budget Data'?B6 Budget Data'1B12. If you don't use the sheet reference, your answer will be marked wrong