right answer please

C5 ft Master Budget Assignment B C D E Metal Parts Inc. Master Budget St St: Metal Parts Inc. is preparing its monthly budget for 2019. The following estimates and information are available. . Each unit requires 5 hours of direct labour and 16 pounds of metal to make. They have 5920 pounds of metal already in inventory. To prepare for supply disruptions of metal, the company likes to keep monthly ending inventory of metal high enough to meet the next two month's production requirements. In practice, their attempts at this might not always work out. Direct labour costs $23 per hour. Accounts payable are paid 60% in the month of purchase and 40% in the following month. Each pound of metal costs $10.50 In December 2018, 6000 pounds of metal were purchased and 1,050 hours of Direct Labour worked Sales are estimated as follows: 3 Data Input Area 4 Selling Price per Unit 5 DLH per Unit 6 % of next months sales for FG Inventory 7 Pounds of Metal per Unit 8 Cost of one pound of Metal 9 Cost per DLR 10 Variable MOH 11 MOH - Fixed 12 Variable SIA per unit 13 Fixed SIA per month 14 Minimum Cash Balance 15 Interest Rate on Line of Credit 16 Monthly Depreciation 17 18 19 20 21 22 23 24 Accounts Payable payment pattern 25 Wages payable payment pattern 26 Customer collections payment pattern 27 28 29 Sales 30 31 Sales (Units) 32 Selling Price per Unit 33 Sales 34 35 Production Budget 36 37 Sales (Units) 20. Docirad Endling Inunnian Budget Sheet2 Month of Month Amount November 2018 actual $270,000 December actual $240,000 Jan 2019 $330,000 Feb $180,000 Mar $510,000 Apr $600,000 May $1,110,000 June $1,770,000 $1,830,000 $1,110,000 Sept $1,020,000 Oct $660,000 Nov $240,000 Dec $180,000 Jan 2020 $450,000 Next month Month After July Aug S $ Nov 270.000 S 5 S 1,350,000 Dec 240.000 5 1 200.000 S Jan 330.000 5 S 1 650 000 age of a 680 words 0 100% Enter > E H CS B D E F Oct Nov Dec Jan 2020 Feb $660,000 $240,000 $180,000 $450,000 $480,000 $600,000 Mar $ $ Nov 270,000 $ 5 $ 1,350,000 Dec 240,000 5 1,200,000 $ Jan 330,000 5 1,650,000 Metal Parts Inc. keeps a minimum amount of the following: Cash Finished units A minimum of $2,000,000 Ending inventory equal to 25% of next month's sales requirements 28 29 Sales 30 31 Sales (Units) 32 Selling Price per Unit 33 Sales 34 35 Production Budget 36 37 Sales (Units) 38 Desired Ending Inventory 39 40 Less: Beginning Inventory 41 Production Requirements 42 43 44 Schedule of Cash Collections 45 46 Month of Sale 47 Prior Month 48 Two Months prior 49 Total Collections 50 Inventory of finished units from December 2018 is equal to the targeted inventory policy. All sales are on account. Jan 51 AC436 Master Budget Assignment 10% of grade Dec Jan Selling and Administration expenses are $8 per unit and $40,000 per month. There is no depreciation included in this figure, Dividends of $500,000 will be paid in December. Customer collections are received 45% in the month of sale, 40% in the next month, and 13% in the second month following the sale. No further collections are received. The company uses the absorption method of costing finished goods inventory and cost of goods sold. The allocate manufacturing overhead on the basis of direct labour hours. Where these instructions are not specific, use the same approach as the in-class example. 52 53 54 DM Requirements 55 56 Production Requirements (Units) 57 58 Pounds Required 59 Add desired Ending Inventory 60 Total Required 61 Less Beginning 62 DM to be purchased 63 64 crankte hantADRA C5 fr B D E F Where these instructions are not specific, use the same approach as the in-class example. Due to payroll processing times, hall of the wages earned by workers is paid in the month of production, and the other half is paid in the following month. Metal Parts Inc. has a very flexible work force that allows them to scale up or down without cost each month. The selling price per unit is $1,500 Overhead is $500,000 per month plus $2 per direct labour hour. Overhead costs are all paid in the month they occur, and there is $100,000 per month in depreciation included. The balance sheet as at December 31, 2018 is below: . . Dec Jan Balance Sheet Dec. 2018 Cash Accounts Rec RM Inventory FG Inventory Land Equipment Acc. Dep'n Total Assets 2,000,000 162,300 62,160 67,839.14 4,000,000 6,000,000 2,240,000 10,052,299 A 69 Total Cost of DM 70 71 Payments in month of purchase 72 Month after month of purchase 73 Total Payments on DM 74 75 76 77 78 DL Requirements 79 80 Production requirements (units) 81 DLH per unit 82 DLH required 83 84 Cash Payments budget for DL 85 86 DLH Required 87 Cost per DLH 88 Total Cost 89 90 Amount paid in month earned 91 Amount paid in next month 92 Total Cash disbursement for DL 93 94 95 96 MOH Budget 97 98 DLH 99 Var MOH per DLH 100 Varialbe MOH 101 102 Fixed MOH 103 Total MOH Expense 104 105 Less Denreciation Accounts Pay Wages Pay Bank Line of Credit Total Liabilities 25,200 12,075 Jan 37,275 Common Shares Retained Earnings Total Equity 5,000,000 5,015,024 10,015,024 Total Liab & Equity 10,052,299 PROTECTED VIEW Be careful files from the Internet can contain viruses. Unless you need to edit Enable Editing it's safer to stay in Protected View. Retained Earnings 5,015,024 Total Equity 10,015,024 C5 fr B D Total Liab & Equity 10,052,299 $750,000 of the equipment will be sold in March. This will have no effect on monthly depreciation because it was no longer a depreciable asset. AC436 Master Budget Assignment 10% of grade Quantity Price Total Another $400,000 of equipment will be purchased in June and will not be in service until 2020. The company has a line of credit at the bank for up to $10 million at an interest rate of 4%. Borrowing happens on the first day of the month and repayments are made, when available, at the end of each month. Interest is calculated and deducted from the bank account at the end of each month based on the loan balance outstanding. Since the interest deducted from the bank account, it is ok to allow the ending bank balance to be below the minimum by the amount of interest expense in the month. For example, if interest expense on the line of credit is $5,000 in a month, the ending balance can be left as $1,995,000 after the interest is deducted. That is close enough to the minimum for our purposes. 137 Total Disbursements 138 139 Balance before financing 140 Borrowing 141 Repayments 142 Loan Balance Total 143 Interest 144 Ending Balance 145 146 147 148 149 PDOH 150 151 152 153 154 Cost per Unit of production 155 DM 156 DL 157 MOH 158 Cost per Unit 159 160 161 162 163 Income Statement 164 165 Sales 166 Cogs 167 Gross Margin 168 169 S&A 170 Bad Debt 171 Operating Profit 172 173 Interest Exp 174 Not.locome Budget Sheet2 2019 Required: Using the Excel template provided, complete the master budget including cash projections, balance sheet and income statement. A template has been provided for you to work with. All formulas should Master Budget Assignment Metal Parts Inc. is preparing its monthly budget for 2019. The following estimates and information are available. 0 Each unit requires 5 hours of direct labour and 16 pounds of metal to make. They have 5920 pounds of metal already in inventory. To prepare for supply disruptions of metal, the company likes to keep monthly ending inventory of metal high enough to meet the next two month's production requirements. In practice, their attempts at this might not always work out. Direct labour costs $23 per hour. Accounts payable are paid 60% in the month of purchase and 40% in the following month. Each pound of metal costs $10.5 In December 2018, 6000 pounds of metal were purchased and 1,050 hours of Direct Labour worked. Sales are estimated as follows: 0 e . Month November 2018 actual December actual Jan 2019 Feb Mar Apr May June Jul Aug Sept Oct Nov Dec Jan 2020 Feb Amount $270,000 $240,000 $330,000 $180,000 $510,000 $600,000 $1,110,000 $1,770,000 $1,830,000 $1,110,000 $1,020,000 $660,000 $240,000 $180,000 $450,000 $480,000 In December 2018, 6000 pounds of metal were purchased and 1,050 hours of Direct Labour worked. . Sales are estimated as follows: Month Amount November 2018 actual $270,000 December actual $240,000 Jan 2019 $330,000 Feb $180,000 Mar $510,000 Apr $600,000 May $1,110,000 June $1,770,000 July $1,830,000 Aug $1,110,000 Sept $1,020,000 Oct $660,000 Nov $240,000 Dec $180,000 Jan 2020 $450,000 Feb $480,000 Mar $600,000 . Metal Parts Inc. keeps a minimum amount of the following: Cash Finished units A minimum of $2,000,000 Ending inventory equal to 25% of next month's sales requirements . Inventory of finished units from December 2018 is equal to the targeted inventory policy. All sales are on account. . Selling and Administration expenses are $8 per unit and $40,000 per month. There is no depreciation included in this figure. Dividends of $500,000 will be paid in December Customer collections are received 45% in the month of sale, 40% in the next month, and 13% in the second month following the sale. No further collections are received. The company uses the absorption method of costing finished goods inventory and cost of goods sold. The allocate manufacturing overhead on the basis of direct labour hours. Where these instructions are not specific, use the same approach as the in-class example. Due to payroll processing times, half of the wages earned by workers is paid in the month of production, and the other half is paid in the following month. Metal Parts Inc. has a very flexible work force that allows them to scale up or down without cost each month. The selling price per unit is $1,500 Overhead is $500,000 per month plus $2 per direct labour hour. Overhead costs are all paid in the month they occur, and there is $100,000 per month in depreciation included. The balance sheet as at December 31, 2018 is below: . . Balance Sheet Dec. 2018 Cash Accounts Rec RM Inventory FG Inventory Land Equipment Acc Dep'n Total Assets 2,000,000 162,300 62,160 67.839.14 4,000,000 6,000,000 2.240,000 10,052 299 Accounts Pay Wages Pay Bank Line of Credit Total Liabilities 25,200 12,075 37,275 580 words the month they occur, and there is $100,000 per month in depreciation included. The balance sheet as at December 31, 2018 is below: Balance Sheet Dec. 2018 Cash Accounts Rec RM Inventory FG Inventory Land Equipment Acc. Dep'n Total Assets 2,000,000 162,300 62,160 67,839.14 4,000,000 6,000,000 2,240.000 10,052,299 Accounts Pay Wages Pay Bank Line of Credit Total Liabilities 25,200 12,075 37,275 Common Shares Retained Earnings Total Equity 5,000,000 5,015.024 10,015.024 Total Liab & Equity 10,052.299 $750,000 of the equipment will be sold in March. This will have no effect on monthly depreciation because it was no longer a depreciable asset. Another $400,000 of equipment will be purchased in June and will not be in service until 2020. The company has a line of credit at the bank for up to $10 million at an interest rate of 4%. Borrowing happens on the first day of the month and repayments are made, when available, at the end of each month. Interest is calculated and deducted from the bank account at the end of each month based on the loan balance outstanding. Since the interest deducted from the bank account, it is ok to allow the ending bank balance to be below the minimum by the amount of interest expense in the month. For example, if interest expense on the line of credit is $5,000 in a month, the ending balance can be left as $1,995,000 after the interest is deducted. That is close enough to the minimum for our purposes. Required: Using the Excel template provided, complete the master budget including cash projections, balance sheet and income statement. A template has been provided for you to work with. All formulas should be linked to the data input area or other cells. Direct input of numbers in the body of the document should be avoided. Note: You must use the Excel file provided