project 5

ACCT122 - Project #5: Preparing a master budget

The Sloan Company, a retailer of auto parts, has requested your assistance in preparing a master budget for their company using the following information:

Information for the operating budget

1. Sales in April were $72,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 $95,000.

3. Monthly purchases average 50% of sales revenue in that same month.

4. The company maintains an inventory level of $10,000 plus 25% of the sales revenue budgeted for the following month.

5. Actual ending inventory on April 30 is $26,000.

6. Monthly salaries total $4,000. Sales commissions equal 4% of sales for that month.

7. Employee’s benefits are 16% of total salaries & commissions.

8. Other monthly expenses:

a. Rent $2,800 (paid as incurred)

b. Depreciation $600 for May and June

c. Depreciation $750 for July and August

d. Insurance expense $300 (expiration of prepaid amount)

e. Miscellaneous expenses are 2% of each month’s sales (paid with cash)

f. Income tax 20% of operating income after interest expense.

Information for the financial budget

1. Sales are 75% cash and 25% credit. Credit sales are collected in the month after the sale.

2. Inventory purchases are paid 25% in the month of purchase and 75% the following month. Purchases in April were $32,000.

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 $30,000. At the end of each month when the account balance dips below the $30,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:

a. Cash $2,500

b. Salaries and commissions payable $2,850 (will be paid in May)

c. Employee’s benefits payable $800 (will be paid in May)

d. Borrowed funds from the bank $0

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:

a. Budgeted cash collections

b. Budgeted cash payments for purchases

c. Budgeted cash payments for operating expenses

3. Prepare a cash budget.

4. Prepare a budgeted balance sheet as of August 31, 2013. The balance sheet for April 30, 2013 is provided.

5. A spreadsheet is provided for you to use. Note that each section is on a separate tab (located at the bottom of the sheet).