i also uploaded the info of last picture as it is not clear, please help me to solve the ques

Metal Parts Inc. Master Budget Student Name:

Stundet Number:

Data Input Area

Selling Price per Unit

DLH per Unit

% of next months sales for FG Inventory

Pounds of Metal per Unit

Cost of one pound of Metal

Cost per DLR

Variable MOH

MOH - Fixed

Variable S/A per unit

Fixed S/A per month

Minimum Cash Balance

Interest Rate on Line of Credit

Monthly Depreciation

Month of Next month Month After

Accounts Payable payment pattern

Wages payable payment pattern

Customer collections payment pattern

Sales

Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec 2024 Total Jan Feb Mar

Sales (Units)

Selling Price per Unit

Sales

Production Budget

Sales (Units)

Desired Ending Inventory

Less: Beginning Inventory

Production Requirements

Schedule of Cash Collections Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec 2024 Total

Month of Sale

Prior Month

Two Months prior

Total Collections

DM Requirements Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec 2024 Total

Production Requirements (Units)

Pounds Required

Add desired Ending Inventory

Total Required

Less Beginning

DM to be purchased

Cash Payments budget for RM

DM to be purchased

Cost per pound

Total Cost of DM

Payments in month of purchase

Month after month of purchase

Total Payments on DM

Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec 2024 Total

DL Requirements

Production requirements (units)

DLH per unit

DLH required

Cash Payments budget for DL

DLH Required

Cost per DLH

Total Cost

Amount paid in month earned

Amount paid in next month

Total Cash disbursement for DL

MOH Budget Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec 2024 Total

DLH

Var MOH per DLH

Varialbe MOH

Fixed MOH

Total MOH Expense

Less: Depreciation

Cash expenditure of MOH

Sales and Administration Budget Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec 2024 Total

Sales (Units)

S/A per unit sold

Variable S/A

Fixed S/A

Total S/A

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec 2024 Total

Cash Budget

Beginning Balance

Collections

Total Available

Disbursements

DM

DL

MOH

S/A

Capital

Dividends

Total Disbursements

Balance before financing

Borrowing

Repayments

Loan Balance Total

Interest

Ending Balance

PDOH

Cost per Unit of production Quantity Price Total Units Cost per Unit Total

DM

DL

MOH

Cost per Unit

Income Statement 2024 Balance Sheet December 2024 Decemer 2023 Y/Y Change

Sales Cash 2,000,000

Cogs Accounts Rec 162,300

Gross Margin RM Inventory 62,160

FG Inventory 67,839.14

S&A Land 4,000,000

Bad Debt Equipment 6,000,000

Operating Profit Acc. Dep'n -2,240,000

Total Assets 10,052,299

Interest Exp

Net Income

Accounts Pay 25,200

Wages Pay 12,075

Bank Line of Credit -

Total Liabilities 37,275

Common Shares 5,000,000

Retained Earnings 5,015,024

Total Equity 10,015,024

Total Liab & Equity 10,052,299

Master Budget Assignment Metal Parts Inc. is preparing its monthly budget for 2024. The following estimates and information are available. - They have 5,920 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 2023, 6000 pounds of metal were purchased and 1,050 hours of Direct Labour worked. - Sales are estimated as follows: - Metal Parts Inc, keeps a minimum amount of the following: Cash A minimum of $2.000.000 Finished units Ending inventory cqual to 25% of next meath's sales requinemento - Inventory of finished units from December 2023 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 declared in December. - Each unit requires 5 hours of direct labour and 16 pounds of metal to make. - 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 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. - 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, 2023 is below: - $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. - $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 2025. - 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 monthly master budget including monthly cash projections, with year-end 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. Feel free to work together, but keep your work separate and submit only your own work. Late files are not accepted for any reason. Master Budget Assignment Metal Parts Inc. is preparing its monthly budget for 2024. The following estimates and information are available. - They have 5,920 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 2023, 6000 pounds of metal were purchased and 1,050 hours of Direct Labour worked. - Sales are estimated as follows: - Metal Parts Inc, keeps a minimum amount of the following: Cash A minimum of $2.000.000 Finished units Ending inventory cqual to 25% of next meath's sales requinemento - Inventory of finished units from December 2023 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 declared in December. - Each unit requires 5 hours of direct labour and 16 pounds of metal to make. - 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 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. - 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, 2023 is below: - $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. - $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 2025. - 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 monthly master budget including monthly cash projections, with year-end 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. Feel free to work together, but keep your work separate and submit only your own work. Late files are not accepted for any reason