Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Can you explain by excel, please? Thank you so much Metal Parts Inc. is preparing its monthly budget for 2019. The following estimates and information
Can you explain by excel, please? Thank you so much
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 S23 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: Month Amount November 2015 actual S270,000 December actual 5940.000 Jan 2019 $130,000 Feb S150.000 Mar 5510.000 Apr S600.000 May 51.110,000 June 51,770,000 July $1.830,000 Aug 51.110,000 Sepe 51.020,000 Oct $660.000 Nov $240.000 Dee S180.000 Jan 2020 $450,000 Feb $450,000 Mar 5600,000 Metal Parts Inc. keeps a minimum amount of the following: Cash A minimum of 52,000,000 Finished units Ending inventory equal to 35 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 S8 per unit and $40,000 per month. There is no depreciation included in this figure. Dividends of S500,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 S2 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. Depin Total Assets 2,000,000 162 300 62.160 67.839.10 4.000.000 6,000,000 2.240 000 10.052 290 25200 12.075 Accounts Pay Wages Pay Bank Line of Credit Total Liabilities 37 275 Common Shares Retained Earnings Total Equity 5.000.000 5,015.024 10.015024 Total Liab & Equity 10.05229 $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: . very fle 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 2.000.000 Accounts Rec 162.300 RM Inventory 62.160 FG Inventory 67 839 14 Land 4.000.000 Equipment 6.000.000 Aco. Depin Total Assets 10.052 299 2240 000 25.200 12.075 Accounts Pay Wages Pay Bank Line of Credit Total Liabilities Common Shares Retained Eamingo Total Equity 37.275 5.000.000 5015024 10.015.024 10,052299 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. 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 SIO 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 aynide Note: You must use the Firsel file nyisd 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 S23 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: Month Amount November 2015 actual S270,000 December actual 5940.000 Jan 2019 $130,000 Feb S150.000 Mar 5510.000 Apr S600.000 May 51.110,000 June 51,770,000 July $1.830,000 Aug 51.110,000 Sepe 51.020,000 Oct $660.000 Nov $240.000 Dee S180.000 Jan 2020 $450,000 Feb $450,000 Mar 5600,000 Metal Parts Inc. keeps a minimum amount of the following: Cash A minimum of 52,000,000 Finished units Ending inventory equal to 35 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 S8 per unit and $40,000 per month. There is no depreciation included in this figure. Dividends of S500,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 S2 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. Depin Total Assets 2,000,000 162 300 62.160 67.839.10 4.000.000 6,000,000 2.240 000 10.052 290 25200 12.075 Accounts Pay Wages Pay Bank Line of Credit Total Liabilities 37 275 Common Shares Retained Earnings Total Equity 5.000.000 5,015.024 10.015024 Total Liab & Equity 10.05229 $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: . very fle 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 2.000.000 Accounts Rec 162.300 RM Inventory 62.160 FG Inventory 67 839 14 Land 4.000.000 Equipment 6.000.000 Aco. Depin Total Assets 10.052 299 2240 000 25.200 12.075 Accounts Pay Wages Pay Bank Line of Credit Total Liabilities Common Shares Retained Eamingo Total Equity 37.275 5.000.000 5015024 10.015.024 10,052299 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. 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 SIO 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 aynide Note: You must use the Firsel file nyisd Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started