Colorado adventures, Inc., sells snowsport equipment. Maya Grenier is the controller for

Colorado adventures and put together the below initial cash budget for the fourth quarter of the

year.

Colorado adventures, Inc.

Cash Budget

For the Quarter Ended December 31

October November December Quarter

Beginning cash balance $ 26,000 $ 20,000 $ 20,000 $ 26,000

Add collections from customers 184,000 318,000 370,000 872,000

Total cash available 210,000 338,000 390,000 898,000

Less cash disbursements:

Purchases for inventory 178,500 259,000 227,500 665,000

Marketing expenses 39,500 60,000 31,000 130,500

Administrative expenses 12,500 16,000 10,500 39,000

Land purchases 8,000 8,000

Dividends paid 24,500 24,500

Total cash disbursements 255,000 343,000 269,000 867,000

Excess (deficiency) of cash available

over disbursements (45,000) (5,000) 121,000 31,000

Financing:

Borrowings 65,000 25,000 0 90,000

Repayments 0 0 (90,000) (90,000)

Interest 0 0 (2,450) (2,450)

Total financing 65,000 25,000 (92,450) (2,450)

Ending cash balance $ 20,000 $ 20,000 $ 28,550 $ 28,550

The company generally borrows money during this quarter to support peak sales. The above

cash budget was based on assembling the following data:

a. Budgeted monthly income statements for October-January are:

October November December January

Sales $ 300,000 $ 450,000 $ 250,000 $ 200,000

Cost of goods sold 210,000 315,000 175,000 140,000

Gross margin 90,000 135,000 75,000 60,000

Operating expenses:

Marketing expense 39,500 60,000 31,000 25,500

Administrative expense* 22,500 26,000 20,500 19,000

Total operating expenses 62,000 86,000 51,500 44,500

Net operating income $ 28,000 $ 49,000 $ 23,500 $ 15,500

*Includes $10,000 of depreciation each month.

b. Each month, 20 percent of sales are for cash and 80 percent are on credit. The collection

pattern for credit sales is 10 percent collected in the month of sale, 70 percent in the first

month following the month of sale, and 20 percent in the second month following the

month of sale. August's sales totaled $100,000, and September's sales totaled $150,000.

c. Inventory purchases are on account. Of those purchases, 50 percent are paid in the month

of purchase. The remaining 50 percent is paid in the following month. Accounts payable

at September 30 for inventory purchases during September total $63,000.

d. The merchandise inventory on October 1 is $42,000. The desired ending inventory for

each month is 20 percent of the cost of the merchandise to be sold the next month.

e. Dividends of $24,500 will be declared and paid in October.

f. Land costing $8,000 will be purchased for cash in November.

g. The cash balance on October 1 is $26,000. The company wants to have an ending cash

balance of at least $20,000. If a cash shortage develops, sufficient cash is borrowed to

cover the shortage and provide the desired ending balance. Any cash borrowed must be

borrowed in increments of $500 at the beginning of each month. The interest rate on

these loans is 1 percent per month (simple interestthat is, assume no compounding).

The company would, as possible given minimum requirement, repay the loan plus

accumulated interest at the end of the quarter.

Colorado Adventure's president wants to know how reducing inventory levels and collecting

accounts receivable sooner will impact the cash budget. She has asked Maya and her staff to

revise the cash collection and ending inventory assumptions as follows:

a. Credit sales still account for 80 percent of total sales. However, the collection period for

October, November, and December credit sales is 25 percent collected in the month of

sale, 65 percent collected in the month following sale, and 10 percent in the second

month following sale. (Any credit sales from August and September collected during the

fourth quarter use the collection percentages noted originally in the previous section.)

b. The company maintains its ending inventory levels for October, November, and

December at 15 percent of the cost of merchandise to be sold in the following month.

(The merchandise inventory at October 1 remains $42,000 and accounts payable for

inventory purchases at September 30 remains $63,000 as noted originally in the previous

section.)

Required:

Your group members are part of the Controller's office. Using the new assumptions, Maya has

asked you to prepare the below. Prepare these items using the Excel spreadsheet provided as

part of the assignment. For the budgets, your task is to enter formulas (cell references) to

calculate required numbers ("Requirements 1 - 4" worksheet). For the cash budget financing

section only, if there are any borrowings, repayments, and/or interest, type out supporting

calculations in the related section noted at the bottom of the worksheet, putting the final number

in its own cell. Then, reference the cell in the cash budget financing section. If the item is zero,

leave the space blank. Do NOT modify the spreadsheet format (for example, do not add rows or

columns, change spacing or margins, change provided data, etc...).

1. Prepare the schedule of expected cash collections for October, November, and December and

for the quarter in total.

2. Prepare the merchandise purchases budget for October, November, and December and for the

quarter in total.

3. Prepare the schedule of expected cash disbursements for merchandise purchases for October,

November, and December and for the quarter in total.

4. Prepare the cash budget for October, November, and December, and for the quarter in total.

5. Compare the original cash budget to your revised cash budget. To better prepare Maya for

her meeting with the president, discuss (explain) how the revised assumptions affect the cash

budget. That is, not only note the changes but also discuss why changes in accounts

receivables collections and inventory levels have an impact