Could you help to work out these instructions and write clearly what formulas to use in excel would be greatly appreciated?
C Information 2 3 Hourly Wage Rates: Department Rate Hour 5 Engineering $20.00 6 Programming $24.00 7 Assembly $24.00 8 Manufacturing overhead is applied to jobs using a pre-determined factory-wide rate 9 per direct labour hour based on the following annual budgets: 10 Budgeted manufacturing overhead $70,000.00 Budgeted direct labour hours $7,400.00 12 13 During September Job 4971 was started and completed 14 Job 4971 was invoiced on 18 September for the quoted price of: $36,000.00 15 16 Journal Accounts 17 Accounts Payable 18 Accounts Receivable 19 Manufacturing Overhead 20 Cost of Goods Sold 21 Finished Goods Inventory 22 Materials Inventory 23 Sales 24 Wages Payable 25 Work in Process Inventory 26 Section D D1 The following are to be completed in the JOB_4971 Worksheet This sheet shows the Job Card for the most recent job, but it is missing several calculations. The first cost section is for direct materials. Items used for the job are listed by item code. In F13:F18 use calculations to lookup the correct description for each item from the Inventory table. In 113:118 use calculations to lookup and calculate the total cost for each item. (Cost to the company not the client). In 121 calculate the total costs for direct materials. In H25:431 use a formula to lookup the correct hourly rate for each department from the Information sheet. In 125:131 calculate the labour costs for each task. In 133 calculate the average hourly rate. (What it cost on average for one hour of labour on this job.) In 134 calculate the total labour costs. In H37 use a calculation to determine the total direct labour hours for the department shown in C37. (Note the formula must work for different scenarios. you can't assume that C26 and C29 will always be Assembly). Copy the formula down to H39. In 137:139 use the data in the Information worksheet to determine the manufacturing overheads for each department. D10 Question Instruction D11 D12 In 142 calculate the total manufacturing overheads. In 145 calculate the total costs. In 146 reference (pull through) the Invoice Amount from the Information Sheet. In 147 calculate the Gross Profit Margin. D13 D14 A 10 Quantity 11 12 13 14 15 DIRECT MATERIALS Date Department 14/9/19 Assembly 14/9/19 Assembly 14/9/19 Assembly 14/9/19 Assembly 8/9/19 Assembly 5/09/2019 Programming Req.No. 225 225 225 Item Code Details/Description S1010 S1009 S1014 R1024 R1004 | 228 224 223 18 19 Total Cost Direct Materials: 22 23 24 25 Rate 26 27 DIRECT LABOUR Date Department 16/9/19 Engineering 14/9/19 Assembly 11/9/19 Programming 9/9/19 Engineering 8/9/19 Assembly 5/9/19 Programming 2/9/19 Engineering Time No. 1028 A388 P227 1026 A386 P225 1024 Details/Description Installation of robots Final assembly in frame and finishing Re-programming Change design request Installing robot kit and servos Programming MGX 8025 Chip Set Design, blueprints and specifications prepared 28 Average Hourly Rate: Total Cost Labour: Hours 35 MANUFACTURING OVERHEAD 36 Month Department 37 September Assembly 38 September Engineering September Programming Application method Direct labour hours Direct labour hours Direct labour hours 39 40 TA Total Manufacturing Overhead: SUMMARY Total Cost Quoted Price GP Percent C Information 2 3 Hourly Wage Rates: Department Rate Hour 5 Engineering $20.00 6 Programming $24.00 7 Assembly $24.00 8 Manufacturing overhead is applied to jobs using a pre-determined factory-wide rate 9 per direct labour hour based on the following annual budgets: 10 Budgeted manufacturing overhead $70,000.00 Budgeted direct labour hours $7,400.00 12 13 During September Job 4971 was started and completed 14 Job 4971 was invoiced on 18 September for the quoted price of: $36,000.00 15 16 Journal Accounts 17 Accounts Payable 18 Accounts Receivable 19 Manufacturing Overhead 20 Cost of Goods Sold 21 Finished Goods Inventory 22 Materials Inventory 23 Sales 24 Wages Payable 25 Work in Process Inventory 26 Section D D1 The following are to be completed in the JOB_4971 Worksheet This sheet shows the Job Card for the most recent job, but it is missing several calculations. The first cost section is for direct materials. Items used for the job are listed by item code. In F13:F18 use calculations to lookup the correct description for each item from the Inventory table. In 113:118 use calculations to lookup and calculate the total cost for each item. (Cost to the company not the client). In 121 calculate the total costs for direct materials. In H25:431 use a formula to lookup the correct hourly rate for each department from the Information sheet. In 125:131 calculate the labour costs for each task. In 133 calculate the average hourly rate. (What it cost on average for one hour of labour on this job.) In 134 calculate the total labour costs. In H37 use a calculation to determine the total direct labour hours for the department shown in C37. (Note the formula must work for different scenarios. you can't assume that C26 and C29 will always be Assembly). Copy the formula down to H39. In 137:139 use the data in the Information worksheet to determine the manufacturing overheads for each department. D10 Question Instruction D11 D12 In 142 calculate the total manufacturing overheads. In 145 calculate the total costs. In 146 reference (pull through) the Invoice Amount from the Information Sheet. In 147 calculate the Gross Profit Margin. D13 D14 A 10 Quantity 11 12 13 14 15 DIRECT MATERIALS Date Department 14/9/19 Assembly 14/9/19 Assembly 14/9/19 Assembly 14/9/19 Assembly 8/9/19 Assembly 5/09/2019 Programming Req.No. 225 225 225 Item Code Details/Description S1010 S1009 S1014 R1024 R1004 | 228 224 223 18 19 Total Cost Direct Materials: 22 23 24 25 Rate 26 27 DIRECT LABOUR Date Department 16/9/19 Engineering 14/9/19 Assembly 11/9/19 Programming 9/9/19 Engineering 8/9/19 Assembly 5/9/19 Programming 2/9/19 Engineering Time No. 1028 A388 P227 1026 A386 P225 1024 Details/Description Installation of robots Final assembly in frame and finishing Re-programming Change design request Installing robot kit and servos Programming MGX 8025 Chip Set Design, blueprints and specifications prepared 28 Average Hourly Rate: Total Cost Labour: Hours 35 MANUFACTURING OVERHEAD 36 Month Department 37 September Assembly 38 September Engineering September Programming Application method Direct labour hours Direct labour hours Direct labour hours 39 40 TA Total Manufacturing Overhead: SUMMARY Total Cost Quoted Price GP Percent
