Part 1:

Perform the necessary calculations as followed:

Download from SimNet: CSE 2111 Pre Lab 3 Computer Budget Master.xlsx CSE 2111 Pre Lab 3 Candidates Master.alsx YOU MUST DOWNLOAD YOUR STARTING FILES FROM YOUR SIMNET SESSION AND SUBMIT YOUR COMPLETED STARTING FILES TO YOUR SIMNET SESSION OR SIMNET WILL BLOCK YOUR SUBMISSION. PART 1: BUYING A COMPUTER There is money left over in the corporate capital budget and each department has been asked to price out a computer system they would like. The corporation has in mind a budget of $2,000 per department - though some may be able to so over if overall the corporation is still within budget. in order to determine if this equipinent can be purchased, you have created an Excel workbook named CSE 2111 Pre Lab3 Computer budget Master. xls . This spreadsheet contains the prices by department for each of the desired components - monitors, computers, soltware, junip drives, printers and additional memory. In addition you have categorized these costs based on where these isems/services will be acquired: Dell, Microcenter or CompUSA. The vendors are pre-determined based on existing, contractual agreements. You will now use this information to perform an analysis. Follow the steps as described beiow. Open the file, CSE 2111 Pre tab 3 Computer Badget Master, wisk Perform the necessary calculations as follows: - Determine the total costs for each computer system - in Cell cg, determine the total cort for the Ingineering. Group. Copy your formula across the row into cells D9:G9. - Compare the total cost of acquiring a system to the department's bueget (cell b1). - Write a formula in cell C11, which can be copied across the row, to determine how much over of under budget you would be if you purchased this system. Write the formula such that if you are over bodget this value will be negative. (Hint: do not forget to properly reference cells so that it can be copied across the fow.) - Write a formula, in cell B12, to automatically determine how many of the department systems are within your budget. (This formula should work even if any of the input costs are iater modified) - The corporation may decide to only fund either the Engineering Group or the R\&D (Research and Development) Group. Originally their decision was to fund the Engineering Group only bot now you hear that they might decide to fund R\&D instead. You just know someone will ask you who's cost miore and by how much on a percentage basis. Thus, in Cell 813, write a formula that shows the percent difference in total costs between the original choice - Engineering, and the alternative option - P\&D's systern - Determine the total costs by vendor - Write a formula in cell C16, that can be copled down and ocross into cells Cl6:618, to determine how much of the costs of this department's equipment are paid to this vendor. For example in cell Cl6 would be the costs for the Engineering Group's system putchased from Microcenter. PART 2: UNIVERSITY ADMISSIONS You work for a University admission comimittee that evaluates applicants. This fle contains the names and scores of applicant candidates including the Application Type (regular or tranffert. Verbal, Math SAT scores and 5cience SAT II scares. You have been asked to calculate a combined weighted score for each candidate, the combined average, the minimum and maximum scores for the applicant group, and summarise some statistics by application type. To complete this aroblem, use the following instructions: Open the file CSE 2111 Pre tab 3 Candidates Master.xlsx. Complete the spreadsheet answering the questions as follows: - In cell Fa write an Excel formula (use cell references whenever possible) which can be copied down into cells FS.f9 that calculates the weighted score of Mary Keck's test values. The weights of each test are given in row 2. So her final weighted score will be a sum of each score multipled by the weight of that score as follows: - Verbal 5AT score (680) * Verbal SAT score weight (20%)+ Math 5 AT score (670) * Math SAT score weight (40%)+ SCI SAT II score (580) * SCI SAT 1 score weight (409). This formula should be copied down the column to calculate the scares for the other applicants. - Write a formula in cell Clo that will calculate the average value of the entire applicant group's Verbal SAT scores. Copy the formula across to cells D10.F10 to calculate the average for each of the correspond ing scare categories. - Write a formula in cell C11 that will calculate the lowest score receved by an applicant on the Verbal SATs. Copy the formula across to cells D11:F11 to determine the fowert store for the corresponding category. - Write a formula in cell c12 that will calculate the highest score received by an applicant on the Verbal 5 ATs. Copy the formula across to cells D12. F12 to determine the hibest score for the corresponding category. Download from SimNet: CSE 2111 Pre Lab 3 Computer Budget Master.xlsx CSE 2111 Pre Lab 3 Candidates Master.alsx YOU MUST DOWNLOAD YOUR STARTING FILES FROM YOUR SIMNET SESSION AND SUBMIT YOUR COMPLETED STARTING FILES TO YOUR SIMNET SESSION OR SIMNET WILL BLOCK YOUR SUBMISSION. PART 1: BUYING A COMPUTER There is money left over in the corporate capital budget and each department has been asked to price out a computer system they would like. The corporation has in mind a budget of $2,000 per department - though some may be able to so over if overall the corporation is still within budget. in order to determine if this equipinent can be purchased, you have created an Excel workbook named CSE 2111 Pre Lab3 Computer budget Master. xls . This spreadsheet contains the prices by department for each of the desired components - monitors, computers, soltware, junip drives, printers and additional memory. In addition you have categorized these costs based on where these isems/services will be acquired: Dell, Microcenter or CompUSA. The vendors are pre-determined based on existing, contractual agreements. You will now use this information to perform an analysis. Follow the steps as described beiow. Open the file, CSE 2111 Pre tab 3 Computer Badget Master, wisk Perform the necessary calculations as follows: - Determine the total costs for each computer system - in Cell cg, determine the total cort for the Ingineering. Group. Copy your formula across the row into cells D9:G9. - Compare the total cost of acquiring a system to the department's bueget (cell b1). - Write a formula in cell C11, which can be copied across the row, to determine how much over of under budget you would be if you purchased this system. Write the formula such that if you are over bodget this value will be negative. (Hint: do not forget to properly reference cells so that it can be copied across the fow.) - Write a formula, in cell B12, to automatically determine how many of the department systems are within your budget. (This formula should work even if any of the input costs are iater modified) - The corporation may decide to only fund either the Engineering Group or the R\&D (Research and Development) Group. Originally their decision was to fund the Engineering Group only bot now you hear that they might decide to fund R\&D instead. You just know someone will ask you who's cost miore and by how much on a percentage basis. Thus, in Cell 813, write a formula that shows the percent difference in total costs between the original choice - Engineering, and the alternative option - P\&D's systern - Determine the total costs by vendor - Write a formula in cell C16, that can be copled down and ocross into cells Cl6:618, to determine how much of the costs of this department's equipment are paid to this vendor. For example in cell Cl6 would be the costs for the Engineering Group's system putchased from Microcenter. PART 2: UNIVERSITY ADMISSIONS You work for a University admission comimittee that evaluates applicants. This fle contains the names and scores of applicant candidates including the Application Type (regular or tranffert. Verbal, Math SAT scores and 5cience SAT II scares. You have been asked to calculate a combined weighted score for each candidate, the combined average, the minimum and maximum scores for the applicant group, and summarise some statistics by application type. To complete this aroblem, use the following instructions: Open the file CSE 2111 Pre tab 3 Candidates Master.xlsx. Complete the spreadsheet answering the questions as follows: - In cell Fa write an Excel formula (use cell references whenever possible) which can be copied down into cells FS.f9 that calculates the weighted score of Mary Keck's test values. The weights of each test are given in row 2. So her final weighted score will be a sum of each score multipled by the weight of that score as follows: - Verbal 5AT score (680) * Verbal SAT score weight (20%)+ Math 5 AT score (670) * Math SAT score weight (40%)+ SCI SAT II score (580) * SCI SAT 1 score weight (409). This formula should be copied down the column to calculate the scares for the other applicants. - Write a formula in cell Clo that will calculate the average value of the entire applicant group's Verbal SAT scores. Copy the formula across to cells D10.F10 to calculate the average for each of the correspond ing scare categories. - Write a formula in cell C11 that will calculate the lowest score receved by an applicant on the Verbal SATs. Copy the formula across to cells D11:F11 to determine the fowert store for the corresponding category. - Write a formula in cell c12 that will calculate the highest score received by an applicant on the Verbal 5 ATs. Copy the formula across to cells D12. F12 to determine the hibest score for the corresponding category