Reference no: EM131627035
Assessment task
Assignment Question - Marshell Ltd produces a subassembly used in the production of forklifts. The assembly is sold to engine manufacturers and forklift maintenance facilities. The company is completing its fifth year of operations and is preparing to build its master budget for the coming year (2018). The master budget will be based on the following information:
(a) The finished goods inventory was 64 000 units at 31 December 2017, each costing $332.12. The desired ending inventory for each month is 80% of the next month's sales.
(b) The data on materials used are as follows:
|
Direct material
|
Per-unit usage
|
Unit cost ($)
|
|
Metal
|
20 kg
|
16
|
|
Components
|
12 components
|
10
|
Inventory policy dictates that sufficient materials be on hand at the end of the month to produce 50% of next month's production needs. This is exactly the amount of material on hand on 31 December of the prior year.
(c) Each unit uses six hours of direct labour. The average direct labour cost per hour is $28.50.
(d) Overhead each month is estimated using a flexible budget formula. (Note: Activity is measured in direct labour hours.)
|
|
Fixed-cost component ($)
|
Variable-cost component ($)
|
|
Supplies
|
-
|
2.00
|
|
Power
|
-
|
1.00
|
|
Maintenance
|
60 000
|
0.80
|
|
Supervision
|
32 000
|
-
|
|
Depreciation
|
400 000
|
-
|
|
Taxes
|
24 000
|
-
|
|
Other
|
159 800
|
1.00
|
(e) The projected sales in units for the first five months of 2018 follow:
|
|
January
|
February
|
March
|
April
|
May
|
|
Estimated unit sales
|
80 000
|
100 000
|
120 000
|
125 000
|
124 000
|
The selling price is $410 per unit. The sales for December 2017 are 75 000 units.
Assume the following in your answer:
- Direct materials inventory and finished goods inventory are costed using the FIFO method.
- There is no work-in-progress inventory at any given point in time.
REQUIRED:
Use two decimal places in calculations when appropriate and do not round up to the nearest dollar for example use direct labour cost per hour of $28.50 and not $29. Ensure your worksheets show such figures accordingly.
A. Design one input and the six output sheets as required in B below. Ensure your spreadsheets meet the "design of spreadsheets" and "formatting" requirements as stated above.
B. Prepare the following five (5) budgets for the first quarter of 2018. Show the monthly figures and the total for the quarter in separate columns.
1. Sales budget
2. Production budget. Show April in a column to the right of the "marks" column.
3. Direct material purchases budget. Show the calculations of Metal separate to that of Components.
4. Direct labour budget.
5. Overhead budget.
6. Prepare the ending finished goods inventory budget as at 31 March 2018. Show the unit cost of each component separately using two decimal places.
Attachment:- Assignment File.rar