Assignment Question -

Tembisa Ltd produces indoor playground equipment. The company is completing its fifth year of operations and is preparing to build its master budget for the coming year (2017). The master budget will be based on the following information:

(a) The finished goods inventory was 13 000 units at 31 December 2016. Tembisa Ltd is planning the following ending finished goods inventories for each quarter of 2017:

(b) Each piece of equipment uses five hours of direct labour and three units of direct materials. Workers are paid $10 per hour, and one unit of direct materials costs $80.

(c) Tembisa plans to have closing inventory of direct materials of 30% of the direct materials needed for next quarter's unit sales.

(d) Fixed overhead totals $1 million each quarter. The fixed overhead rate is computed by dividing the quarter's total fixed overhead by the quarter's budgeted production in units.

(e) Variable overhead is budgeted at $6 per direct labour hour.

(f) The forecast quarterly sales in units for 2017 are as follows:

The selling price is $400 per unit. The fourth-quarter sales for 2016 are 65 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:

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 and the second quarter of 2017 and also show the totals for the first half of the year.               

1. Sales budget

2. Production budget.

3. Direct material purchases budget.

4. Direct labour budget.

5. Overhead budget.

6. Prepare the ending finished goods inventory budget as at 30 June 2017. Show the unit cost of each component separately using two decimal places.

Attachment:- Assignment File.rar