ACCT 116 Budget Assignment 50 Points Due November 20th — AT THE BEGINNING OF CLASS

Right Price Plant Company operates a garden center where peak sales occur in the month of May. Data regarding the store’s operations follow:

? Sales are budgeted at $300,000 for April, 350,000 for May, and $280,000 for June and $240,000 in July

? Collections are expected to be 20% in the month of sale, 75% in the month following the sale, 4% in the second month following sale and 1% uncollectible.

? The cost of goods sold is 72% of sales

. ? The company desires ending merchandise inventory to equal 20% of the following month’s cost of goods sold. Payment for merchandise is made 50% in the month of purchase and 50% in the month following the purchase.

? Miscellaneous monthly expenses to be paid in cash are $12,000.

? Monthly depreciation is $11,500.

? Equipment purchases of $22,000 in April and $135,000 in May were paid in cash.

? Dividends of $50,000 were declared and paid in June.

? Any borrowings must be in $1,000 increments at 12% annual interest. Assume interest accrues at the beginning of the month and is paid at the end of the month. The company must maintain a minimum cash balance of $20,000.

? Ignore income taxes

. ? All accounts receivable from March will be collected in April and all accounts payable in March will be paid in April.

? The balance sheet as of March 31st.

Right Price Plant Company Balance Sheet 3/31/14

Assets: Cash Net Accounts Receivable Merchandise Inventory Property Plant and Equipment Less: accumulated depreciation Total Assets

Liabilities & Stockholder’s Equity: Accounts Payable Common Stock Retained Earnings Total Liabilities and Stockholder’s Equity

$1,500,000 $688,000

$27,000 $90,000 $156,000

$812,000 $1,085,000

$205,000 $300,000 $580,000 $1,085,000

Prepare the following budgets for each month April, May, June and Total for the quarter in good form in excel with proper use of formulas and formatting:

a. Prepare a Schedule of Expected Cash Collections ? What is the budgeted accounts receivable at June 30th?

b. Prepare a Merchandise Purchases Budget and a Schedule of Expected Cash Disbursements ? What is the budgeted accounts payable at June 30th?

c. Prepare a Cash Budget ? How much does the company need to borrow for the quarter? ? How much can the company repay for the quarter?

d. Prepare a Budgeted Income Statement

Extra credit 5 points Prepare a Budgeted Balance Sheet

Please highlight your answers.

Formatting is as important as the correct answer for this project. Your spreadsheet should be in good form, presented and printed clearly, and labeled appropriately. Your print out should be in report format and clearly presented. Each budget should not span more than one page, i.e. pay attention to what prints out on each page.

In addition to your spreadsheet, you MUST print out your spreadsheet formulas. Use “control r-d” to toggle over to view the formulas and then print again. Projects submitted without formulas will only receive half credit.

Check figures: cash collections at end of quarter = $775,500 Deficiency in cash at the end of the quarter = ($95,740)

You will need to borrow and repay during the quarter.

(Note that due to varying assumptions made in the budgeting process, your numbers may be slightly different than mine. If your balance sheet balances, then your budget works.)

DO NOT PLUG ANYTHING!