ABC Pty Ltd produces turbines used in the production of hydro-electric generating equipment. The turbines are sold to various engineering companies that produce hydropowered generators in Australia.
Details of the operations for the coming four months are provided in the attached excel spread sheet (See picture 1)
Other information:
The company plans to purchase land for future expansion
? Sales are on credit. Amounts not received in the month following the sale are written off as bad debt immediately.
? The payment for labour and purchases of materials and other costs are for cash and paid for in the month of acquisition.
? If the firm develops a cash shortage by the end of the month, sufficient cash is borrowed to cover the shortage (including any interest payments due ). Any cash borrowed is repaid one month later, as is the interest due.
During the process of preparing the organisation's budget, the Sales Manager is discussing the possible outcome of the forthcoming election with the Production Manager. She noted that if one of the major political parties wins the election and forms the government, there is a strong possibility that alternative sources of energy such as hydro-powered electricity may no longer be as actively supported by the new government as is the case under the current government.
The sales manager's primary concern is that market for alternative power generation is already volatile and subject to significant uncertainty. The production manager is also concerned about his plans to build the new automated manufacturing facility on the land to be purchased in May. This new manufacturing facility will enable him to manufacture, in-house, the major two parts he is now purchasing and to significantly automate the assembly process that is currently somewhat labour intensive. His projection for the new facility indicates a reduction in direct material & direct labour costs of 33% but that his fixed manufacturing overheads are likely to increase by 65% due to the increased investment in production capacity.
Required:
Part A: Prepare Operating Budgets as follows:
1) Monthly Sales Budget for the quarter ending June
2) Monthly Production Budget for the quarter ending June
3) Monthly Direct Materials Budget for the quarter ending June
4) Monthly Direct Labour Budget for the quarter ending June
5) Monthly Manufacturing Overhead Budget for the quarter ending June
6) Monthly Selling & Administrative Expenses Budget for the quarter ending June
7) Ending Inventory Budget for the month of June
8) Cost of goods Sold Budget for the quarter
9) Budgeted Income Statement for the quarter
10) Monthly Cash budget for the quarter.
Please ONLY answer question 6-10. Questions 1-5 has been answered (See picture 2-4). Thanks.
ABC Pty Ltd Sales April May June July Actual Sales Volume 3-moths to June Units 67,500 54,000 60,750 81,000 162,200 Unit selling price $6,200 The desired finished goods inventory for each month is 60% of the next month's sales The full absorption cost of the opening finished goods inventory is $4,730 per unit The variable manufacturing cost of the opening finished goods inventory is $1,930 per unit Finished goods inventory on April 1 is 43,200 units Materials required to be on hand at the beginning of the month to produce 20% of that month's estimated sales Direct Material used per unit Actual Material Used - 3 months to June Rotor Blades Rotor Blades Quantity 5 750,800 729,100 Cost per unit $81 $108 Actual cost of material Used - 3 months to June $48,651,840 $80,883,680 Budgeted Direct Labour time per unit 8 hours Actual Labour Used - 3 months to June 1,662,590 Direct Labour cost per hour $50 Actual cost of labour Used - 3 months to June $70,660,100 Budgeted Manufacturing Overheads Recent statistical data for Maintenance Costs Fixed Cost Variable Cost component component per month per DL hour Labour Hours Total Maintenance Costs 1,302,800 $79,920,000 Indirect labour $0 56.70 1,485,000 $86,805,000 Power $5.40 1,363,500 $82,215,000 Maintenance 30,705,000 37.78 1,242,000 $77,625,000 Supervision $37,800,000 $3,375,000 $0 Depreciation Rates & Utilities $2,789,100 Other $13,500,000 $40.50 April May June Variable Selling Expenses $62,775,000 $50,220,000 $56,497,500 Fixed Selling & Admin Expenses $24,300,000 $19,440,000 $21,870,000 Total Selling & Admin Expenses $87,075,000 $69,660,000 $78,367,500 Cash on hand at opening $3,375,000 Annual interest rate on borrowing 6% Cash Sales $0 Received in month of sale 80% Received in month after sale 18% Balance of accounts receivables at the start of the month $82,863,000 Dividends paid in June $1,194,750 Land purchased in May $185,100,000ABC Pty Ltd. Sales Budget Quarter Ending June Budgeted Budgeted Budgeted Unit Sales Unit Price Sales Dollar April 67,500 $ 6,200 $ 418,500,000 May 54,000 $ 6,200 334,800,000 June 60,750 $ 6,200 376,650,000 Total for the quarter ending June 182,250 $ 1,129,950,000 ABC Pty Ltd. Production Budget Quarter Ending June April May June Quarter Next month's budgeted sales (units) 54,000 60,750 81,000 Ratio of inventory to future sales 60% 60% 60% Sales (in units) 67,500 54,000 60,750 182,250 Add: Ending finished goods inventory 32,400 36,450 48,600 48,600 Required units of available production 99,900 90,450 109,350 230,850 Less: Opening finished goods inventory 43,200 32,400 36,450 43,200 Units to be produced 56,700 58,050 72,900 187,650 ABC Pty Ltd. Raw Material Purchase Budget - Rotor Quarter Ending June April May June Quarter Production Budget (units) 56,700 58,050 72,900 187,650 Raw material required per unit of finished goods 4.00 4.00 1.00 4.00 Material needed for production 226,800 232,200 291,600 750,600 Add: Ending raw material 10,800 12,150 16,200 16,200 Total material requirement 237,600 244,350 307,800 766,800 Less: Opening raw material inventory 13,500 10,800 12,150 13,500 Material to be purchased 224,100 233,550 295,650 753,300 Raw material cost per unit 81.00 $ 81.00 $ 81.00 $ 81.00 Total raw material cost S 18,152,100 $ 18,917,550 $ 23,947,650 $ 61,017,300ABC Pty Ltd. Raw Material Purchase Budget - Blades Quarter Ending June April May June Quarter Production Budget (units) 56,700 58,050 72,900 187,650 Raw material required per unit of finished goods 5.00 5.00 5.00 5.00 Material needed for production 283,500 290,250 364,500 938,250 Add: Ending raw material 10,800 12,150 16,200 16,200 Total material requirement 294,300 302,400 380,700 954,450 Less: Opening raw material inventory 13,500 10,800 12,150 13,500 Material to be purchased 280,800 291,600 368,550 940,950 Raw material cost per unit 108.00 $ 108.00 S 108.00 $ 108.00 Total raw material cost $ 30,326,400 $ 31,492,800 $ 39,803,400 $ 101,622,600 ABC Pty Ltd. Direct Labour Budget Quarter Ending June April May June Quarter Production Budget (units) 56,700 58,050 72,900 187,650 Labour hour required per unit of finished goods 8.00 8.00 8.00 8.00 Total labour hours needed 453,600 464,400 583,200 1,501,200 Labour cost per labour hour 50 $ 50 $ 50 $ 50 Budgeted direct labour cost S 22,680,000 $ 23,220,000 $ 29,160,000 $ 75,060,000ABC Pty Ltd. Manufacturing Overhead Budget Quarter Ending June April May June Quarter Total labour hours needed 453,600 464,400 583,200 1,501,200 Variable manufacturing Overhead: Indirect labor $ 25,719,120 $ 26,331,480 $ 33,067,440 $ 85,118,040 Power S 2,449,440 $ 2,507,760 $ 3,149,280 $ 8,106,480 Maintenance @$37.78 per Lab. Hr. $ 17,136,000 $ 17,544,000 $ 22,032,000 $ 56,712,000 Other $ 18,370,800 $ 18,808,200 $ 23,619,600 $ 60,798,600 Total variable manufacturing overhead $ 63,675,360 $ 65,191,440 $ 81,868,320 $ 210,735,120 Fixed overhead cost: Supervision $ 37,800,000 $ 37,800,000 $ 37,800,000 $ 113,400,000 Depreciation 3,375,000 $ 3,375,000 $ 3,375,000 $ 10,125,000 Rates & Utilities S 2,789,100 $ 2,789,100 $ 2,789,100 $ 8,367,300 Other S 13,500,000 $ 13,500,000 $ 13,500,000 $ 40,500,000 Maintenance S 30,705,000 $ 30,705,000 $ 30,705,000 $ 92,115,000 Total fixed overhead cost $ 88,169,100 $ 88,169,100 $ 88,169,100 $ 264,507,300 Total manufacturing overhead $ 151,844,460 $ 153,360,540 $ 170,037,420 $ 475,242,420 Working Notes. Maintenance cost per DLH = ($86,805,000 - 77,625,000) / (1,485,000 - 1,242,000) = $37.78 per DLH Fixed maintenance cost = $86,805,000 - (1,485,000 * $37.78) = $30,705,000
