Project General Information and Background In this project you are working with cost and financial information for a small computer manufacturer. The company manufacturers only two premium computers. One targets the high-end gamers market and the other targets power users who crunch a lot of data. You will work with cost data and financial information ultimately to prepare financial statements. During the project you will be required to demonstrate good Excel skills. All reports must be formatted so that they will readily print in good format. Guidelines: - There is information provided in these instructions that give you links to information on how to perform some of the Excel functions that are necessary for this project. Other excel function tutorial are available through your instructor D2L site, Microsoft Excel support or many YouTube videos. - Before you start working on the excel file posted on D2L, you need to download a copy of the excel file. Please save it as "ACCG 211 Excel Project 2022 Last Name". Make sure to save it in a place where you can easily locate from your computer. - This project requires that everything be submitted in a single Excel file using the template provided with separate worksheets. Each worksheet should be formatted so that all columns print on a single page with proper headings. If the worksheet will print on more than one page you will need to make sure that the headings print on every page. - All cells that depend on other data must be linked to the original cell. - Where calculations are required, you must do those calculations in Excel. All calculations should be rounded to the nearest dollar. - The numbers must be formatted in the comma style and where appropriate the dollar style. - Your final project must be uploaded in D2L under Assessments>>Assignments. 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 Fils"." worksheet. 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 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 each computer unit. Feel free to add columns as needed (for example, you should add a column called "Total Product Cost") Daily Sales Worksheet: Step 1: Calculate the daily sales by bring over the selling price from the "Inventory Master File" by Using the Vlook-up function and by 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. If you are not sure about The Sumif function, please visit the link "how to use 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 and its balance by function (office, sales, and factory) to complete the eoct of annds manufactured and income ctatements hu followino the stenc helow. Cost of Good Manufactured (sch cgm) worksheet: The income data (inc data) worksheet is provided and sorjed by the account name. You will need to re-sort the accounts and its balance 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 work sheet. Use the copy and paste function to get the data from the "Inc data" because the original sheet is protected. Name this worksheet as "inc data sorting" work sheet. You can work out all your numbers in here without changing your original numbers. The quick way to re-sort is 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 the month of February. Cost of Goods Sold Worksheet: Step 1: 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. 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". Clean up the worksheet by deleting all balance sheet accounts not needed. You should only use the accounts in income statement sorted by "type". Using VLookup bring the account balance from the "inc data" sheet by referencing account number. Sum the total fixed cost and total variable cost (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" work sheet. 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 that you have properly set up the income statement to print out on a single page in good form. Best Computer Company 1. Sett Contruter Company 2 itiventory Master File Best Computer Company Dally Sales Best Computer Company 1 For the month of Febrary of 2021 \begin{tabular}{l|l|l|l|l|l|l|l|} \hline.. Inventory Master File. inventoryworkingfile Product Cost & Daily Sales bal Inc data Sheet4 sch cgm \end{tabular} 1 Schedule of Cost of Good Manufactured 3 Direct Material 4 Beginning balance \begin{tabular}{ll|l|} 5 & purchase & 9,932,477 \\ \hline 6 & Raw material available for use \\ \hline 7 & Less ending balance of raw material \\ \hline \end{tabular} 7 Less ending balance of raw material 8 Direct Material used 9 10 Direct labor 11 12 Factory Overhead 13 15 16 17 19 20 21 21 22 23 23 25 Project General Information and Background In this project you are working with cost and financial information for a small computer manufacturer. The company manufacturers only two premium computers. One targets the high-end gamers market and the other targets power users who crunch a lot of data. You will work with cost data and financial information ultimately to prepare financial statements. During the project you will be required to demonstrate good Excel skills. All reports must be formatted so that they will readily print in good format. Guidelines: - There is information provided in these instructions that give you links to information on how to perform some of the Excel functions that are necessary for this project. Other excel function tutorial are available through your instructor D2L site, Microsoft Excel support or many YouTube videos. - Before you start working on the excel file posted on D2L, you need to download a copy of the excel file. Please save it as "ACCG 211 Excel Project 2022 Last Name". Make sure to save it in a place where you can easily locate from your computer. - This project requires that everything be submitted in a single Excel file using the template provided with separate worksheets. Each worksheet should be formatted so that all columns print on a single page with proper headings. If the worksheet will print on more than one page you will need to make sure that the headings print on every page. - All cells that depend on other data must be linked to the original cell. - Where calculations are required, you must do those calculations in Excel. All calculations should be rounded to the nearest dollar. - The numbers must be formatted in the comma style and where appropriate the dollar style. - Your final project must be uploaded in D2L under Assessments>>Assignments. 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 Fils"." worksheet. 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 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 each computer unit. Feel free to add columns as needed (for example, you should add a column called "Total Product Cost") Daily Sales Worksheet: Step 1: Calculate the daily sales by bring over the selling price from the "Inventory Master File" by Using the Vlook-up function and by 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. If you are not sure about The Sumif function, please visit the link "how to use 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 and its balance by function (office, sales, and factory) to complete the eoct of annds manufactured and income ctatements hu followino the stenc helow. Cost of Good Manufactured (sch cgm) worksheet: The income data (inc data) worksheet is provided and sorjed by the account name. You will need to re-sort the accounts and its balance 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 work sheet. Use the copy and paste function to get the data from the "Inc data" because the original sheet is protected. Name this worksheet as "inc data sorting" work sheet. You can work out all your numbers in here without changing your original numbers. The quick way to re-sort is 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 the month of February. Cost of Goods Sold Worksheet: Step 1: 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. 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". Clean up the worksheet by deleting all balance sheet accounts not needed. You should only use the accounts in income statement sorted by "type". Using VLookup bring the account balance from the "inc data" sheet by referencing account number. Sum the total fixed cost and total variable cost (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" work sheet. 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 that you have properly set up the income statement to print out on a single page in good form. Best Computer Company 1. Sett Contruter Company 2 itiventory Master File Best Computer Company Dally Sales Best Computer Company 1 For the month of Febrary of 2021 \begin{tabular}{l|l|l|l|l|l|l|l|} \hline.. Inventory Master File. inventoryworkingfile Product Cost & Daily Sales bal Inc data Sheet4 sch cgm \end{tabular} 1 Schedule of Cost of Good Manufactured 3 Direct Material 4 Beginning balance \begin{tabular}{ll|l|} 5 & purchase & 9,932,477 \\ \hline 6 & Raw material available for use \\ \hline 7 & Less ending balance of raw material \\ \hline \end{tabular} 7 Less ending balance of raw material 8 Direct Material used 9 10 Direct labor 11 12 Factory Overhead 13 15 16 17 19 20 21 21 22 23 23 25