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 VLOOKUP 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 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-up function and referencing the part number given. Using the SumIf 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 Daily 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 numbers second digit (10^th 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 VLookup 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.

Best Computer Company Accounting policies Best Computer Company Accounting policies \begin{tabular}{|l|l|l|l|l|} \hline Income Statements & 62010 Professional fees - office & Fixed & Admin & Period \\ \hline Income Statements & 66010 Depreciation- office equipment & Fixed & Admin \\ \hline Income Statements & 66020 Depreciation- selling equipment & Fixed & Selling \\ \hline Income Statements & 66030 Depreciation- factory equipment & Period \\ \hline Income Statements & 70000 Raw Material & Variable & DM & Product \\ \hline \end{tabular} Best Computer Company Inventory Master File Best Computer Company Premium Desktop Gaming Compter Product Cost Best Computer Company Premium Descktop Computer- Power Users Best Computer Company Daily Sales \begin{tabular}{l|r|r|r|} \hline Computer, Complete & Premium Descktop Computer- Power Users & 56 & ea \\ \hline Computer, Complete & Premium Desktop Gaming Computer & 141 & ea \\ \hline Computer, Complete & Premium Descktop Computer- Power Users & 69 & ea \\ \hline Computer, Complete & Premium Desktop Gaming Computer & 144 & ea \\ \hline Computer, Complete & Premium Descktop Computer- Power Users & 101 & ea \\ \hline Computer, Complete & Premium Desktop Gaming Computer & 83 & ea \\ \hline Computer, Complete & Premium Descktop Computer- Power Users & 35 & ea \\ \hline Computer, Complete & Premium Desktop Gaming Computer & 138 & ea \\ \hline Computer, Complete & Premium Descktop Computer- Power Users & 86 & ea \\ \hline Computer, Complete & Premium Desktop Gaming Computer & 86 & ea \\ \hline Computer, Complete & Premium Descktop Computer- Power Users & 23 & ea \\ \hline Computer, Complete & Premium Desktop Gaming Computer & 143 & ea \\ \hline Computer, Complete & Premium Descktop Computer- Power Users & 122 & ea \\ \hline Computer, Complete & Premium Desktop Gaming Computer & 144 & ea \\ \hline Computer, Complete & Premium Descktop Computer- Power Users & 98 ea \\ \hline Computer, Complete & Premium Desktop Gaming Computer & 30 ea \\ \hline Computer, Complete & Premium Descktop Computer- Power Users & 120 & ea \\ \hline Computer, Complete & Premium Desktop Gaming Computer & 86 & ea \\ \hline Computer, Complete & Premium Descktop Computer- Power Users & 129 & ea \\ \hline Computer, Complete & Premium Desktop Gaming Computer & 45 & ea \\ \hline Computer, Complete & Premium Descktop Computer- Power Users & 119 & ea \\ \hline Computer, Complete & Premium Desktop Gaming Computer & 18 & ea \\ \hline Computer, Complete & Premium Descktop Computer- Power Users & 99 & ea \end{tabular} Best Computer Company Balance sheet \begin{tabular}{|l|rr|rr|} \hline AP & $ & 50,000 & $ & 46,250 \\ \hline Wage payable & $ & 250,000 & $ & 250,000 \\ \hline Stockholder equity & & & & \\ \hline Total liability and stockholder equity & $ & 1,393,873 & $ & 3,315,243 \\ \hline & $ & 1,693,873 & $ & 3,611,493 \\ \hline \hline \end{tabular} For the month of Febrary of 2023 Schedule of Cost of Good Manufactured Direct Material Beginning balance purchase 9,932,477 Raw material available for use Less ending balance of raw material Direct Material used Direct labor Factory Overhead Best Computer Company Accounting policies Best Computer Company Accounting policies \begin{tabular}{|l|l|l|l|l|} \hline Income Statements & 62010 Professional fees - office & Fixed & Admin & Period \\ \hline Income Statements & 66010 Depreciation- office equipment & Fixed & Admin \\ \hline Income Statements & 66020 Depreciation- selling equipment & Fixed & Selling \\ \hline Income Statements & 66030 Depreciation- factory equipment & Period \\ \hline Income Statements & 70000 Raw Material & Variable & DM & Product \\ \hline \end{tabular} Best Computer Company Inventory Master File Best Computer Company Premium Desktop Gaming Compter Product Cost Best Computer Company Premium Descktop Computer- Power Users Best Computer Company Daily Sales \begin{tabular}{l|r|r|r|} \hline Computer, Complete & Premium Descktop Computer- Power Users & 56 & ea \\ \hline Computer, Complete & Premium Desktop Gaming Computer & 141 & ea \\ \hline Computer, Complete & Premium Descktop Computer- Power Users & 69 & ea \\ \hline Computer, Complete & Premium Desktop Gaming Computer & 144 & ea \\ \hline Computer, Complete & Premium Descktop Computer- Power Users & 101 & ea \\ \hline Computer, Complete & Premium Desktop Gaming Computer & 83 & ea \\ \hline Computer, Complete & Premium Descktop Computer- Power Users & 35 & ea \\ \hline Computer, Complete & Premium Desktop Gaming Computer & 138 & ea \\ \hline Computer, Complete & Premium Descktop Computer- Power Users & 86 & ea \\ \hline Computer, Complete & Premium Desktop Gaming Computer & 86 & ea \\ \hline Computer, Complete & Premium Descktop Computer- Power Users & 23 & ea \\ \hline Computer, Complete & Premium Desktop Gaming Computer & 143 & ea \\ \hline Computer, Complete & Premium Descktop Computer- Power Users & 122 & ea \\ \hline Computer, Complete & Premium Desktop Gaming Computer & 144 & ea \\ \hline Computer, Complete & Premium Descktop Computer- Power Users & 98 ea \\ \hline Computer, Complete & Premium Desktop Gaming Computer & 30 ea \\ \hline Computer, Complete & Premium Descktop Computer- Power Users & 120 & ea \\ \hline Computer, Complete & Premium Desktop Gaming Computer & 86 & ea \\ \hline Computer, Complete & Premium Descktop Computer- Power Users & 129 & ea \\ \hline Computer, Complete & Premium Desktop Gaming Computer & 45 & ea \\ \hline Computer, Complete & Premium Descktop Computer- Power Users & 119 & ea \\ \hline Computer, Complete & Premium Desktop Gaming Computer & 18 & ea \\ \hline Computer, Complete & Premium Descktop Computer- Power Users & 99 & ea \end{tabular} Best Computer Company Balance sheet \begin{tabular}{|l|rr|rr|} \hline AP & $ & 50,000 & $ & 46,250 \\ \hline Wage payable & $ & 250,000 & $ & 250,000 \\ \hline Stockholder equity & & & & \\ \hline Total liability and stockholder equity & $ & 1,393,873 & $ & 3,315,243 \\ \hline & $ & 1,693,873 & $ & 3,611,493 \\ \hline \hline \end{tabular} For the month of Febrary of 2023 Schedule of Cost of Good Manufactured Direct Material Beginning balance purchase 9,932,477 Raw material available for use Less ending balance of raw material Direct Material used Direct labor Factory Overhead