Sales are expected to increase from the base period by 3% each month. However, the business is cyclical, and sales will increase by 30% per month during May to July, then will fall back to the April level in August and increase by 2% per month after that.

Sales consist of 17% cash and 83% on accounts receivable.

Receivables are collected 20% in the month of sale, 70% in the following month, and 8% in the third month.

In order to maintain adequate inventory levels purchases will be 70% of the next month's sales.

Purchases are paid 60% in the month of purchase and 40% in the following month.

Commissions on sales are paid at the rate of 5% of sales and are paid in the month following the sale.

Rent on the building and equipment is $800 per month.

Salaries and wages are $1,200 per month. In addition, the staff will be paid a bonus in January of 5% of the amount by which sales exceed $250,000 for the year. Prior year sales were 295,000.

Other fixed operating expenses are $2,150 per month. This includes depreciation expense of $1,350 per month.

Property taxes of $2,600 are paid in April and $2,800 in October.

Sales taxes paid are 8.25% of sales. They are paid on the 15th of each month in the following month. (So sales tax on the Jan sales is paid on Feb 15.)

Beginning cash balance is $4,000.

If the cash balance falls below the minimum of $2,500 in any month, the company will borrow from their bank under a line of credit. No loan principal or interest repayments are made during the year. Maybe use an =if( , , , ) formula?

Some calculations:

Nov prior year

Dec prior year

Jan-17

Feb-17

Mar-17

Apr-17

May-17

Jun-17

Jul-17

Aug-17

Sep-17

Oct-17

Nov-17

Dec-17

Sales

21,800

22,000

22,660

23,340

Purchases

15,400

15,862

Cash Budget

Jan-17

Feb-17

Mar-17

Apr-17

May-17

Jun-17

Jul-17

Aug-17

Sep-17

Oct-17

Nov-17

Dec-17

Beginning cash balance

4,000

Collections:

Cash sales

3,852

3,968

Credit sales:

Current month

3,762

3,874

Prior month

Second prior month

1,448

Total collections from credit sales

5,209

Total collections from cash + credit sales

9,061

Less: Disbursements:

For inventory purchases:

Current month's

Prior month's

Sales commissions

Salaries and wages

Rent

Taxes - sales

Taxes - property

Other operating expenses

Total cash disbursements

Projected cash balance, end of month

4,000

Minimum cash balance required

2,500

Borrowing needed

Final projected cash balance, end of month

Memo items:

cash sales as % of total sales

17%

Credit sales collection rates:

month of sale

20%

1st month after sale

70%

2nd month after sale

8%

Payment for purchases:

month of purchase

60%

1st month after purchase

40%

Specific comments or concerns: These are worth 10 points.

Please note any comments or concerns and recommendations in the following rows.

1

2

3

4

others?