Accounting

Rock Solid Inc.
Two months ago you were hired by Rock Solid Inc. as an assistant controller. Rock Solid Inc. produces a single product, a six inch tall bronze statuette of Dwayne “The Rock” Johnson. Your role so far has consisted primarily of oversight of the Accounts Receivable department and uncollectible accounts. Early this morning the controller Dave informed you that you would be responsible for preparing the master budget for the quarter ended December 31, 2015. You have assembled the following information:
The statuettes sell for $240 each. Recent and forecasted sales (in units) are as follows:
July (actual) 1,400
August (actual) 1,300
September (actual) 1,200
October 800
November 2,200
December 4,700
January 1,800
February 1,600
March 1,300

Inventories of finished goods on hand at the end of each month are to be equal to 50% of the following months budgeted sales. As of September 30th the company had 400 statuettes in inventory.
Each statuette requires 50 ounces of bronze, which the company purchases for $.25 per ounce. Rock Solid keeps an ending inventory of bronze at the end of each month equal to 75% of the next month’s production needs. As of September 30th the company had 56,250 ounces of bronze on hand.
Purchases of raw materials are paid for as follows: 50% in the month of purchase and the remaining 50% in the following month. All sales are on credit, with no discount, and payable within 15 days. The company has found, however, that only 30% of a month’s sales are collected by month-end. An additional 50% is collected in the month following, and the remaining 20% is collected in the second month following. Bad debts have been negligible.
Each statuette requires 3 hours to mould and polish. Employees who make the statuettes are paid $15 per hour and never work overtime (i.e. the company has enough casual workers that they can call in if additional work is required).
Manufacturing overhead includes all the costs of production other than direct materials and direct labour. The variable component is $10 per statuette in production and the fixed component is $20,000 per month (this amount includes depreciation of $5,000 per month on the furnaces, moulds and polishing equipment). Direct labour hours is used as an allocation base for assigning manufacturing overhead to units produced.

Rock Solid’s monthly operating expenses are given below:
Variable:
Royalties paid to The Rock $15 per statuette sold
Fixed:
Wages & salaries $12,000
Utilities 5,000
Insurance expired 1,500
Depreciation 750
Miscellaneous 2,000

All operating expenses are paid during the month in cash, with the exception of the depreciation and insurance expenses. Due to the popularity of The Rock’s movies the company is planning on producing different styles of statuettes with movie tie-ins. To accommodate the expected increase in demand the company will be purchasing a new blast furnace in October for $110,000 and some new moulds in November for $40,000. Rock Solid declares a dividend of $20,000 on the last day of each quarter which is then paid in the first month of the next quarter.

The balance sheet at September 30th is given below:

Assets
Cash $ 14,000
Accounts receivable 264,000
Inventory, raw materials 14,063
Inventory, finished goods 29,928
Unexpired insurance 13,500
Fixed assets 263,400
Accumulated depreciation (85,000) 178,400

Total Assets $ 513,891

Liabilities and Shareholders’ Equity
Accounts payable, purchases $ 8,594
Dividends payable 20,000
Capital stock, no par 300,000
Retained earnings 185,297
Total liabilities and shareholders’ equity $ 513,891

Management of Rock Solid requires a minimum ending cash balance each month of $14,000. The company can borrow money from its bank at 12% annual interest. All borrowing must be done at the beginning of a month, and repayments must be made at the end of a month. Repayments of principal must be in round $1,000 amounts. Borrowing is also in round $1,000 amounts. Interest is computed and paid at the end of each quarter on all loans outstanding during the quarter. Round all interest payments to the nearest whole dollar. Compute interest using whole months. The company wishes to use any excess cash to pay loans off as rapidly as possible.

REQUIRED:
1. Prepare a Sales Budget; Production Budget, Direct Materials Budget; Direct Labour Budget; Manufacturing Overhead Budget; and Sales and Administration Budget.
2. Prepare Schedules of Expected Cash Collections and Expected Cash Disbursements for Material as well as a Cash Budget.
3. Prepare a Budgeted Income Statement for the quarter ending December 31st
4. Prepare a Budgeted Balance Sheet at December 31st.

All schedule and statements should be formatted professionally.

Use excel to do the work. Use cell references and formulas in your schedules.

If you’re having difficulty you can always email me a copy of your excel file and I’ll point you in the right direction.

Upload your excel file to the dropbox in the Project folder on Moodle prior to midnight on Friday November 6th.

Check Figures:

Total cash collections for the quarter $1,216,800 (Expected cash collections)
Total required production for the quarter (units) 8,200 (Production)
Raw materials to be purchased for the quarter (ounces) 417,500 (Direct materials)
Ending cash balance $291,238 (Cash budget)
Total cost of goods manufactured for the quarter $613,501
Total assets $1,589,861 (Balance sheet)

TAKE ADVANTAGE OF OUR PROMOTIONAL DISCOUNT DISPLAYED ON THE WEBSITE AND GET A DISCOUNT FOR YOUR PAPER NOW!