50.1K

Verified Solution

Link Copied!

Master Budget Assignment 10% of grade AC436 Master Budget Assignment Metal Parts Inc. is preparing its monthly budget for 2019. The following estimates and information are available. D.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 Amount November 2018 actual $270,000 240,000 Month December actual Jan 2019 Feb Mar Apr May June Ju Aug Sept Oct Nov Dec Jan 2020 Feb $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 $600,000 Mar Metal Parts Inc. keeps a minimum amount of the following: Cash A minimum of $2,000,000 Ending inventory equal to 25% of next month's sales requirements -nished units Inventory of finished units from December 2018 is equal to the targeted inventory policy All sales are on account. AC436 Master Budget Assignment 10% of grade 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 Common Shares Retained Earnings Total Equity 37,275 5,000,000 5,015,024 10,015,024 Total Liab& Equity $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 Another $400,000 of equipment will be purchased in June and will not be in service until 2020 available, at the end of each month Interest is calculated and deducted from the bank account Since the interest deducted from the bank account, it is ok to allow the ending bank balance to 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 as soon as cash is at the end of each month based on the loan balance outstanding 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. Al 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. Submit file to dropbox by 11:59 February 6th. Feel free to work together, but keep your work separate and submit only your own work. Home Page Layout ormulas Data Review nser Metal Parts Inc. MasBudget Selling Price per Unt 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 o Variable MOH per unit 1 MOH-Fixed per month 2 Variable S/A per unit 13 Fixed S/A per month 4 Minimum Cash Balance 5 Interest Rate on Line of Credit 6 Monthly Depreciation 18 19 21 23 24 Accounts Payable payment pattern 25 Wages payable payment pattem 26 Customer Month of Next month Month After 29 Sales Nov Dec Jan 31 Sales (Units) 2 Selling Price per Unit 33 Sales 34 35 Production Budget 7 Sales (Units) 38 Desired Ending Inventory 40 Less: Beginning Inventory 44 Schedule of Cash Collections Nov Dec 46 Month of Salo 47 Prior Month es Two Months prior Sheet1 Sheet2+ Excel File Edit View Insert Format Tools Data Window Help Home InsertPage Layout Formulas Data Review View Developer di Man 54 DM Requirements 56 Production Requirements (Units) 58 Pounds Required 59 Add desired Ending Inventory 60 Total Required 61 Less Beginning 62 DM to be purchased 65 Cash Payments budget for RM 67 68 69 DM to be purchased Cost per pound Total Cost of DM 71 Payments in month of purchase 72 | Morin after mont, of purchase 73 Total Payments on DM 78 DL Requirements 80 Production requirements (units) 1 DLH per unit 82 DLH required 84 Cash Payments budget for DL B6 DLH Required 87 Cost per DLH B8 Total Cost 90 91 Amount paid in month earned Amount paid in next month 92 Total Cash disbursement for DL MOH Budget DLH Var MOH per DLH Sheett Sheet2+ Excel File Insert Format Tools Data Window Help Insert 106 Cash expenditure of MOH 113 S/A per unit sold Enancing 142 Loan Balance Total 4PSheet1 Sheet2+ MacBook Excel File Edit View Format Tools Data Window Help Home Insert Page Layout Formulas Data Review View eloper B4 49 PDOH 151 152 154 Cost per Unit of production 155 DM 56 DL 157 MOH 158 Cost per Unit 159 Total Finished Goods COGS 161 162 163 Income Statement L64 65 Sales 166 Cogs 167 Gross Margin 2019 169 S&A 170 Bad Debt 171 Operating Profit 172 173 Interest Exp 174 Net Income 175 178 4Sheet1 Sheet2+ 5 Maste t Template () Balance Sheet Dec. 2019 Dec. 2018YY Change 2000,0002,000,000 162.300 Accounts Rec RM Inventory FG Inventory 82,160 67,839.14 62,160 67,839.14 . ,000,000 4,000,000 6,000,000 6,000,000 2.240,000 2240,000 10 052 299 Acc. Dopn Total Assets 10,052 25,200 12,075 Accounts Pay Wages Pay Bank Line of Credt Total Liabilities 12.075 37,275 37.275 Common Shares Retained Eamings Total Equity 000,000 5,000,000 5.015,0245.015.024 10,015,024-10,015,024 Total Liab &Equity 2,29910.052,299 Master Budget Assignment 10% of grade AC436 Master Budget Assignment Metal Parts Inc. is preparing its monthly budget for 2019. The following estimates and information are available. D.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 Amount November 2018 actual $270,000 240,000 Month December actual Jan 2019 Feb Mar Apr May June Ju Aug Sept Oct Nov Dec Jan 2020 Feb $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 $600,000 Mar Metal Parts Inc. keeps a minimum amount of the following: Cash A minimum of $2,000,000 Ending inventory equal to 25% of next month's sales requirements -nished units Inventory of finished units from December 2018 is equal to the targeted inventory policy All sales are on account. AC436 Master Budget Assignment 10% of grade 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 Common Shares Retained Earnings Total Equity 37,275 5,000,000 5,015,024 10,015,024 Total Liab& Equity $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 Another $400,000 of equipment will be purchased in June and will not be in service until 2020 available, at the end of each month Interest is calculated and deducted from the bank account Since the interest deducted from the bank account, it is ok to allow the ending bank balance to 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 as soon as cash is at the end of each month based on the loan balance outstanding 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. Al 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. Submit file to dropbox by 11:59 February 6th. Feel free to work together, but keep your work separate and submit only your own work. Home Page Layout ormulas Data Review nser Metal Parts Inc. MasBudget Selling Price per Unt 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 o Variable MOH per unit 1 MOH-Fixed per month 2 Variable S/A per unit 13 Fixed S/A per month 4 Minimum Cash Balance 5 Interest Rate on Line of Credit 6 Monthly Depreciation 18 19 21 23 24 Accounts Payable payment pattern 25 Wages payable payment pattem 26 Customer Month of Next month Month After 29 Sales Nov Dec Jan 31 Sales (Units) 2 Selling Price per Unit 33 Sales 34 35 Production Budget 7 Sales (Units) 38 Desired Ending Inventory 40 Less: Beginning Inventory 44 Schedule of Cash Collections Nov Dec 46 Month of Salo 47 Prior Month es Two Months prior Sheet1 Sheet2+ Excel File Edit View Insert Format Tools Data Window Help Home InsertPage Layout Formulas Data Review View Developer di Man 54 DM Requirements 56 Production Requirements (Units) 58 Pounds Required 59 Add desired Ending Inventory 60 Total Required 61 Less Beginning 62 DM to be purchased 65 Cash Payments budget for RM 67 68 69 DM to be purchased Cost per pound Total Cost of DM 71 Payments in month of purchase 72 | Morin after mont, of purchase 73 Total Payments on DM 78 DL Requirements 80 Production requirements (units) 1 DLH per unit 82 DLH required 84 Cash Payments budget for DL B6 DLH Required 87 Cost per DLH B8 Total Cost 90 91 Amount paid in month earned Amount paid in next month 92 Total Cash disbursement for DL MOH Budget DLH Var MOH per DLH Sheett Sheet2+ Excel File Insert Format Tools Data Window Help Insert 106 Cash expenditure of MOH 113 S/A per unit sold Enancing 142 Loan Balance Total 4PSheet1 Sheet2+ MacBook Excel File Edit View Format Tools Data Window Help Home Insert Page Layout Formulas Data Review View eloper B4 49 PDOH 151 152 154 Cost per Unit of production 155 DM 56 DL 157 MOH 158 Cost per Unit 159 Total Finished Goods COGS 161 162 163 Income Statement L64 65 Sales 166 Cogs 167 Gross Margin 2019 169 S&A 170 Bad Debt 171 Operating Profit 172 173 Interest Exp 174 Net Income 175 178 4Sheet1 Sheet2+ 5 Maste t Template () Balance Sheet Dec. 2019 Dec. 2018YY Change 2000,0002,000,000 162.300 Accounts Rec RM Inventory FG Inventory 82,160 67,839.14 62,160 67,839.14 . ,000,000 4,000,000 6,000,000 6,000,000 2.240,000 2240,000 10 052 299 Acc. Dopn Total Assets 10,052 25,200 12,075 Accounts Pay Wages Pay Bank Line of Credt Total Liabilities 12.075 37,275 37.275 Common Shares Retained Eamings Total Equity 000,000 5,000,000 5.015,0245.015.024 10,015,024-10,015,024 Total Liab &Equity 2,29910.052,299

Answer & Explanation Solved by verified expert

See Answer