QUESTION IS IN THE ATTACHED PICS, AND HAS TO BE DONE ON EXCEL THROUGH THE EXACT FORMAT.
You are the assistant controller for Gnomes Incorporated. After the abysmal failure of their original business plan, Gnomes has found success by selling various styles of terracotta garden gnomes. You have been asked to prepare the master budget for the quarter ended June 30th, 2016. You have assembled the following information: The garden gnomes sell for $40 each. Recent and forecasted sales (in units) are as follows: January (actual) 800 February (actual) 900 March (actual) 1,800 April 2,800 May 3,500 June 4,900 3,600 August 2,200 September 1,400 July 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 March 31st the company had 1,400 statuettes in inventory. Each statuette requires two kilograms of clay, which the company purchases for $3 per kilogram. Gnomes keeps an ending inventory of clay at the end of each month equal to 60% of the next month's production needs. As of March 31st the company had 3,780 kilos of clay 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 30 minutes to cast and paint. Employees who make the statuettes are paid $12 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 statuette in production and the fixed component is $10,000 per month (this amount includes depreciation of $3,000 per month on the kilns and moulds). Direct labour hours is used as an allocation base for assigning manufacturing overhead to units produced. Gnomes' monthly operating expenses are given below: $5 per statuette sold Variable: Shipping costs Fixed: Wages & salaries Utilities Insurance expired Depreciation Miscellaneous $8,000 3,000 1,500 750 2,000 All operating expenses are paid during the month in cash, with the exception of the depreciation and insurance expenses. Management has decided to release a new line of zombie themed gnomes. To accommodate the expected increase in demand the company will be purchasing a new kiln in April for $20,000 and some new moulds in May for $10,000. Gnomes declares a dividend of $5,000 on the last day of each quarter which is then paid in the first month of the next quarter. The balance sheet at March 31st is given below: Assets Cash Accounts receivable Inventory, raw materials Inventory, finished goods Unexpired insurance Fixed assets Accumulated depreciation $ 12,000 57,600 11,340 26,020 13,500 163,500 (47,000) 116,500 Total Assets $ 236,960 Liabilities and Shareholders' Equity Accounts payable, purchases Dividends payable Capital stock, no par Retained earnings Total liabilities and shareholders' equity $ 8,430 5,000 60,000 163,530 $ 236,960 Management of Gnomes requires a minimum ending cash balance each month of $10,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 June 30th 4.Prepare a Budgeted Balance Sheet at June 30th. Instructions to student (PLEASE READ AND FOLLOW THESE INSTRUCTIONS-THANK YOU:) 1. All schedules and statements are included in the excel template, including the numbers in the Sales Budget, Production Budget, and Direct Materials Budget to get you started. BUT these formulas are just to help your understanding. You still need to review them to ensure you understand them as well as check and see if any of them need to be changed. 2. Download and save the excel template on your own computer; assign and use a unique file name. 3. Post your questions to the Budget Project forum. Also, you are encouraged to respond to any questions posted in the forum. The Instructor will monitor the forum and provide input as required. You must use the Budget Project forum to ask questions; do not send e-mails to the Instructor requesting assistance. 4. One person can submit the budget project for the group under Assignments: Budget Project. The Budget Project must be submitted by April 6, 2019, no late submissions will be accepted. Check Figures: Total cash collections for the quarter Total required production for the quarter (units) Raw materials to be purchased for the quarter (kilos) Ending cash balance Total cost of goods manufactured for the quarter Total assets $340,400 11,600 22,900 $17,610 $215,600 $370,955 (Expected cash collections) (Production) (Direct materials) (Cash budget) (Balance sheet) Master Budget Case Sales Budget Budgeted Sales in units Selling Price per unit February 900 March 1800 April 2800 40 May 3500 40 June 4900 40 Total 11200 40 40 40 Total Sales 36,000 72,000 112,000 140,000 196,000 448,000 May 3500 Production Budget Budgeted sales Desired ending inventory Total needs Less beginning inventory Required production 2450 March 1800 1400 3200 900 2300 April 2800 1750 4550 1400 3150 June 4900 1800 6700 2450 4250 Total 11200 1800 13000 5950 1750 4200 1400 11600 2300 3150 4250 11600 2 - 22 8500 Direct Materials Budget Required production Materials per unit Production needs (in kilos) Desired ending inventory Total needed Less beginning inventory Materials to be purchased 4600 3780 8380 2760 5620 6300 5040 11340 3780 7560 4200 2 8400 5100 13500 5040 8460 3480 11980 5100 6880 23200 3480 26680 3780 22900 Direct Labour Budget Required production Direct labour time/unit Total hours needed Direct labour cost/hour Total direct labour cost Quantity Cost Total Total MOH DL Hours MOH/Hr Ending Finished Goods Inventory Budget Production costs per unit: Direct material Direct labour Manufacturing overhead Total Ending inventory in units Unit product cost Ending finished goods inventory Total Selling and Administrative Budget Budgeted sales Variable selling and admin rate Variable expense Fixed selling and admin costs Wages and salaries Utilities Insurance expired Depreciation Miscellaneous Fixed expense Total selling and administrative costs Less non cash expenses Insurance expired Depreciation Total non cash expenses Cash disbursements for selling and admin Total for budgets page Gnomes Inc. Schedule of Expected Cash Collections April May June Total January February February sales March sales April sales May sales June sales Total cash collections Gnomes Inc. Schedule of Expected Cash Disbursements for Material April May June Total Cost per kilo $ 3.00 Accounts payable March 31 (50% of March purchases) April purchases May purchases June purchases Total cash disbursements for material Dividends paid Total disbursements Excess (deficiency) of receipts over disbursements Financing: Borrowings Repayments Interest Total financing Cash balance, ending Loan balance B Gnomes Inc. Schedule of Cost of Goods Manufactured For the Three Months Ended June 30 Opening raw materials inventory Add: raw materials purchases Raw materials available for use Less: Closing raw materials inventory Raw matterials used in production Direct labour Manufacturing overhead Cost of goods manufactured Gnomes Inc. Budgeted Income Statement For the Three Months Ended June 30 Sales revenue Cost of goods sold Opening inventory Cost of goods manufactured Goods available for sale Ending inventory Cost of goods sold Gross Margin Less Operating Expenses Selling and Administration Net operating income Less interest expense Net income Rock Solid Inc. Budgeted Balance Sheet As at December 31 Assets Cash Accounts receivable Inventory- Raw Materials Inventory - Finished Goods Unexpired insurance Fixed assets Accumulated depreciation Total assets Liabilities and Equity Accounts payable, purchases Dividends payable Capital stock, no par Retained earnings (see below) Total liabilities and equity Retained earnings at June 30 Balance, March 31 Add net income Total Less dividends declared Balance, June 30 Formulas Total You are the assistant controller for Gnomes Incorporated. After the abysmal failure of their original business plan, Gnomes has found success by selling various styles of terracotta garden gnomes. You have been asked to prepare the master budget for the quarter ended June 30th, 2016. You have assembled the following information: The garden gnomes sell for $40 each. Recent and forecasted sales (in units) are as follows: January (actual) 800 February (actual) 900 March (actual) 1,800 April 2,800 May 3,500 June 4,900 3,600 August 2,200 September 1,400 July 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 March 31st the company had 1,400 statuettes in inventory. Each statuette requires two kilograms of clay, which the company purchases for $3 per kilogram. Gnomes keeps an ending inventory of clay at the end of each month equal to 60% of the next month's production needs. As of March 31st the company had 3,780 kilos of clay 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 30 minutes to cast and paint. Employees who make the statuettes are paid $12 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 statuette in production and the fixed component is $10,000 per month (this amount includes depreciation of $3,000 per month on the kilns and moulds). Direct labour hours is used as an allocation base for assigning manufacturing overhead to units produced. Gnomes' monthly operating expenses are given below: $5 per statuette sold Variable: Shipping costs Fixed: Wages & salaries Utilities Insurance expired Depreciation Miscellaneous $8,000 3,000 1,500 750 2,000 All operating expenses are paid during the month in cash, with the exception of the depreciation and insurance expenses. Management has decided to release a new line of zombie themed gnomes. To accommodate the expected increase in demand the company will be purchasing a new kiln in April for $20,000 and some new moulds in May for $10,000. Gnomes declares a dividend of $5,000 on the last day of each quarter which is then paid in the first month of the next quarter. The balance sheet at March 31st is given below: Assets Cash Accounts receivable Inventory, raw materials Inventory, finished goods Unexpired insurance Fixed assets Accumulated depreciation $ 12,000 57,600 11,340 26,020 13,500 163,500 (47,000) 116,500 Total Assets $ 236,960 Liabilities and Shareholders' Equity Accounts payable, purchases Dividends payable Capital stock, no par Retained earnings Total liabilities and shareholders' equity $ 8,430 5,000 60,000 163,530 $ 236,960 Management of Gnomes requires a minimum ending cash balance each month of $10,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 June 30th 4.Prepare a Budgeted Balance Sheet at June 30th. Instructions to student (PLEASE READ AND FOLLOW THESE INSTRUCTIONS-THANK YOU:) 1. All schedules and statements are included in the excel template, including the numbers in the Sales Budget, Production Budget, and Direct Materials Budget to get you started. BUT these formulas are just to help your understanding. You still need to review them to ensure you understand them as well as check and see if any of them need to be changed. 2. Download and save the excel template on your own computer; assign and use a unique file name. 3. Post your questions to the Budget Project forum. Also, you are encouraged to respond to any questions posted in the forum. The Instructor will monitor the forum and provide input as required. You must use the Budget Project forum to ask questions; do not send e-mails to the Instructor requesting assistance. 4. One person can submit the budget project for the group under Assignments: Budget Project. The Budget Project must be submitted by April 6, 2019, no late submissions will be accepted. Check Figures: Total cash collections for the quarter Total required production for the quarter (units) Raw materials to be purchased for the quarter (kilos) Ending cash balance Total cost of goods manufactured for the quarter Total assets $340,400 11,600 22,900 $17,610 $215,600 $370,955 (Expected cash collections) (Production) (Direct materials) (Cash budget) (Balance sheet) Master Budget Case Sales Budget Budgeted Sales in units Selling Price per unit February 900 March 1800 April 2800 40 May 3500 40 June 4900 40 Total 11200 40 40 40 Total Sales 36,000 72,000 112,000 140,000 196,000 448,000 May 3500 Production Budget Budgeted sales Desired ending inventory Total needs Less beginning inventory Required production 2450 March 1800 1400 3200 900 2300 April 2800 1750 4550 1400 3150 June 4900 1800 6700 2450 4250 Total 11200 1800 13000 5950 1750 4200 1400 11600 2300 3150 4250 11600 2 - 22 8500 Direct Materials Budget Required production Materials per unit Production needs (in kilos) Desired ending inventory Total needed Less beginning inventory Materials to be purchased 4600 3780 8380 2760 5620 6300 5040 11340 3780 7560 4200 2 8400 5100 13500 5040 8460 3480 11980 5100 6880 23200 3480 26680 3780 22900 Direct Labour Budget Required production Direct labour time/unit Total hours needed Direct labour cost/hour Total direct labour cost Quantity Cost Total Total MOH DL Hours MOH/Hr Ending Finished Goods Inventory Budget Production costs per unit: Direct material Direct labour Manufacturing overhead Total Ending inventory in units Unit product cost Ending finished goods inventory Total Selling and Administrative Budget Budgeted sales Variable selling and admin rate Variable expense Fixed selling and admin costs Wages and salaries Utilities Insurance expired Depreciation Miscellaneous Fixed expense Total selling and administrative costs Less non cash expenses Insurance expired Depreciation Total non cash expenses Cash disbursements for selling and admin Total for budgets page Gnomes Inc. Schedule of Expected Cash Collections April May June Total January February February sales March sales April sales May sales June sales Total cash collections Gnomes Inc. Schedule of Expected Cash Disbursements for Material April May June Total Cost per kilo $ 3.00 Accounts payable March 31 (50% of March purchases) April purchases May purchases June purchases Total cash disbursements for material Dividends paid Total disbursements Excess (deficiency) of receipts over disbursements Financing: Borrowings Repayments Interest Total financing Cash balance, ending Loan balance B Gnomes Inc. Schedule of Cost of Goods Manufactured For the Three Months Ended June 30 Opening raw materials inventory Add: raw materials purchases Raw materials available for use Less: Closing raw materials inventory Raw matterials used in production Direct labour Manufacturing overhead Cost of goods manufactured Gnomes Inc. Budgeted Income Statement For the Three Months Ended June 30 Sales revenue Cost of goods sold Opening inventory Cost of goods manufactured Goods available for sale Ending inventory Cost of goods sold Gross Margin Less Operating Expenses Selling and Administration Net operating income Less interest expense Net income Rock Solid Inc. Budgeted Balance Sheet As at December 31 Assets Cash Accounts receivable Inventory- Raw Materials Inventory - Finished Goods Unexpired insurance Fixed assets Accumulated depreciation Total assets Liabilities and Equity Accounts payable, purchases Dividends payable Capital stock, no par Retained earnings (see below) Total liabilities and equity Retained earnings at June 30 Balance, March 31 Add net income Total Less dividends declared Balance, June 30 Formulas Total
