Product Costs Worksheet: Step 1: Use the V look-up function and the part number given, complete "costs per unit for the respective types of computers. For reference on how to write Vlookup function, please see link at "Vlookup Function from MS Excel (Hint: you may also want to use an absolute reference to lock the range referenced in the vlook up function). If you receive the N/A error message after writing the formula, please check the reference link for possible reasons and fix any errors using the procedure How to correct the N/a message from Vlookup" Step 2: Calculate the overhead cost per direct labor hour based on the overhead cost shown in the "Inventory Master File". The estimated direct labor hours for the year will be provided. Use that information and the direct labor rate per hour to finish calculating the total cost of each of the products. Feel free to add column as needed (for example, you should add a column called "Total Cost") Step 3: Calculate the total material cost and total cost per each computer unit. omputer Company ory Master File Colper Inventory Category Description Unit of Measure Current Inventory Selling Price Component 152 ca ca ca ca Part Number 00100 00200 00210 00300 00400 00410 00500 00510 00600 00610 00700 OOOO 00820 00900 00910 10100 10200 CA ca Computer Case Computer Complete Computer Complete Cooler Graphics Card Graphics Card Memory Memory Mother Board Mother Board Power Source Processor Processor SSD Drive SSD Drive Labor Overhead Coursair Obsidian 7500 CC ATX Full Tower Computer Case Premium Desktop Gaming Computer Premium Descktop Computer - Power Users Corsair Hydro Series H150 PRO 240mm Water liquid CPU Cooler Nvidia GeForce RTC 2080 Ti Graph Card 11GB Nvidia GeForce GTX 970 Graphic Card 4 GB G.SKILL Trideni2 RGB Series 32GB SDRAM DDR4 3600 G.SKILL TridentZ RGB Series 16GB SDRAM DDR4 3200 ASUS AMD AM4 ROG Strix Gaming ATX Motherboard ASUS AM4 TUF Gaming X37..Plus ATX Motherboard with PCIe 4.0 Corsair RMx Series RMKSINS0 Will Power Source AMD Ryzen 9 3900X 12 Core 3.8 GHz Processor AMD Ryzen 7 3800X 8-Core 3.9 GHZ Processor wiCorsair Hydro water cooler Crucial MX500 2TB SATA III Internal Solid State Drive Crucial MX500 2TB SATA III Solid State Drive Direct Labor Overhead 2.000 20 50 30 30 30 30 30 30 30 30 30 30 30 190 3.558 2.200 190 1.250 470 185 100 320 225 150 530 510 510 240 SO 27.5 152 1.000 400 197 ND 272 INO 120 434 408 108 192 20 hr hr Cost per Unit of Measure Total Product Cost Component ea Computer Company ming Compter Product Cost Part Description Number 00100 Coursair Obsidian 7500 CC ATX Full Tower Computer Case 00600 ASUS AMD AM4 ROG Strix Gaming ATX Motherboard 00800 AMD Ryzen 9 3900X 12 Core 3.8 GHz Processor 00300 Corsair Hydro Series H150i PRO 240mm Water/liquid CPU Cooler 00500 G. SKILL TridentZ RGB Series 32GB SDRAM DDR4 3600 00400 Nvidia GeForce RTC 2080 Ti Graph Card 11GB 00700 Corsair RMx Series RM850x 850 Watt Power Source 00900 Crucial MX500 2TB SATA III Internal Solid State Drive Total Materials 10100 Direct Labor 10200 Overhead 00200 Total Cost per Unit 2 2 2 2 2 2 2 2 Units Required 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 en ea hr hr 3.00 3.00 Unit of Measure Cost per Component ea ea ea Computer Company p Computer-Power Users Part Number Description 00100 Coursair Obsidian 7500 CC ATX Full Tower Computer Case 00610 ASUS AM4 TUF Gaming X57--Plus ATX Motherboard with PCIe 4.0 00820 AMD Ryzen 7 3800X 8-Core 3.9 GHZ Processor w/Corsair Hydro water cooler 00510 G.SKILL TridentZ RGB Series 16GB SDRAM DDR4 3200 00410 Nvidia GeForce GTX 970 Graphic Card 4 GB 00700 Corsair RMx Series RM850x 850 Watt Power Source 00910 Crucial MX500 2TB SATA III Solid State Drive Total Materials 10100 Direct Labor 10200 Overhead 00210 Total Cost per Unit Units Required 1.00 1.00 1.00 1.00 1.00 2.00 1.00 ea ea hr hr 2.00 2.00
