The XY Gadget Company manufactures a home assistance device that makes household tasks easier and more efficient. The company has only been in business for 9 months, and management has never prepared a formal business budget. You will be using the template provided to prepare a master budget for the 4th quarter ending December 31, 2023. Guidelines and Tips: 1. Do not expect information to be presented in the same order needed to prepare the schedules. Read all information carefully. 2. Use the "Data Sheet" worksheet to capture information given in the case, and any other numbers you need to input directly. Organize your data sheet so it is easily readable by others. 5 marks are allocated for this. 3. All cells in your budget (other than the Data Sheet) should use Excel calculations and/or reference information from other cells. 5 marks are allocated for this. 4. Proper formatting should be used, so the budget looks professional. (i.e. correct formatting of numbers, underlining, etc.) 5 marks are allocated for this. Marking Rubric Item Marks Available Sales & cash collection budget Production budget 6 4 DM and cash disbursements budget 18 Direct labour budget 14 Ending inventory & COGM Manufacturing overhead budget Selling, G & A budget Cash budget Income statement Balance sheet Effective use of 'Data Sheet' Use of Excel formulas (1 mark deducted for each error) Professional appearance 13 16 15 14 8 17 5 5 5 TOTALS 140 Overview The XY Gadget Company manufactures a home assistance device that makes household tasks easier and more efficient. The company has only been in business for 9 months, and management has never prepared a formal business budget. The balance sheet, based on actual results, at September 30, 2023 is provided below: XY Gadget Company Balance Sheet September 30, 2023 Assets Cash Inventories $ 100,000 Materials $1,777,152 Finished goods Income taxes recoverable 330,000 2,107,152 725,000 2,932,152 Production equipment & tools 4,700,000 Accum. depreciation - Prod Equip (325,000) 4,347,500 Furniture & fixtures 300,000 Accum. depreciation - Furn & fixt (22,500) 277,500 Building 2,000,000 Accum. depreciation - Building (75,000) 1,925,000 Total assets $9,482,152 Liabilities Line of credit - First National Bank Accounts payable Notes payable, Commercial Credit Union Total liabilities Shareholders' Equity Common shares Retained earnings Total liabilities & shareholders' equity $ 0 172,173 3,000,000 3,172,173 $6,000,000 309,979 6,309,979 $9,482,152 Product Information: The company's manufacturing function consists of three departments: cutting, assembly, and finishing. Each device requires the following direct material: 4 units of Material X @ $11.00 per unit 2 units of Material Y @ $16.00 per unit XY pays shipping costs for Material X of $1.00 per unit. Material Y is shipped at no charge. All material purchased are paid for within the same quarter they are purchased The cutting and assembly departments employs hourly workers who complete the required direct labour. Each department has the following configuration: # of production machines # workers at each machine Maximum workers employed per shift Hours per shift (8 hours/day x 22 days/month x 3 months/quarter) Maximum units made per hour 30 5 150 528 1.8 Workers in each department are paid $8 per hour for the first shift. XY is able to run a second shift if needed. Workers on the 2nd shift are paid the normal hourly wage PLUS a 20% shift premium. No new materials are added in the finishing department. All labour in the finishing department is considered indirect. XY completes all production each day (there is no WIP inventory). Material X can sometimes be difficult to get on a timely basis. There has never been Jany difficulties getting as much Material Y as needed, very quickly. Sales have been increasing each quarter. XY hopes to increase their ending materials inventory at December 31 to: Material X Material Y 153,000 5,000 units units Beginning inventory consists of 148,096 units of Material X. There was no Material Y inventory on hand at the beginning of the quarter. There were 2,000 units of Finished Goods on hand at the beginning of the quarter. XY plans to have the same number of Finished Goods on hand at the end of the quarter. Manufacturing Overhead: Fixed manufacturing overhead (MOH) for Quarter 3 was $1,889,574, and has been applied based on expected production of 55,000 per quarter. Fixed MOH is expected to be the same in Quarter 4. Total fixed MOH is comprised of the following: Indirect labour cutting dept. Indirect labour-assembly dept. Indirect labour - finishing dept. $60,000 100,000 950,000 Utilities 10,500 Production planning & control 120,000 Purchasing & receiving 447,074 Factory insurance 12,000 Depreciation factory equipment 117,500 Depreciation building 20,000 Factory supplies 10,000 Factory workers training 42,500 $1,889,574 Each finished unit requires one hour of machine time. Variable manufacturing overhead is applied based on hours of machine time as follow: Utilities Repairs & maintenance Supplies Material spoilage $0.75 2.00 0.75 2.20 $5.70 Marketing/sales information: As mentioned above, demand has been increasing each quarter. XY expects they will be able to sell 57,024 finished units in Quarter 4. XY sells finished units for $300 each. All sales are cash sales, and no discounts are Joffered. Due to a poor compensation plan, all salespeople employed by XY quit at the end of the third quarter. To meet the sales demand for the fourth quarter, XY will need to hire 1,000 salespeople immediately. It will cost XY $270 to train each new salesperson. This will be treated as a fixed selling expense for quarter 4. The new compensation plan will pay salespeople a salary of $3,000 per quarter plus 10% of sales. In order to meet the sales target, XY plans to spend $800,000 in advertising for the upcoming quarter. XY sells products in several Provinces. Fixed selling costs, in addition to those described above include: Provincial Sales Office Rent Provincial Offices Operating Expenses Head Office Sales Expenses $45,000 180,000 73,175 XY will also incur some variable selling expenses. Salespeople are expected to make a total of 235,000 sales calls in the 4th quarter, and the travel cost is estimated at $1.65 per sales call made. Also, packaging costs are expected to be $2.00 per unit sold. Cash information: VK management requires a minimum of $100,000 cash on hand at the end of the quarter. They have access to a $1,000,000 Line of Credit at First National Bank. Interest rates are negligible and therefore will be ignored for budgeting purposes. Any accounts payable from the previous quarter should be paid in full. Other information: XY has administrative costs as follows: Fixed: Executive salaries $480,000 Secretarial & clerical 180,000 Supplies 15,000 Depreciation-building 5,000 Depreciation-furniture & fixtures 7,500 Variable: Supplies Travel XY's current income tax rate is 40%. $1.00 per unit sold $3.15 per unit sold