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.

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.

Step 3: Calculate the total material cost and total cost per each computer unit.

Daily Sales Worksheet:

Step 1: Use the Vlook-up function and the part number given, to link the selling price in the Inventory Master File to the Daily Sales worksheet. Using the SumIf function to total the units and the dollar sales for each type of computer.

Step 2: a pivot table using the same daily sales file to show by computer the total units sold and the total dollar sales for each of the two products in the same worksheet.

Cost of Good Manufactured (sch cgm) worksheet:

Step 1: The income data (inc data) worksheet is provided and sorted by the account name. You will need to re-sort the accounts and its balance by function to complete the cost of goods manufactured and income statements.

Step 2: Create a new work sheet by right clicking the inc data tab. Choose move or copy function to duplicate the inc data work sheet. Name is as inc data sorting work sheet. You can work out all your numbers in here without changing your original numbers.

Step 3: Using data provided from the income statement data (inc data) worksheet and the balance sheet (bal) complete the Schedule of Cost of Goods Manufactured (see tab sch cgm).

Step 4: Calculate the cost of the goods manufactured for the month of February.

Cost of Goods Sold Worksheet:

Step 1: Set up another blank worksheet named as CGS. Using Cost of Goods Manufactured calculated from sch_cgm worksheet and adding data needed from the balance sheet, calculate the Cost of goods sold.

Income Statement Worksheet:

Step 1: Set up a separate worksheet called IS in a multi-step income statement format.

You are going build a Multi-step Income Statement by linking all G/A expenses and Sales expenses from the inc data sorting worksheet previously completed. Sorting related steps were discussed in previous section.

Step 2: Using the cost of goods sold from the CGS worksheet and the inc data sorting worksheet you updated, prepare a multistep income statement in good form.

Step 3: Use 21% as the federal income tax rate. Apply the rate to the pre-tax income and calculate the final net income after the tax expense.

Step 4: Make sure that you have properly set up the income statement to print out on a single page in good form.

