SHADES STORE LIMITED You were recently promoted to assistant controller at Shades Store Limited. Your employer specializes in selling pink trendy shades. The first task you have been assigned in your new role is to prepare the master budget for the quarter ended September 30, 2020 You have assembled the following information: The shades sell for $130 each. Recent and budgeted sales (in units) are as follows: April (actual) May (actual) 1050 June (actual) 980 July 1100 August September October November 1000 1050 1062 1120 1300 1440 December Inventories of finished goods on hand at the end of each month are to be equal to 40% of the following months budgeted sales. As of June 30 the company had 500 shades in inventory. Each pair of shades requires 9 ounces of pink plastic, which the company purchases for $1.50 per ounce. It is company policy to keep enough pink plastic to meet 80% of the next month's production needs. As of May 31" Shades Store had 7,834 ounces of pink plastic on hand. Purchases of raw materials are paid for as follows: 30% in the month of purchase and the remaining 70% in the following month. All sales are on credit, with no discount, and payable within 15 days. The company has found, however, that only 45% of a month's sales are collected by month-end. An additional 50% is collected in the month following, and the remaining 5% is collected in the second month following. Bad debts have been negligible. Each pair of shades requires 3 hours to manufacture Emplees who make the shades are paid $20 per hour and never work overtime (ie 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 $4 per pair of shades in production and the fixed component is $15,000 per month (this amount includes depreciation of $2,000 per month). Direct labour hours is used as an allocation base for assigning manufacturing overhead to units produced Shades Store's monthly operating expenses are given below: Shades Store's monthly operating expenses are given below: $2 per shades sold Variable: Royalties paid Fixed: Wages & salaries Utilities Insurance Depreciation Miscellaneous $12,000 4,000 2,500 1,000 6,500 All operating expenses are paid during the month in cash. Due to the planned introduction of a new model of shades "The Glittery Bonanza" the company is expecting a big increase in sales for Christmas. To accommodate the expected increase in demand the company will be purchasing a new industrial die machine in July for $50,000 and some new processing machines in September for $5,000. The company declares a dividend of $10,000 on the last day of each quarter which is then paid in the first month of the next quarter The balance sheet at June 30th is given below: Assets Cash Accounts receivable Inventory, raw materials Inventory, finished goods Fixed assets Accumulated depreciation $ 14,000 76,895 11,016 45,870 297,000 (104,000) 193,000 Total Assets $ 340, 781 Liabilities and Shareholders' Equity Accounts payable, purchases $ 11,163 Dividends payable 10,000 Capital stock, no par 50,000 Retained earnings 269,618 Total liabilities and shareholders' equity $ 340,781 Management of Shades Store requires a minimum ending cash balance each month of $20,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. Borrowings are disbursed to the nearest $1,000. 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 the following budgets for the company for the quarter ending September 30th: a. Sales Budget and Schedule of Expected Cash Collections (8 marks) b. Production Budget (4 marks) c. Direct Materials Budget and Schedule of Expected Cash Disbursements (11marks) d. Direct Labour Budget (inclusive of expected labour disbursements) (3 marks) e Manufacturing Overhead Budget (inclusive of expected overhead disbursements) (5 marks) f. Ending Finished Goods Inventory Budget (8 marks) & Selling and Administrative Budget (6 maris) 2. Prepare the following cash schedules for the company for the quarter ending September 30th: a. Cash Budget (15 marks) 3. Prepare a Budgeted Income Statement for the quarter ending September 30th (10 marks) 4. Prepare a Budgeted Balance sheet at Septembergoth. (30 marks) All schedule and statements should be formatted professionally. Use excel to do the work. Use cell references and formulas in your schedules. Upload your excel file to the dropbox in the Project folder on Moodle prior to midnight on Tuesday June 09, 2020: Check Figures: Total cash collections for the quarter Total required production for the quarter (units) Raw materials to be purchased for the quarter (ounces) Ending cash balance Total cost of goods manufactured for the quarter Total assets $411,697 3,160 29,678 $20,365 $289,900 $396,096 (Expected cash collections) (Production) (Direct materials) (Cash budget) (Income Statement) (Balance sheet)