Excel Project Managerial Accounting

Excel project #1 – Example

XYZ products needs a cash budget for October. The following information is available:

a. The cash balance at the beginning of October is $9,000.

b. Actual sales for August and September and expected sales for October are as follows:

| |August |September |October |

|Cash Sales |$6,600 |5,000 |9,200 |

|Sales on Account |18,000 |33,000 |38,000 |

|Total Sales |24,600 |38,000 |47,200 |

Sales on account are collected over a three-month period as follows: 10% collected in the month of sale, 60% collected in the month following sale, and 28% collected in the second month following sale. The remaining 2% is uncollectible.

c. Purchases of inventory will total $20,000 for October. Thirty percent of a month’s inventory purchases are paid for during the month of purchase. The accounts payable remaining from September’s inventory purchases total $19,000, all of which will be paid in October.

d. Selling and administrative expenses are budgeted at $15,000 for October. Of this amount, $4,000 is for depreciation.

e. Equipment costing $18,000 will be purchased for cash during October, and dividends totaling $4,000 will be paid during the month.

f. The company maintains a minimum cash balance of $5,000. An open line of credit is available from the company’s bank to bolster the cash balance as needed.

Required:

1. Prepare a schedule of expected cash collections for October.

2. Prepare a schedule of expected cash disbursements for inventory purchases for October....