Excel CSE 2111 question

KEY FOR COMPARISON

Compare the total cost of acquiring a system to the department's budget (cell B1). - Write a formula in cell C11, which can be copied across the row, to determine how much over or under budget you would be if you purchased this system. Write the formula such that if you are over budget this value will be negative. (Hint: do not forget to properly reference cells so that it can be copied across the row.) . . 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 later 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 but now you hear that they might decide to fund R&D instead. You just know someone will ask you who's cost more and by how much on a percentage basis. Thus, in Cell B13, write a formula that shows the percent difference in total costs between the original choice - Engineering, and the alternative option - R&D's system Determine the total costs by vendor Write a formula in cell C16, that can be copied down and across into cells C16:G18, to determine how much of the costs of this department's equipment are paid to this vendor. For example in cell C16 would be the costs for the Engineering Group's system purchased from Microcenter. - - . B12 v fx A B D E F G H 1 Budget 2000 Engineering Customer Service Marketing Finance R&D 659 259 $ $ $ $ 259 659 449 59 299 1,409 $ 859 $ 449 $ $ $ 1,299 239 $ $ $ $ 789 499 99 $ 59 $ $ $ 79 $ 55 $ 1,250 129 $ 299 $ 299 $ 1,634 $ 1,363 $ 3,447 $ 1,945 $ 2,066 2 Vendor 3 Monitor Microcenter 4 Computer Dell 5 Software CompUSA 6 Jump Drive Microcenter 7 Printer Microcenter 8 Addl Memory Dell 9 TOTAL 10 11 over/under budget 12 How many can afford? How much greater in % is R&D's cost vs. 13 Engr'g 14 15 totals: 16 Microcenter 17 Dell 18 CompUSA 19 20 21 . B D E F G 1 Budget 2000 R&D 2 3 AA 299 1,409 4 5 Vendor Microcenter Dell CompUSA Microcenter Microcenter Dell Monitor Computer Software Jump Drive Printer Addi Memory TOTAL Customer Engineering Marketing Finance Service $ 259 $ 659 $ 259 $ $ 659 $ 859 $ 1,299 $ 789 $ $ 449 $ 449 $ 239 $ 499 $ 59 $ 99 $ $ 79 $ 55$ 1,250 $ 129 $ 299 $ $ 1,634 $ 1,363 $ 3,447 $ 1,945 $ 6 59 7 8 299 2,066 9 $ 366.00 $ 637.00 $ (1,447.00 $ 55.00 $ (66.00) 26.4% 10 11 over/under budget How many can 12 afford? How much greater in % is R&D's cost 13 vs. Engr'g 14 15 totals: 16 Microcenter 17 Dell 18 CompUSA 55 $ 358$ 358 $ $ $ 397 $ 788$ 449 $ GA 1,909 $ 1,299 $ 239 $ 859 $ 449 $ 1,708 1,088$ 499 $