ABC Pty Ltd produces turbines used in the production of hydro-electric generating equipment. The turbines are sold to various engineering companies that produce hydro-powered generators in Australia.

Details of the operations for the coming four months are provided in the attached excel spread sheet.

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

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.

Part B (25% of the marks)

Write report addressing the Sales managers concerns, using some of the concepts covered in topic 1 to 6 AND the information provided on the cost structures identified in the budget prepared in Part A. Your report should also include a discussion on the impact of the production manager's intended investment in new manufacturing capacity. Support your report with relevant calculations.

Excel File Edit View Insert Format Tools Data Window Help 40 30% 8 99% [27 Sat 22:24 Q Assignment ACT502 S2 2020 Q Search Sheet U+ Home Insert Page Layout Formulas Data Review View OTA Calibri (Body) 11 - A- A- Wrap Text Accounting Insert Delete Paste BI UT TS AT Merge & Center $ % " -.0 Conditional Format Cell Format Sort & Formatting as Table Styles Filter A10 X V fix The variable manufacturing cost of the opening finished goods inventory is A B C D E F H 1 J K L M N 0 P Q R S 1 ABC Pty Ltd Sales April May June July Actual Sales Volume 3-moths to June 5 Units 67,500 54,000 60,750 81,000 162,200 6 Unit selling price $6,200 Co The desired finished goods inventory for each month is 60% of the next month's sales 9 The full absorption cost of the opening finished goods inventory is $4,730 per unit 10 The variable manufacturing cost of the opening finished goods inventory is $1,930 per unit 11 Finished goods inventory on April 1 is 43,200 units 12 Materials required to be on hand at the beginning of the month to produce 20% of that month's estimated sales 13 14 6Direct Material used per unit Actual Material Used - 3 months to June 16 Rotor Blades tote Blades 17 Quantity 750,800 729,100 Cost per unit $81 $108 Actual cost of material Used - 3 months to June 19 $48,651,840 580,883,680 20 Budgeted Direct Labour time per unit B hours Actual Labour Used - 3 months to June 22 1,662,590 23 Direct Labour cost per hour $50 Actual cost of labour Used - 3 months to June 24 $70,660,100 25 6 Budgeted Manufacturing Overheads Recent statistical data for Maintenance Costs 27 Fixed Cost Variable Cost component per component per 28 month DL hour Labour Hours Total Maintenance Costs 29 1,302,800 $79,920,000 30 Indirect labour $56.70 1,485,000 $86,805,000 Sheet1 Part 1 + Ready + 100% OCT ding Invent 3 -A W XLSXExcel File Edit View Insert Format Tools Data Window Help 40 30% 8 99% [27 Sat 22:25 Q Assignment ACT502 S2 2020 Q Search Sheet U+ Home Insert Page Layout Formulas Data Review View Calibri (Body) 11 - A- A- Wrap Text Accounting Insert Delete Paste BI UT TAT + Merge & Center $ ~ % ) Conditional Format Cell Sort & Formatting as Table Styles Format Filter A10 X V fix The variable manufacturing cost of the opening finished goods inventory is A B C D E F H K L M N 0 P Q R S 20 21 Budgeted Direct Labour time per unit 8 hours Actual Labour Used - 3 months to June 22 1,662,590 23 Direct Labour cost per hour $50 Actual cost of labour Used - 3 months to June 24 $70,660,100 25 26 Budgeted Manufacturing Overheads Recent statistical data for Maintenance Costs 27 Fixed Cos Variable Cost component per component per 28 month DL hour Labour Hours Total Maintenance Costs 29 1,302,800 $79,920,000 30 Indirect labour $56.70 1,485,00 $86,805,000 31 $5.40 1,363,500 $82,215,000 2 Maintenance ?? 1,242,000 $77,625,000 33 34 35 Supervision $37,800,000 6 Depreciation $3,375,000 50 7 Rates & Utilities $2,789,100 SO 38 Other $13,500,000 $40.50 39 40 41 April May June Variable Selling Expenses $62,775,000 $50,220,000 $56,497,500 Fixed Selling & Admin 44 Expenses $24,300,000 $19,440,000 $21,870,000 Total Selling & Admin 45 $87,075,000 $69,660,000 $78,367,500 46 47 Sheet1 Part 1 + Ready + 100% OCT ding Invent 3 X XLSXExcel File Edit View Insert Format Tools Data Window Help 40 30% 8 99% [27 Sat 22:25 Q Assignment ACT502 S2 2020 Q Search Sheet U+ Home Insert Page Layout Formulas Data Review View Calibri (Body) 11 - A- A- Wrap Text Accounting Insert Delete Paste + Merge & Center $ % " -.0 Conditional Format Cell Format Sort & Formatting as Table Styles Filter A10 X V fx The variable manufacturing cost of the opening finished goods inventory is B C D E H J K L M N 0 P Q R S 3 Variable Selling Expenses $62,775,000 $50,220,000 $56,497,500 Fixed Selling & Admin 44 Expenses $24,300,000 $19,440,000 $21,870,000 Total Selling & Admin 45 $87,075,000 $69,660,000 $78,367,500 46 47 48 49 Cash on hand at opening $3,375,000 50 51 Annual interest rate on borrowing 6% 53 Cash Sales $0 54 Received in month of sale 80 55 Received in month after sale 18% 56 Balance of accounts receivables at the start of 7 the month $82,863,000 58 59 60 Dividends paid in June $1,194,750 Land purchased in May $185,100,000 62 63 64 65 66 67 68 69 70 Sheet1 Part 1 + Ready + 100% OCT ding Invent 3 WE X XLSX