Sunny Days manufactures patio furniture, April Day, the company manager has provided the following information and is requesting you prepare a master budget for Sunny Days for the first 6 month of of 2020.

Balances in select accounts on January 1, 2020:

Cash $40,000

Accounts receivable $140,000

Inventory $105,000

Accounts Payable $65,000

Other Information:

Sales for January are budgeted to be $300,000

A) Percentage increase in sales for each month will be 5%

B) Cost of goods sold 50%

C) Sales are 60% cash

D) Credit sales are collected: 50% in month of sale, 40% in the month following sale, 10% in the second month following sale

E) The net Accounts Receivable on January 1st is comprised of $125,000 from December 2017 sales and $15,000 from November sales

F) Ending inventory for each month should equal 70% of the following months budgeted cost of goods sold

G) 60% of a month's inventory purchases are paid in the month of purchase, 40% in the first month following the sale

H) Depreciation is $15,000 for the first half of the year

I) The following monthly expenses are paid in the month when incurred:

Commission percentage on sales equal to 30%

Rent of $5,000 per month

Depreciation of $2,500 per month

Other operating expenses excluding depreciation is equal to 30% of sales

J) The company building will require repairs of $10,000. The work is expected to be paid in February. The repair will not increase the value of the building.

K) The company prefers to increase minimum cash balance equal to $80,000 in January. If cash balance is below this amount funds need to be burrowed to meet this requirement. All borrowing occurs at the beginning of a month. Interest on the loan is 6% per year or 0.5% per month.

Only when cash balance is above minimum balance is there to be any payment of outstanding loans with interest is to be paid first. Interest is computed on all outstanding amounts for a month and is added to financing amount when not paid in a month. The outstanding financing amount includes beginning balance for the month plus any new borrowing for the month and any unpaid interest.

Required:

Using Excel prepare the following for the first six months of the year:

1. Schedule of expected cash collections for the first half of the year

2. Merchandise purchases budget

3. Schedule of expected cash disbursements

4. Cash budget (computation of excess/deficiency in cash, loan balance, interest payment, loan borrowing/payment)

5. Determine the balance in net accounts receivable for June 30, 2020

6. Determine the balance in accounts payable for June 30, 2020

7. Determine the balance in inventory for June 30, 2020

8. Prepare Income statement for the period January to June 2020

9. Redo 1-8 above with the following changes and save as a separate spreadsheet assuming:

• Growth rate for sales is equal to 5%

• Commission percentage is equal to 30%

• Cost of goods is 40%

• Other operating expenses excluding depreciation as a percentage of sales of 35%

• Sales are 50% cash