ACCT122 - Preparing a master budget project

The Sloan Company, a retailer of auto parts, has requested your assistance in preparing a master budget for the months of May, June & July.  The actual Balance Sheet for April 30, 2015 is attached.  Use the following information for Sloan Manufacturing to prepare the master budget: 

Information for the operating budget

1. Actual sales in April were $160,000.  You forecast that monthly sales will increase each month over the previous month by 5.0% in May; 6.0% in June; 7.0% in July; and 10.0% in August.
2. Sales budgeted for September is $224,000.
3. The company maintains an inventory level of $9,000 plus 25% of the cost of goods sold budgeted for the following month. 
4. Cost of Goods Sold is 55% of the monthly sales.
5. Actual ending inventory on April 30 is $29,000. 
6. Monthly salaries total $4,000.  Sales commissions equal 4% of sales for that month.
7. Other monthly expenses:
1. Rent $2,800 (paid as incurred)
2. Depreciation $600 for May and June
3. Depreciation $750 for July and August
4. Insurance expense $300 (expiration of prepaid amount)
5. Miscellaneous expenses are 2% of each month’s sales (paid with cash)
6. Income tax 20% of operating income after interest expense.

Information for the financial budget

1. Sales are 60% cash and 40% credit.  Credit sales are collected in the month after the sale.
2. Inventory purchases are paid 60% in the month of purchase and 40% the following month. 
3. Salaries and sales commissions are also paid half in the month earned and half the next month.
4. Employee benefits are paid the following month.
5. Income tax is paid at the end of the year.
6. The bank requires a minimum cash balance of $25,000.  At the end of each month when the account balance dips below the $25,000, the bank automatically extends credit to the store in multiples of $1,000.  Monthly installments of $500 are made at the end of the month plus the accrued interest when there are borrowed funds.  Interest is 12% annually on any borrowed funds.
7. April 30 balance sheet is attached.  Some notes on the balances:
8. The company purchased a delivery truck at the end of June for $35,000.

Required:

1. Prepare the budgeted income statements for May, June, July and August.  Round to the nearest whole dollar.
2. Prepare schedules of:
1. Budgeted cash collections
2. Budgeted cash payments for purchases
3. Budgeted cash payments for operating expenses
3. Prepare a cash budget.
4. Prepare a budgeted balance sheet as of August 31, 2015.  The balance sheet for April 30, 2015 is provided.
5. A spreadsheet is provided for you to use.