MBA 7030 Managerial Accounting Computer Assignment 1 The Manthei Corporation manufactures and sells three products, Superior, Excellent, and Good In August 2021, Mantheis Budget Department gathered the following data in order to prepare budgets for 2021 2021 Projected Sales Product Units Price Superior 52,000 $330 Excellent 25,000 $250 Good 28,000 $190 Expected Target Inventories Product January 1, 2021 December 31, 2021 Superior 18,000 17,000 Excellent 20,000 26,000 Good 18,000 23,000 The following direct materials are expected to be used in the three products Amount Used Per Unit Direct Material Unit Superior Excellent Good M 1 pound 8 4 0 M 2 pound 4 4 3 Projected data for 2021 with respect to direct materials are as follows Direct Materials Expected Purchase Price Expected Inventories (January 1, 2021) Target Inventories (December 31, 2021) M 1 $16 60,000 lb 55,000 lb M 2 $9 87,000 lb 62,000 lb Projected direct manufacturing labor requirements and rates for 2021 are as follows Product Hours Per Unit Rate Per Hour Superior 4 $15 Excellent 3 $14 Good 3 $11 Manufacturing overhead is allocated at the rate of $13 per direct manufacturing labor hour Required Based on the preceding projections and budget requirements for Superior, Excellent, and Good, prepare the following budgets for Thomas for 2021 Show breakups where appropriate C ombine results for all three products to get the total for the company, when appropriate Revenues budget (in dollars) Production budget (in units) Direct materials purchases budget (in quantities) Direct materials purchases budget (in dollars) Direct manufacturing labor budget (in dollars) Direct manufacturing overhead budget (in dollars) For each product, calculate the standard budgeted cost per unit for DM, DL, MOH, and the Total Cost Budgeted finished goods inventory (in dollars) Do this by using the Total Budgeted Cost Per Unit Cost of Goods Sold budget (in dollars) Use the Total Budgeted Cost Per Unit to calculate this budget Note Budgets are flexible and fluid and may change as managers have access to additional information If the above budget is computed manually, and a budgeting change is made to a key number, the entire budget would have to be recomputed This would be a redundant and time consuming exercise Instead, if a spreadsheet template that employs simple formulas is constructed, a budgeting change can be made in a matter of seconds and the answers will be recomputed automatically This computer assignment must be completed utilizing two sheets within an Excel file The first sheet should contain the data entry section where you will enter the above data The second sheet should be the budgeting template, which will ONLY contain formulas that are linked to the data entry section from the first sheet I recommend you test your template upon completion, i e change a key number in the data entry section and see if the numbers in the template recompute correctly Please answer items 5 9 )

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Jul 10, 2024

MBA 7030 Managerial Accounting Computer Assignment 1 The Manthei Corporation manufactures and sells three products, Superior, Excellent, and Good. In August 2021, Mantheis Budget Department

MBA 7030 Managerial Accounting

Computer Assignment 1

The Manthei Corporation manufactures and sells three products, Superior, Excellent, and Good. In August 2021, Mantheis Budget Department gathered the following data in order to prepare budgets for 2021:

2021 Projected Sales

Product	Units	Price
Superior	52,000	$330
Excellent	25,000	$250
Good	28,000	$190

Expected Target Inventories

Product	January 1, 2021	December 31, 2021
Superior	18,000	17,000
Excellent	20,000	26,000
Good	18,000	23,000

The following direct materials are expected to be used in the three products:

		Amount Used Per Unit
Direct Material	Unit	Superior	Excellent	Good
M₁	pound	8	4	0
M₂	pound	4	4	3

Projected data for 2021 with respect to direct materials are as follows:

Direct Materials	Expected Purchase Price	Expected Inventories (January 1, 2021)		Target Inventories (December 31, 2021)
M₁	$16		60,000 lb	55,000 lb
M₂	$9		87,000 lb	62,000 lb

Projected direct manufacturing labor requirements and rates for 2021 are as follows:

Product	Hours Per Unit	Rate Per Hour
Superior	4	$15
Excellent	3	$14
Good	3	$11

Manufacturing overhead is allocated at the rate of $13 per direct manufacturing labor-hour.

Required: Based on the preceding projections and budget requirements for Superior, Excellent, and Good, prepare the following budgets for Thomas for 2021. Show breakups where appropriate. Combine results for all three products to get the total for the company, when appropriate.

Revenues budget (in dollars)
Production budget (in units)
Direct materials purchases budget (in quantities)
Direct materials purchases budget (in dollars)
Direct manufacturing labor budget (in dollars)
Direct manufacturing overhead budget (in dollars)
For each product, calculate the standard/budgeted cost per unit for: DM, DL, MOH, and the Total Cost.
Budgeted finished goods inventory (in dollars). Do this by using the Total Budgeted Cost Per Unit.
Cost of Goods Sold budget (in dollars). Use the Total Budgeted Cost Per Unit to calculate this budget.

Note:

Budgets are flexible and fluid and may change as managers have access to additional information. If the above budget is computed manually, and a budgeting change is made to a key number, the entire budget would have to be recomputed. This would be a redundant and time-consuming exercise. Instead, if a spreadsheet template that employs simple formulas is constructed, a budgeting change can be made in a matter of seconds and the answers will be recomputed automatically.

This computer assignment must be completed utilizing two sheets within an Excel file. The first sheet should contain the data-entry section where you will enter the above data. The second sheet should be the budgeting template, which will ONLY contain formulas that are linked to the data-entry section from the first sheet. I recommend you test your template upon completion, i.e. change a key number in the data-entry section and see if the numbers in the template recompute correctly.

Please answer items 5-9 :)