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:

• 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.
• 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.

• Each unit uses six hours of direct labour. The average direct labour cost per hour is \$28.50.
• 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

• 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.

• 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.

1. 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.
2. 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.
3. Sales budget
4. Production budget. Show April in a column to the right of the “marks” column.
5. Direct material purchases Show the calculations of Metal separate to that of Components.
6. Direct labour budget.