B E F C Best Computer Company 1 2 Accounting policies -EEEEEE 4 Category Account numb: Account Description Type Classification Product/Period 5 Balance Sheet 10100 Cash and Cash equivalents Assets Current Assets NIA 6 Balance Sheet 10200 Short term investments Assets Current Assets N/A 7 Balance Sheet 10300 Accounts Receivable Assets Current Assets NIA Balance Sheet 10400 Office supplies Assets Current Assets N/A 9 Balance Sheet 11130 Inventories. raw material Assets Current Assets NIA 10 Balance Sheet 11230 Inventories-work in process Assets Current Assets N/A 11 Balance Sheet 11330 Inventories. Finished goods Assets Current Assets NIA 12 Balance Sheet 16000 Plant Assets Long term asse NIA 13 Balance Sheet 16110 Equipment office Assets Long term asse NIA 14 Balance Sheet 16111 Accumulated depreciation_office equil Assets Long term asse NIA 15 Balance Sheet 16120 Equipment-selling Assets Long term asse NIA 16 Balance Sheet 16121 accumulated depreciation_selling equil Assets Long term asse NIA 17 Balance Sheet 16130 Equipment factory Assets Long term asse NIA 18 Balance Sheet 16131 accumulated depreciation_factory equ Assets Long term asse NIA 19 Balance Sheet 18000 Other fixed assets Assets Long term asse N/A 20 Balance Sheet 20100 Accounts payable Liability Current Liability NIA 21 Balance Sheet 20200 Wages payable Liability Current Liability NIA 22 Balance Sheet 20300 Payroll tax payable Liability Current Liability NIA 23 Balance Sheet 20400 Income tax payable Liability Current Liability NIA 24 Balance Sheet 31000 Common Stock Euqity Equity NIA 25 Balance Sheet 32000 Retained Earnings Euqity Equity NIA 26 Income Statemen 41000 Product sales Revenue Revenue NIA 27 Income Statemen 51000 Cost of good sold CGS CGS product 28 Income Statemen 52030 Payroll expense factory labor Variable DL Product 29 Income Statemen 52230 Payroll expense-indirect labor Fixed MOH Product 30 Income Statemen 52310 Payroll expense office salary Fixed Admin Period 31 Income Statemen 52315 Employee benefits-office Fixed Admin Period 32 Income Statemen 52320 Payroll expense-sales salary Fixed Selling Period 33 Income Statemen 52321. Payroll expense-sales commission Variable Selling Period 34 Income Statemen 52325 Employee benefits - sales Variable Selling Period 35 Income Statemen 53010 Supply expense-Office Variable Admin Period 36 Income Statemen 53030 Supplies used-factory Variable MOH Product 37 Income Statemen 53130 Utilities - factory Variable MOH Product 38 Income Statemen 53110 Utilities - Office Variable Admin Period 39 Income Statemen 55010 Insurance expense-office Fixed Admin Period 40 Income Statemen 55020 Insurance expense. selling Fixed Selling Period 41 Income Statemen 55030 Insurance expense.factory Fixed MOH Product 42 Income Statemen 56010 Maintenance and repair expense-offic: Fixed Admin Period 43 Income Statemen 56020 Maintenance and repair expense-sellin Fixed Admin Period 44 Income Statemen 56030 maintenance expense and repair-facto Fixed MOH Product 45 Income Statemen 57010 Rent- office Fixed Admin Period 46 Income Statemen 57020 Rent - Sales Fixed Selling Period 47 Income Statemen 57030 Rent-factory Fixed MOH Product 48 Income Statemen 60020 Advertising Fixed Selling Period 49 Income Statemen 61010 Travel- office/CEO Fixed Admin Period 50 Income Statemen 61020 Travel-sales Variable Selling Period 51 Income Statemen 62010 Professional fees - Office Fixed Admin Period 52 Income Statemen 66010 Depreciation office equipment Fixed Admin Period 53 Income Statemen 66020 Depreciation-selling equipment Fixed Selling Period 54 Income Statemen 66030 Depreciation factory equipment Fixed MOH Product 55 Income Statemen 70000 Raw Material Variable DM Product A B D E G 1 2 Best Computer Company Inventory Master File Unit of Measure Current Inventory 2,000 20 Cost per Component 152.00 ea ea ea 30 30 ea ea ea 30 30 30 ea Part 3 Number Inventory Category Description 4 00100 Computer Case Coursair Obsidian 7500 CC ATX Full Tower Computer Case 5 00200 Computer, Complete Premium Desktop Gaming Computer 6 00210 Computer, Complete Premium Descktop Computer- Power Users 7 00300 Cooler Corsair Hydro Series H150i PRO 240mm Water/liquid CPU Cooler 8 00400 Graphics Card Nvidia GeForce RTC 2080 Ti Graph Card 11GB 9 00410 Graphics Card Nvidia GeForce GTX 970 Graphic Card 4 GB 10 00500 Memory G. SKILL TridentZ RGB Series 32GB SDRAM DDR4 3600 Memory G. SKILL TridentZ RGB Series 16GB SDRAM DDR4 3200 12 00600 Mother Board ASUS AMD AM4 ROG Strix Gaming ATX Motherboard 13 00610 Mother Board ASUS AM4 TUF Gaming X57--Plus ATX Motherboard with PCle 4.0 14 00700 Power Source Corsair RMx Series RM850x 850 Watt Power Source 15 00800 Processor AMD Ryzen 9 3900X 12 Core 3.8 GHz Processor 16 00820 Processor AMD Ryzen 7 3800X 8-Core 3.9 GHZ Processor w/Corsair Hydro water cooler 17 00900 SSD Drive Crucial MX500 2TB SATA III Internal Solid State Drive 18 00910 SSD Drive Crucial MX500 2TB SATA III Solid State Drive 19 10100 Labor Direct Labor 20 10200 Overhead Overhead 11 00510 ea 30 Selling Price 190.00 3,558.00 2,200.00 190.00 1,250.00 470.00 185.00 100.00 320.00 225.00 150.00 530.00 510.00 510.00 240.00 50.00 27.50 ea 30 30 ea 152.00 1,000.00 400.00 157.00 80.00 272.00 180.00 120.00 424.00 408.00 408.00 192.00 20.00 11.00 ea 30 ea ea 30 30 30 30 ea ea hr hr A B C D E F 1 Best Computer Company Premium Desktop Gaming Compter Product Cost 2 Total Product Cost Part 3 Number Description 4 00100 Coursair Obsidian 7500 CC ATX Full Tower Computer Case 5 00600 ASUS AMD AM4 ROG Strix Gaming ATX Motherboard 6 00800 AMD Ryzen 9 3900x 12 Core 3.8 GHz Processor 7 00300 Corsair Hydro Series H150i PRO 240mm Water/liquid CPU Cooler 8 00500 G.SKILL TridentZ RGB Series 32GB SDRAM DDR4 3600 9 00400 Nvidia GeForce RTC 2080 Ti Graph Card 11GB 10 00700 Corsair RMx Series RM850x 850 Watt Power Source 11 00900 Crucial MX500 2TB SATA III Internal Solid State Drive 12 Total Materials 13 10100 Direct Labor 14 10200 Overhead 15 00200 Total Cost per Unit 16 Unit of Units Cost per Measure Required Component ea 1.00 ea 1.00 ea 1.00 ea 1.00 ea 1.00 ea 1.00 ea 1.00 ea 1.00 hr 3.00 hr 3.00 17 18 Best Computer Company Premium Descktop Computer-Power Users 19 20 Part 21 Number 22 00100 23 00610 24 00820 25 00510 26 00410 27 00700 28 00910 29 30 10100 31 10200 Unit of Units Cost per Measure Required Component ea 1.00 ea 1.00 ea 1.00 ea 1.00 ea Description Coursair Obsidian 7500 CC ATX Full Tower Computer Case ASUS AM4 TUF Gaming X57--Plus ATX Motherboard with PCIe 4.0 AMD Ryzen 7 3800X 8-Core 3.9 GHZ Processor w/Corsair Hydro water cooler G.SKILL TridentZ RGB Series 16GB SDRAM DDR4 3200 Nvidia GeForce GTX 970 Graphic Card 4 GB Corsair RMx Series RM850x 850 Watt Power Source Crucial MX500 2TB SATA III Solid State Drive Total Materials Direct Labor Overhead Total Cost per Unit ea 1.00 2.00 1.00 ea hr 2.00 hr 2.00 32 00210 B D E F 2 . 1 Balance sheet 2 3 4 Cash 5 AR 6 Supplies 7 Inventory RM 8 Inventory WIP 9 Inventory finished goods 10 Equipment-office 11 Accumulated depreciation 12 Equipment-sales 13 Accumulated depreciation 14 Equipment-factory 15 Accumulated depreciation 16 17 18 AP 19 Wage payable 2/1/2020 $ 200,000.00 1,000,000.00 20,000.00 75,860.00 140,000.00 179,680.00 15,000.00 (3,250.00) 10,000.00 (2,166.67) 75,000.00 (16,250.00) $ 1,693,873.33 2/29/2020 $ 1,595,309.73 1,487,096.25 20,000.00 296,190.00 120,000.00 106,980.00 15,000.00 (3,500.00) 10,000.00 (2,333.33) 75,000.00 (21,250.00) $ 3,698,492.65 50,000.00 250,000.00 46,250.00 250,000.00 20 21 Stockholder equity 1,393,873.33 22 Total liability and stockholder equit $ 1,693,873.33 3,402,242.65 $ 3,698,492.65 B D E A B 1 For the month of Febrary of 2020 2 3 4 52030 Payroll expense- factory labor 5 52230 Payroll expense-indirect labor 6 52310 Payroll expense-office salary 7 52315 Employee benefits-office 8 52320 Payroll expense-sales salary 9 52321 Payroll expense-sales commission 10 52325 Employee benefits - sales 11 53010 Supply expense-office 12 53030 Supplies used-factory 13 53110 Utilities - office 14 53130 Utilities - factory 15 55010 Insurance expense-office 16 55030 Insurance expense - factory 17 55020 Insurance expense- selling 18 56010 Maintenance and repair expense- office 19 56030 maintenance expense and repair- factory 20 57010 Rent- office 21 57020 Rent - sales 22 57030 Rent-factory 23 60020 Advertising 24 61010 Travel- office/CEO 25 61020 Travel- sales 26 62010 Professional fees - office 27 66010 Depreciation-office equipment 28 66020 Depreciation-selling equipment 29 66030 Depreciation-factory equipment $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 224,420 20,000 4,000 6,600 8,000 256,858 8,000 200 6,000 150 8,000 450 2,000 500 250 5,000 1,000 1,200 80,000 1,000 2,000 5,000 2,000 250 167 5,000 30 D 9,932,477 A B 1 Schedule of Cost of Good Manufactured 2 2 3 Direct Material 4 Beginning balance 5 purchase 6 Raw material available for use 7 Less ending balance of raw material 8 Direct Material used 9 10 Direct labor 11 12 Factory Overhead B E F C Best Computer Company 1 2 Accounting policies -EEEEEE 4 Category Account numb: Account Description Type Classification Product/Period 5 Balance Sheet 10100 Cash and Cash equivalents Assets Current Assets NIA 6 Balance Sheet 10200 Short term investments Assets Current Assets N/A 7 Balance Sheet 10300 Accounts Receivable Assets Current Assets NIA Balance Sheet 10400 Office supplies Assets Current Assets N/A 9 Balance Sheet 11130 Inventories. raw material Assets Current Assets NIA 10 Balance Sheet 11230 Inventories-work in process Assets Current Assets N/A 11 Balance Sheet 11330 Inventories. Finished goods Assets Current Assets NIA 12 Balance Sheet 16000 Plant Assets Long term asse NIA 13 Balance Sheet 16110 Equipment office Assets Long term asse NIA 14 Balance Sheet 16111 Accumulated depreciation_office equil Assets Long term asse NIA 15 Balance Sheet 16120 Equipment-selling Assets Long term asse NIA 16 Balance Sheet 16121 accumulated depreciation_selling equil Assets Long term asse NIA 17 Balance Sheet 16130 Equipment factory Assets Long term asse NIA 18 Balance Sheet 16131 accumulated depreciation_factory equ Assets Long term asse NIA 19 Balance Sheet 18000 Other fixed assets Assets Long term asse N/A 20 Balance Sheet 20100 Accounts payable Liability Current Liability NIA 21 Balance Sheet 20200 Wages payable Liability Current Liability NIA 22 Balance Sheet 20300 Payroll tax payable Liability Current Liability NIA 23 Balance Sheet 20400 Income tax payable Liability Current Liability NIA 24 Balance Sheet 31000 Common Stock Euqity Equity NIA 25 Balance Sheet 32000 Retained Earnings Euqity Equity NIA 26 Income Statemen 41000 Product sales Revenue Revenue NIA 27 Income Statemen 51000 Cost of good sold CGS CGS product 28 Income Statemen 52030 Payroll expense factory labor Variable DL Product 29 Income Statemen 52230 Payroll expense-indirect labor Fixed MOH Product 30 Income Statemen 52310 Payroll expense office salary Fixed Admin Period 31 Income Statemen 52315 Employee benefits-office Fixed Admin Period 32 Income Statemen 52320 Payroll expense-sales salary Fixed Selling Period 33 Income Statemen 52321. Payroll expense-sales commission Variable Selling Period 34 Income Statemen 52325 Employee benefits - sales Variable Selling Period 35 Income Statemen 53010 Supply expense-Office Variable Admin Period 36 Income Statemen 53030 Supplies used-factory Variable MOH Product 37 Income Statemen 53130 Utilities - factory Variable MOH Product 38 Income Statemen 53110 Utilities - Office Variable Admin Period 39 Income Statemen 55010 Insurance expense-office Fixed Admin Period 40 Income Statemen 55020 Insurance expense. selling Fixed Selling Period 41 Income Statemen 55030 Insurance expense.factory Fixed MOH Product 42 Income Statemen 56010 Maintenance and repair expense-offic: Fixed Admin Period 43 Income Statemen 56020 Maintenance and repair expense-sellin Fixed Admin Period 44 Income Statemen 56030 maintenance expense and repair-facto Fixed MOH Product 45 Income Statemen 57010 Rent- office Fixed Admin Period 46 Income Statemen 57020 Rent - Sales Fixed Selling Period 47 Income Statemen 57030 Rent-factory Fixed MOH Product 48 Income Statemen 60020 Advertising Fixed Selling Period 49 Income Statemen 61010 Travel- office/CEO Fixed Admin Period 50 Income Statemen 61020 Travel-sales Variable Selling Period 51 Income Statemen 62010 Professional fees - Office Fixed Admin Period 52 Income Statemen 66010 Depreciation office equipment Fixed Admin Period 53 Income Statemen 66020 Depreciation-selling equipment Fixed Selling Period 54 Income Statemen 66030 Depreciation factory equipment Fixed MOH Product 55 Income Statemen 70000 Raw Material Variable DM Product A B D E G 1 2 Best Computer Company Inventory Master File Unit of Measure Current Inventory 2,000 20 Cost per Component 152.00 ea ea ea 30 30 ea ea ea 30 30 30 ea Part 3 Number Inventory Category Description 4 00100 Computer Case Coursair Obsidian 7500 CC ATX Full Tower Computer Case 5 00200 Computer, Complete Premium Desktop Gaming Computer 6 00210 Computer, Complete Premium Descktop Computer- Power Users 7 00300 Cooler Corsair Hydro Series H150i PRO 240mm Water/liquid CPU Cooler 8 00400 Graphics Card Nvidia GeForce RTC 2080 Ti Graph Card 11GB 9 00410 Graphics Card Nvidia GeForce GTX 970 Graphic Card 4 GB 10 00500 Memory G. SKILL TridentZ RGB Series 32GB SDRAM DDR4 3600 Memory G. SKILL TridentZ RGB Series 16GB SDRAM DDR4 3200 12 00600 Mother Board ASUS AMD AM4 ROG Strix Gaming ATX Motherboard 13 00610 Mother Board ASUS AM4 TUF Gaming X57--Plus ATX Motherboard with PCle 4.0 14 00700 Power Source Corsair RMx Series RM850x 850 Watt Power Source 15 00800 Processor AMD Ryzen 9 3900X 12 Core 3.8 GHz Processor 16 00820 Processor AMD Ryzen 7 3800X 8-Core 3.9 GHZ Processor w/Corsair Hydro water cooler 17 00900 SSD Drive Crucial MX500 2TB SATA III Internal Solid State Drive 18 00910 SSD Drive Crucial MX500 2TB SATA III Solid State Drive 19 10100 Labor Direct Labor 20 10200 Overhead Overhead 11 00510 ea 30 Selling Price 190.00 3,558.00 2,200.00 190.00 1,250.00 470.00 185.00 100.00 320.00 225.00 150.00 530.00 510.00 510.00 240.00 50.00 27.50 ea 30 30 ea 152.00 1,000.00 400.00 157.00 80.00 272.00 180.00 120.00 424.00 408.00 408.00 192.00 20.00 11.00 ea 30 ea ea 30 30 30 30 ea ea hr hr A B C D E F 1 Best Computer Company Premium Desktop Gaming Compter Product Cost 2 Total Product Cost Part 3 Number Description 4 00100 Coursair Obsidian 7500 CC ATX Full Tower Computer Case 5 00600 ASUS AMD AM4 ROG Strix Gaming ATX Motherboard 6 00800 AMD Ryzen 9 3900x 12 Core 3.8 GHz Processor 7 00300 Corsair Hydro Series H150i PRO 240mm Water/liquid CPU Cooler 8 00500 G.SKILL TridentZ RGB Series 32GB SDRAM DDR4 3600 9 00400 Nvidia GeForce RTC 2080 Ti Graph Card 11GB 10 00700 Corsair RMx Series RM850x 850 Watt Power Source 11 00900 Crucial MX500 2TB SATA III Internal Solid State Drive 12 Total Materials 13 10100 Direct Labor 14 10200 Overhead 15 00200 Total Cost per Unit 16 Unit of Units Cost per Measure Required Component ea 1.00 ea 1.00 ea 1.00 ea 1.00 ea 1.00 ea 1.00 ea 1.00 ea 1.00 hr 3.00 hr 3.00 17 18 Best Computer Company Premium Descktop Computer-Power Users 19 20 Part 21 Number 22 00100 23 00610 24 00820 25 00510 26 00410 27 00700 28 00910 29 30 10100 31 10200 Unit of Units Cost per Measure Required Component ea 1.00 ea 1.00 ea 1.00 ea 1.00 ea Description Coursair Obsidian 7500 CC ATX Full Tower Computer Case ASUS AM4 TUF Gaming X57--Plus ATX Motherboard with PCIe 4.0 AMD Ryzen 7 3800X 8-Core 3.9 GHZ Processor w/Corsair Hydro water cooler G.SKILL TridentZ RGB Series 16GB SDRAM DDR4 3200 Nvidia GeForce GTX 970 Graphic Card 4 GB Corsair RMx Series RM850x 850 Watt Power Source Crucial MX500 2TB SATA III Solid State Drive Total Materials Direct Labor Overhead Total Cost per Unit ea 1.00 2.00 1.00 ea hr 2.00 hr 2.00 32 00210 B D E F 2 . 1 Balance sheet 2 3 4 Cash 5 AR 6 Supplies 7 Inventory RM 8 Inventory WIP 9 Inventory finished goods 10 Equipment-office 11 Accumulated depreciation 12 Equipment-sales 13 Accumulated depreciation 14 Equipment-factory 15 Accumulated depreciation 16 17 18 AP 19 Wage payable 2/1/2020 $ 200,000.00 1,000,000.00 20,000.00 75,860.00 140,000.00 179,680.00 15,000.00 (3,250.00) 10,000.00 (2,166.67) 75,000.00 (16,250.00) $ 1,693,873.33 2/29/2020 $ 1,595,309.73 1,487,096.25 20,000.00 296,190.00 120,000.00 106,980.00 15,000.00 (3,500.00) 10,000.00 (2,333.33) 75,000.00 (21,250.00) $ 3,698,492.65 50,000.00 250,000.00 46,250.00 250,000.00 20 21 Stockholder equity 1,393,873.33 22 Total liability and stockholder equit $ 1,693,873.33 3,402,242.65 $ 3,698,492.65 B D E A B 1 For the month of Febrary of 2020 2 3 4 52030 Payroll expense- factory labor 5 52230 Payroll expense-indirect labor 6 52310 Payroll expense-office salary 7 52315 Employee benefits-office 8 52320 Payroll expense-sales salary 9 52321 Payroll expense-sales commission 10 52325 Employee benefits - sales 11 53010 Supply expense-office 12 53030 Supplies used-factory 13 53110 Utilities - office 14 53130 Utilities - factory 15 55010 Insurance expense-office 16 55030 Insurance expense - factory 17 55020 Insurance expense- selling 18 56010 Maintenance and repair expense- office 19 56030 maintenance expense and repair- factory 20 57010 Rent- office 21 57020 Rent - sales 22 57030 Rent-factory 23 60020 Advertising 24 61010 Travel- office/CEO 25 61020 Travel- sales 26 62010 Professional fees - office 27 66010 Depreciation-office equipment 28 66020 Depreciation-selling equipment 29 66030 Depreciation-factory equipment $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 224,420 20,000 4,000 6,600 8,000 256,858 8,000 200 6,000 150 8,000 450 2,000 500 250 5,000 1,000 1,200 80,000 1,000 2,000 5,000 2,000 250 167 5,000 30 D 9,932,477 A B 1 Schedule of Cost of Good Manufactured 2 2 3 Direct Material 4 Beginning balance 5 purchase 6 Raw material available for use 7 Less ending balance of raw material 8 Direct Material used 9 10 Direct labor 11 12 Factory Overhead