Excel SIM: 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 materlals budget, including a schedule of expected cash disbursements for purchases of matenals. 3. Prepare a cash budget \begin{tabular}{|l|l|} \hline 21 & Total budgeted selling \& administrative expens \\ \hline 22 & Budgeted selling \& administrative depreciation \\ \hline 23 & \\ \hline 24 & Other budgeted cash disbursements in March \\ \hline \end{tabular} \begin{tabular}{|l|l|r|} \hline 25 & Equipment purchases & $14,000 \\ \hline 26 & Dividends to be paid & $2,000 \\ \hline 27 & & \\ \hline 28 & Minimum cash balance to be maintained & $10,000 \\ \hline 29 & March 1 cash balance & $11,500 \\ \hline 30 & March 1 outstanding borrowings & $0 \\ \hline 31 & March 1 interest due & $0 \\ \hline \end{tabular} 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: 38 1. Schedule of expected cash collections for March. 39 2. Schedule of expected cash disbursements for inventory purchases for March. 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 Click the Schedules and Cash Budget tab to prepare the following: 38 1. Schedule of expected cash collections for March. 39 2. Schedule of expected cash disbursements for inventory purchases for March. 40 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. \begin{tabular}{|l|l|} \hline 1 & Expected cash collections: \\ \hline 2 & March cash collections \\ \hline 3 & March collections on account: \\ \hline 4 & January sales \\ \hline 5 & February sales \\ \hline 6 & March sales \\ \hline 7 & Total cash collections \\ \hline 8 & \\ \hline 9 & Payments to suppliers: \\ \hline 10 & Accounts payable for inventory purchases, beginning balance \\ \hline 11 & March purchases \\ \hline 12 & Total cash payments \\ \hline 13 & \\ \hline 14 & For the Month of March \\ \hline 15 & Cash Budget \\ \hline 16 & \\ \hline 17 & Cash balance, March 1 \\ 18 & \\ \hline 19 & Add cash receipts: \\ 20 & Collections from customers \\ \hline \end{tabular} Cash balance, March 1 Add cash receipts: 20 Collections from customers 21 Total cash available 22 Less disbursements: 23 Payments to suppliers Selling and administrative expenses Equipment purchases Dividends paid Total disbursements 28 Preliminary cash balance Financing: 30 Additional loan 31 Loan repayments 32 Interest 33 Total financing 34 Cash balance, March 31
