Sunny Days manufactures patio furniture. April Day, the company treasure has provided the following information and is requesting you prepare a master budget for Sunny Days for the first six months of 2021.

Balances in select accounts on January 1, 2021:

Cash $40,000

Accounts receivable $140,000. Collection in January is $112,000 and 28,000 in February.

Inventory $100,000

Accounts payable (all merchandise inventory) $65,000. Of this amount $48,750 will be paid in January and $16,250 in February.

Other information:

Sales for January are budgeted to be $300,000

Cash sales collected in month of sale will receive 2% discount.

a. Percentage increase in sales for each month will be 5%

b. Cost of goods sold 50%

c. Sales are 40% cash

d. Credit sales are collected:

50% in the month of sale

40% in the month following sale

10% in the second month following sale

3. Ending inventory for each month should equal 70% of the following month's budgeted cost of

goods sold.

g. Sixty percent of a month's inventory purchases are paid in the month of purchase, 30 percent

percent in the first month following sale and 10 percent in the second following month.

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 $ 2,500 per month

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

j. The company building will require repairs costing $10,000. The work is expected to be

paid in February. The repair will not increase the value of the building.

k. Dividends of $60,000 will be paid in February and $10,000 in April.

l. If cash balance is below $40,000 funds need to be borrowed to meet this requirement.

All borrowing occurs at the beginning of a month. Interest on the loan is 6% per year or .5%

per month.

Only when cash balance is above minimum balance (40,000) 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. (Note sensitivity analysis will need to be conducted and submission is the excel workbook prepared) :

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

2. Cost of goods sold for each month and merchandise purchases budget 10%

3. Scheduled of expected cash disbursements10%.

4. Cash budget 25% (computation of excess/deficiency in cash, loan balance, interest

payment, loan borrowing /payment. )

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

6. Determine the balance in accounts payable for June 30,2021. 5%

7. Determine the balance in inventory for June 30 5%

8. Prepare income statement for the period January to June, 2021 10%