1 of 4 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) 1000 May (actual) 1050 June (actual) 980 July 1100 August 1050 September 1062 October 1120 November 1300 December 1440 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 30the 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 6,224 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. Employees who make the shades are paid $20 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 $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: Variable: Royalties paid $2 per shades sold Fixed: Wages & salaries $12,000 Utilities 4,000 Insurance 2,500 Depreciation 1,000 Miscellaneous 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 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) g. Selling and Administrative Budget (6 marks) 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 September 30th.(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 (Expected cash collections) 3,160 (Production) 29,678 (Direct materials) $20,215 (Cash budget) $289,900 $395,947 (Balance sheet)