Brooks Ltd makes and sells two sizes (Mini and Large) of foam rollers that people use to rejuvenate, recover, and relieve stress and muscle tightness, and to self-massage trigger points in their bodies. For the fourth quarter of 2014, the company sold 11 000 Mini rollers at $40 each and 4 200 Large rollers at $80 each. The following data are available for the 2015 calendar year budget: Mini rollers Quarter 1 Quarter 2 Quarter 3 Quarter 4 Estimated unit sales 10 000 14 000 16 000 18 000 Sales price per unit $40 $42 $42 $45 Direct labour hours per unit 5 minutes 4 minutes 4 minutes 4 minutes Direct material (foam) required per unit 0.5 kg 0.5 kg 0.5 kg 0.5 kg Large rollers Quarter 1 Quarter 2 Quarter 3 Quarter 4 Estimated unit sales 4 500 4 800 5 200 5 500 Sales price per unit $80 $90 $90 $95 Direct labour hours per unit 10 minutes 8 minutes 8 minutes 8 minutes Direct material (foam) required per unit 1 kilogram 1 kilogram 1 kilogram 1 kilogram Both products use the same direct materials and are manufactured by the same labourers. Following are the costs for Direct Materials and the rates for Direct Labour for the relevant periods: Costs Q4 2014 Q1 2015 Q2 2015 Q3 2015 Q4 2015 Direct material per kilogram $45 $50 $55 $55 $55 Direct labour per hour $15 $15 $18 $18 $18 The direct labour rate includes wages, all employee-related benefits, and the employer’s share of payroll tax. The company’s enterprise agreement calls for an increase in direct labour wages on 1 April every year (the first day of the second quarter). The increase from 1 April 2015 has been included in the direct labour hourly rate above. The current machinery has been in operation since 2012 and the direct labour hours and the quantity of the direct material foam required per unit of quarter 1 2015 has been the standard since then. New and improved machinery will be fully operational in the second quarter of 2015, hence the reduction on the direct labour time required per unit. Management desires an ending inventory of finished goods each quarter equal to 60 per cent of the next quarter’s sales. Management also desires an ending inventory of raw materials equal to 75% of the particular quarter’s usage. The raw material inventory on 31 December 2014 was 7 185 kilograms. The manufacturing overhead costs change twice a year: on the 1st of April and on the 1st of October every year. The variable cost per Direct Manufacturing Labour Hour (DMLH) rate is the same for both products. The following table summarises the manufacturing overhead costs relevant to the period of the budget, effective from the following dates: 1/10/2014 1/4/2015 1/10/2015 Variable cost per DMLH $3.00 $3.10 $3.20 Total fixed costs for six months (allocated equally p.m.) $30 000 $36 000 $36 000 5 The company calculates the fixed costs per unit based on the total number of rollers produced for the relevant quarter. 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: Design one input and the seven output sheets as required below. Ensure your spreadsheets meet the “design of spreadsheets” and “formatting” requirements as stated above. Keep and show the calculations of the two products separate unless informed otherwise. Show the budgets for the two products in separate columns for budgets 1, 2, 3, 4 and 6 below. Prepare the following five (5) budgets for the first and second quarter of 2015 and the totals for the first half of the year. Also show the total for the figures that will be linked to the Income Statement required in budget 7 below in each of these five budgets (where relevant): 1. Revenue 2. Production budget in units. Include a column on the left of the 2015 quarter columns and show the fourth quarter of 2014. Also show the total number of both products added together, produced per quarter and for the half year, at the bottom of this budget. 3. Direct material usage in quantity and dollars. First, calculate the budgeted kilograms of Direct Material used per product per quarter and for the half year separately and show the quantity used for the fourth quarter of 2014 in a column on the left hand side of the 2015 columns. Then, add the quantity figures of the two products together and show these figures for each quarter and for the half year. You require this to calculate the kilograms of Direct Material that will be used for the period. Then use these figures to calculate the kilograms of Direct Material as well as the cost of Direct Materials to be purchased and used for the period ending 30 June 2015. There is no need to calculate the cost used and/or purchased per product or per quarter. (Refer to Schedule 3A on page 383 of the prescribed textbook). 4. Direct manufacturing labour costs. Show the calculation of DMLH per product separately and then add the hours together to calculate the costs for the relevant periods. 5. Manufacturing overhead costs. Show the DMLH of both products together per quarter to calculate the variable costs. Prepare the following two (2) budgets for the first half of 2015. 6. Finished Goods inventory: beginning and ending (Show the calculation of the cost per unit for beginning and for closing inventory for both products) 7. Income Statement (use absorption costing method and show cost of goods manufactured, cost of goods available for sale, and cost of goods sold) Use three (3) decimal places in all numerical calculations. However, it is not necessary to show 3 decimal places in all the figures, as your worksheets will look cluttered and messy. Hence only show 3 decimal places in the cells which contain figures that have 3 decimal places. For example, show the figure 0.083 with 3 decimal places but do not show the figure 1 as 1.000.