Question

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 materials budget, including a schedule of expected cash disbursements for purchases of materials
3. Prepare a cash budget.

Answer 1

To prepare a sales budget, estimate the sales revenue and create a schedule of expected cash collections. To prepare a direct materials budget, estimate materials needed and create a schedule of expected cash disbursements. Lastly, prepare a cash budget by considering all sources of cash inflows and outflows.

Step-by-step explanation:

1. Prepare a sales budget, including a schedule of expected cash collections

In order to prepare a sales budget, you would need to estimate the sales revenue for a given period. This can be done by analyzing historical sales data, market trends, and other relevant factors. Once you have estimated the sales revenue, you can then create a schedule of expected cash collections by estimating the timing and method of payment for each sale. This schedule will help you determine the expected cash inflows from sales.

2. Prepare a direct materials budget, including a schedule of expected cash disbursements for purchases of materials

To prepare a direct materials budget, you need to estimate the quantity and cost of materials required for production. This can be done by analyzing historical usage, production plans, and other relevant factors. Once you have estimated the materials needed, you can create a schedule of expected cash disbursements by estimating the timing and method of payment for each purchase. This schedule will help you determine the expected cash outflows for materials.

3. Prepare a cash budget

A cash budget is a financial plan that shows the expected cash inflows and outflows for a given period. To prepare a cash budget, you need to consider all the sources of cash inflows (such as sales revenue, investments, loans) and the uses of cash outflows (such as expenses, purchases, debt repayments). By comparing the inflows and outflows, you can determine if there will be any cash surpluses or deficits during the period.

Answer 2

The company will need to borrow $36,450 from its line of credit in March to maintain the minimum cash balance requirement.

How to solve

Schedule of Expected Cash Collections for March

Month Cash Sales Collections from Month of Sale Collections from Month Following Sale Total Collections

March $5,100 $1,600 $2,250 $8,950

Schedule of Expected Cash Disbursements for Inventory Purchases for March

Month Payments from Month of Purchase Payments from Month Following Purchase Total Disbursements

March $14,100 $4,700 $18,800

Cash Budget for March

| | Amount | |

| The start of the cash balance | $11,500 | |

| Cash receipts | | |

| Cash sales | $5,100 | |

| Collections from accounts receivable | $8,950 | |

| Cash receipts in total | $14,050 | |

| Disbursements of cash | | |

| Purchases of inventory | $18,800 | |

| Selling and administrative expenses | $12,500 | |

| Depreciation | $3,200 | |

| Equipment purchases | $14,000 | |

| Dividends | $2,000 | |

| Total cash disbursements | $50,500 | |

| Net cash flow | $(36,450) | |

| Financing | | |

Utilizing a line of credit for borrowing: $36,450.

Ending cash balance: $0.

As indicated in the cash budget, the company will need to borrow $36,450 from its line of credit in March to maintain the minimum cash balance requirement.

The Complete Question

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 References 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 matenials budget, including a schedule of expected cash disbursements for purchases of materials. 0 日 勺. c·

4- cash Budget with Supporting Cash Collections and Osbursements Schedules-Excel ? ; Sign In HOME INSERT PAGE LAYOUTFORMULAS DATA REVIEWVEW A Alignment Number Conditional Format as Cell Cells Editing PasteB Clipboard A1 Formatting. Table- Styles. xS&.P Enterprises needs a cash budget for March, The following Information is S& PEnterprises needs a cash budget for March.

The following infor nation is available. 3 Data January February March Actual January and February and expected March sales: 5 Cash sales 6 Sales on account 7 Total Sales $ 1,600 3,750$5,100 9 Accounts Recelvable Collections: 10 Month of sale 11 Month following sale 12 Second month following sale 15% 60% 22% 14 1s Accounts payable for inventory purchases, March 1 balance 16 Budgeted inventory purchases in March 17 Inventory payments: $10,500 $23,500 Month of purchase 60% 1Month following purchase 20 21 Total budgeted selling & administrative expenses in March 22 Budgeted selling & administrative depreciation in March 23 $12,500 $3,200 c in Manch | BudgetData | Schedules and Cash Budget 1 C) Attemptis) References Cash Budget with Supporting Cash Collections and Disbursements Schedules