F1 F2 F3 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 E14:E23 use calculations to lookup the correct description for each item from the Inventory table. (Please check answers are correct and still work if inventory data is sorted differently.) In G14:G23 use calculations to lookup the correct cost price for each item from the Inventory table. The hourly rate for most labour tasks can be looked up in the Labour Costs sheet using the activity code. Some tasks labelled Special however incur a special rate determined just for that job, the special rate is specified in H9. In G31 create a formula that will return the appropriate rate for each activity code or the special rate if that has been applied. Copy down to G39. Note: you are required to have the same formula in all these cells as this job sheet needs to be reused for other jobs. In 649 use a calculation to determine the total direct labour hours for the department shown in C49. (Note the formula must work for different scenarios. you can't assume that C34:C38 will always be engineering). Copy the formula down to G51. In H49:H51 use the data in the Information worksheet to determine the overhead costs for each department. In D59 use a cell reference to pull through the quoted price from the Information sheet. In H59 calculate the total costs. (Mark will only be awarded if the answer AND the formula are correct). In H60 calculate the Gross Profit Percent. F4 F5 F6 F7 F8 AB D E G 3 SafeTNET Job Card 4 Job No. 4636 Pyrmont Industries 167 Pyrmont Rd, Sydney, 2000 Date Started: 05/08/2021 Date Finished: 26/08/2021 Core Network Upgrade Special Activity Rate: $190.00 Details/Description Cost/Unit Total Cost 5 6 Customer 7 8 9 Job Description 10 11 . 12 MATERIALS 13 Item No 14 1 15 2 16 3 17 4 18 5 19 6 20 7 21 8 22 9 23 10 24 25 26 27 TOTAL MATERIALS 28 29 LABOUR TASKS 30 Department 31 Management 32 Sales Support 33 Sales Support 34 Engineering 35 35 Engineering 36 Engineering 37 Engineering 38 Engineering 39 Sales Support 40 40 Item Code SX-F1-90397 SX-F1-70603 SX-24-44965 . IVI-LI-25984 FI-SX-40752 E1M-SX-46099 E1M-LX-71138 E1M-CW-54373 10G-XF-87968 10G-XF 62532 Quantity 6 2 6 1 1 6 6 4 8 2 $ Hourly Rate Cost Activity Code 4026 3027 3028 1022 3035 3035 1022 3035 Special 2050 Details/Description Requirements Gathering High Level Design Detailed Design Install new Standby Chassis Switchover Primary to Standby and Monitor Install new Primary Chassis Switchover Standby to new Primary and Monitor Review and Document confirgurations Training and Handover Hours 16 24 32 16 8 16 10 8 . 8 24 41 42 43 44 Formula Bar 45 TOTAL LABOUR 152 $ Hours Cost 46 47 PRODUCTION OVERHEADS 48 Department Application method 49 Engineering Direct labour hours 50 Management Direct labour hours 51 Sales Support Direct labour hours 52 53 54 55 TOTAL OVERHEADS 56 57 SUMMARY 58 59 Invoice Amount $275,000.00 60 61 $ Total Cost Gross Profit Margin (%) G1 Complete the journal entries for Job 4636. Select the correct Journal from the drop-down list and then use a calculation and appropriate values in the rest of the workbook to calculate each entry. 22 Mark for each correct entry (must have both journal and value correct.) The first set have been provided as an example. A B 1 Journal Entries for Job 4636 2 Credit The Materials inventory was debited for the cost of the materials at time of purchase, so this has already been done. You will need to complete the other journal entries for job 4636. Select the appropriate accounts from the drop down lists, then calculate the appropriate debit and credit amounts (use an Excel formula to reference the appropriate cell, do not just type the values in). The first one has been done for you to give you an idea. 3 4 5 Account Debit U 7 Issue of direct materials to production 8 Work in Process Inventory $0.00 9 Raw Materials Inventory $0.00 10 11 Direct labour incurred 12 13 14 15 Overhead applied to production 16 17 18 19 Cost of finished production transferred to Finished Goods store 20 21 22 23 Cost of finished product sold 24 25 26 27 August Sales 28 29 Accounts Payable 30 31 Accounts Receivable 32 Manufacturing Overhead 33 Cost of Goods Sold 34 35 Finished Goods Inventory 36 Raw Materials Inventory 37 Sales 38 39 Wages Payable 40 Work in Process Inventory 41 42 F1 F2 F3 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 E14:E23 use calculations to lookup the correct description for each item from the Inventory table. (Please check answers are correct and still work if inventory data is sorted differently.) In G14:G23 use calculations to lookup the correct cost price for each item from the Inventory table. The hourly rate for most labour tasks can be looked up in the Labour Costs sheet using the activity code. Some tasks labelled Special however incur a special rate determined just for that job, the special rate is specified in H9. In G31 create a formula that will return the appropriate rate for each activity code or the special rate if that has been applied. Copy down to G39. Note: you are required to have the same formula in all these cells as this job sheet needs to be reused for other jobs. In 649 use a calculation to determine the total direct labour hours for the department shown in C49. (Note the formula must work for different scenarios. you can't assume that C34:C38 will always be engineering). Copy the formula down to G51. In H49:H51 use the data in the Information worksheet to determine the overhead costs for each department. In D59 use a cell reference to pull through the quoted price from the Information sheet. In H59 calculate the total costs. (Mark will only be awarded if the answer AND the formula are correct). In H60 calculate the Gross Profit Percent. F4 F5 F6 F7 F8 AB D E G 3 SafeTNET Job Card 4 Job No. 4636 Pyrmont Industries 167 Pyrmont Rd, Sydney, 2000 Date Started: 05/08/2021 Date Finished: 26/08/2021 Core Network Upgrade Special Activity Rate: $190.00 Details/Description Cost/Unit Total Cost 5 6 Customer 7 8 9 Job Description 10 11 . 12 MATERIALS 13 Item No 14 1 15 2 16 3 17 4 18 5 19 6 20 7 21 8 22 9 23 10 24 25 26 27 TOTAL MATERIALS 28 29 LABOUR TASKS 30 Department 31 Management 32 Sales Support 33 Sales Support 34 Engineering 35 35 Engineering 36 Engineering 37 Engineering 38 Engineering 39 Sales Support 40 40 Item Code SX-F1-90397 SX-F1-70603 SX-24-44965 . IVI-LI-25984 FI-SX-40752 E1M-SX-46099 E1M-LX-71138 E1M-CW-54373 10G-XF-87968 10G-XF 62532 Quantity 6 2 6 1 1 6 6 4 8 2 $ Hourly Rate Cost Activity Code 4026 3027 3028 1022 3035 3035 1022 3035 Special 2050 Details/Description Requirements Gathering High Level Design Detailed Design Install new Standby Chassis Switchover Primary to Standby and Monitor Install new Primary Chassis Switchover Standby to new Primary and Monitor Review and Document confirgurations Training and Handover Hours 16 24 32 16 8 16 10 8 . 8 24 41 42 43 44 Formula Bar 45 TOTAL LABOUR 152 $ Hours Cost 46 47 PRODUCTION OVERHEADS 48 Department Application method 49 Engineering Direct labour hours 50 Management Direct labour hours 51 Sales Support Direct labour hours 52 53 54 55 TOTAL OVERHEADS 56 57 SUMMARY 58 59 Invoice Amount $275,000.00 60 61 $ Total Cost Gross Profit Margin (%) G1 Complete the journal entries for Job 4636. Select the correct Journal from the drop-down list and then use a calculation and appropriate values in the rest of the workbook to calculate each entry. 22 Mark for each correct entry (must have both journal and value correct.) The first set have been provided as an example. A B 1 Journal Entries for Job 4636 2 Credit The Materials inventory was debited for the cost of the materials at time of purchase, so this has already been done. You will need to complete the other journal entries for job 4636. Select the appropriate accounts from the drop down lists, then calculate the appropriate debit and credit amounts (use an Excel formula to reference the appropriate cell, do not just type the values in). The first one has been done for you to give you an idea. 3 4 5 Account Debit U 7 Issue of direct materials to production 8 Work in Process Inventory $0.00 9 Raw Materials Inventory $0.00 10 11 Direct labour incurred 12 13 14 15 Overhead applied to production 16 17 18 19 Cost of finished production transferred to Finished Goods store 20 21 22 23 Cost of finished product sold 24 25 26 27 August Sales 28 29 Accounts Payable 30 31 Accounts Receivable 32 Manufacturing Overhead 33 Cost of Goods Sold 34 35 Finished Goods Inventory 36 Raw Materials Inventory 37 Sales 38 39 Wages Payable 40 Work in Process Inventory 41 42