Product Costs Worksheet: Step 1: Use the "Part Number" as the reference and V look-up function to bring the "Cost per Component" information from the "Inventory Master File" worksheet. For reference on how to write the VLOOKUP function, please see the link at "Vlookup function from MS Excel" (Hint: you may also want to use an absolute reference to lock the range referenced in the VLQ@KUP. 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 NI a message from Vlookup." Step 2: Calculate the total material cost and total cost per computer unit. You can add more columns as needed (for example, a column called "Total Product Cost," etc.) Daily Sales Worksheet: Step 1: Calculate the daily sales by bringing over the selling price from the "Inventory Master File" using the Vlook-ug function and referencing the "part number" given. Using the Sumlf function to total the units and the dollar sales for each type of computer. Display the results at the bottom of the spreadsheet. Please visit the link if you are unsure about The Sumif function." Step 2: To double-check your answers from step one, create 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. For reference on how to build a pivot table, please follow the link: "Learn Pivot Table in 6 minutes from Microsoft Excel". The pivot table should appear on the Dally Sales Worksheet below the Sumif calculations. Cost of Good Manufactured (sch cgm) worksheet: The income data (inc data) worksheet is provided and sorted by the account name. You will need to re-sort the accounts numbers by function (office, sales, and factory) to complete the cost of goods manufactured and income statements by following the steps below: Step 1: Create a new blank worksheet. Copy the income data to the blank sheet because the original sheet is protected. Name this worksheet an "inc data sorting" worksheet. You can work out all your numbers here without changing your original numbers. The quick way to re-sort is by utilizing the excel function called "text to columns". (Hint: The account number's second digit (10th digit) represents the functional difference. Use the "text to columns\fixed width" function to split the account number into three groups. Then you can use the sorting function to sort the second digit. For reference on how to use the "text to columns, see link "Text to Columns- Excel 2019" Step 2: 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). (Please see the image following the rest of the instructions for the proper format.) Step 3: Calculate the cost of the goods manufactured for February. Cost of Goods Sold Worksheet: Set up another blank worksheet named as "CGS". Using the Cost of Goods Manufactured calculated in your 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 to build a Multi-step Income Statement by linking all G/A expenses and Sales expenses from the "inc data sorting" worksheet previously completed. Refer to the sorting related steps 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 properly set up the income statement to print out on a single page in good form. Company-wide Contribution Margin Statements Worksheet - Variable Costing Method (no product line allocation needed): Step 1: Use "move or copy" function to create a copy of the "Chart of Accounts" worksheet. Name it "CA sorting". Step 2: Use the "sort" or "filter" function within "CA sorting" to sort accounts by "type" to identify all variable and fixed expenses. Step 3: Set up a new worksheet called "Margin sup." Calculate the total product material variable cost referencing the "product cost" worksheet and "daily sales" worksheet. Step 4: Continue within "Margin sup". Using Vtookup bring the account balance from the "inc data" sheet by referencing account number. Sum the total fixed and variable costs (not including the variable material cost). Step 5: Set up a worksheet named "margin". This is your worksheet for the final Contribution Margin Income Statement. Link the total sales revenue from the "Daily Sales" worksheet, and link the "total variable cost" from the "Margin sup" worksheet. Complete the rest of the income statements based on the proper format. Step 6: 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 7: Make sure you have correctly set up the income statement to print out on a single page in good form. Product Costs Worksheet: Step 1: Use the "Part Number" as the reference and V look-up function to bring the "Cost per Component" information from the "Inventory Master File" worksheet. For reference on how to write the VLOOKUP function, please see the link at "Vlookup function from MS Excel" (Hint: you may also want to use an absolute reference to lock the range referenced in the VLQ@KUP. 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 NI a message from Vlookup." Step 2: Calculate the total material cost and total cost per computer unit. You can add more columns as needed (for example, a column called "Total Product Cost," etc.) Daily Sales Worksheet: Step 1: Calculate the daily sales by bringing over the selling price from the "Inventory Master File" using the Vlook-ug function and referencing the "part number" given. Using the Sumlf function to total the units and the dollar sales for each type of computer. Display the results at the bottom of the spreadsheet. Please visit the link if you are unsure about The Sumif function." Step 2: To double-check your answers from step one, create 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. For reference on how to build a pivot table, please follow the link: "Learn Pivot Table in 6 minutes from Microsoft Excel". The pivot table should appear on the Dally Sales Worksheet below the Sumif calculations. Cost of Good Manufactured (sch cgm) worksheet: The income data (inc data) worksheet is provided and sorted by the account name. You will need to re-sort the accounts numbers by function (office, sales, and factory) to complete the cost of goods manufactured and income statements by following the steps below: Step 1: Create a new blank worksheet. Copy the income data to the blank sheet because the original sheet is protected. Name this worksheet an "inc data sorting" worksheet. You can work out all your numbers here without changing your original numbers. The quick way to re-sort is by utilizing the excel function called "text to columns". (Hint: The account number's second digit (10th digit) represents the functional difference. Use the "text to columns\fixed width" function to split the account number into three groups. Then you can use the sorting function to sort the second digit. For reference on how to use the "text to columns, see link "Text to Columns- Excel 2019" Step 2: 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). (Please see the image following the rest of the instructions for the proper format.) Step 3: Calculate the cost of the goods manufactured for February. Cost of Goods Sold Worksheet: Set up another blank worksheet named as "CGS". Using the Cost of Goods Manufactured calculated in your 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 to build a Multi-step Income Statement by linking all G/A expenses and Sales expenses from the "inc data sorting" worksheet previously completed. Refer to the sorting related steps 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 properly set up the income statement to print out on a single page in good form. Company-wide Contribution Margin Statements Worksheet - Variable Costing Method (no product line allocation needed): Step 1: Use "move or copy" function to create a copy of the "Chart of Accounts" worksheet. Name it "CA sorting". Step 2: Use the "sort" or "filter" function within "CA sorting" to sort accounts by "type" to identify all variable and fixed expenses. Step 3: Set up a new worksheet called "Margin sup." Calculate the total product material variable cost referencing the "product cost" worksheet and "daily sales" worksheet. Step 4: Continue within "Margin sup". Using Vtookup bring the account balance from the "inc data" sheet by referencing account number. Sum the total fixed and variable costs (not including the variable material cost). Step 5: Set up a worksheet named "margin". This is your worksheet for the final Contribution Margin Income Statement. Link the total sales revenue from the "Daily Sales" worksheet, and link the "total variable cost" from the "Margin sup" worksheet. Complete the rest of the income statements based on the proper format. Step 6: 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 7: Make sure you have correctly set up the income statement to print out on a single page in good form