Case Requirements Assume your responsibility is to analyze a pool of investment options from around the country. A capital budget with a cap of $435,000 is available. Using Excel, first, analyze the investments individually using three methods and rank them accordingly. Second, consolidate the results for all three methods on a single worksheet for additional analysis. 1. Use appropriate formulas to indicate whether the investment in each plant should be accepted or rejected for each of the following methods: a payback method (formula provided) b. net present value c. internal rate of return 2. For each of the three methods: a. Sort the data set on each of the three tabs by most favorable to least favorable. b. Add conditional formatting (traffic lights) to the results column. 3. Consolidate the results onto the Summary tab: a. Use VLOOKUP formulas to bring in the data as calculated for the three methods b. Sort the data, using a desired method, by most favorable to least favorable c. Add conditional formatting (flags) individually to each of the three columns 4. On a new tab, identify investments with an NPV in excess of $15,000: a. Copy/paste the Summary results onto a new Analysis tab. b. Remove the conditional formatting Add filters and set a number filter to achieve the desired result d. Add totals for the initial Investment and NPV columns, as filtered (which will appear as Subtotals) 5. Recall that your company has $435,000 to allocate to the list of capital projects. Write a report explaining the options and your recommendations. Include in your report the considerations that factored into your decision 6. Submit both the Excel spreadsheet and the Word document report for review. C 6 H J K L M N o Payback NPV IRR 8,000 B D E F Initial 1. Plant Investment Year 1 Year 2 Year 3 Year 4 2 Albuquerque; New Mexico (20,000) 10,000 8,000 9,000 8,600 3 Anaheim; California (40,000) 20,000 13,000 14,000 16,000 4 Anchorage municipality, Alaska (22,000) 16,000 5,000 4,000 8,000 5 Arlington; Texas (38,000) 16,000 14,000 10,000 14,000 6 Atlanta, Georgia (29,000) 6,000 16,000 3,000 5,000 7 Aurora; Colorado co (20,000) 16,000 15,000 15,000 13,000 8 Austin, Texas (25,000) 7,000 12,000 8,000 9 Bakersfield; California (29,000) 17,000 16,000 8,000 20,000 10 Baltimore; Maryland (32,000) 3,000 17,000 2,000 9,000 11 Baton Rouge; Louisiana (30,000) 18,000 2,000 6,000 6,000 12 Birmingham; Alabama (35,000) 20,000 15,000 7,000 6,000 13 Boise; Idaho 125,000) 17,000 9,000 19,000 11,000 14 Boston, Massachusetts (20,000) 8,000 5,000 11,000 19,000 15 Buffalo, New York (32,000) 13,000 4,000 14,000 16,000 16 Chandler, Arizona (33,000) 19,000 14,000 19,000 14,000 17 Charlotte; North Carolina (26,000) 15,000 14,000 18,000 11,000 18 Chesapeake: Virginia ( (22,000) 7,000 10,000 19,000 15,000 19 Chicago, Illinois (27,000) 11,000 10,000 5,000 11,000 20 Chula Vista, California (27,000) 4,000 12,000 13,000 3,000 21 Cincinnati, Ohio (28,000) 12,000 9,000 2,000 17,000 22 Cleveland; Ohio [22,000) 19,000 18,000 5,000 12.000 23 Colorado Springs, Colorado (24,000) 9,000 10,000 10,000 4,000 24 Columbus, Ohio (32,000) 9,000 7,000 18,000 8,000 25 Corpus Christi; Texas (29,000) 8,000 16,000 15,000 2,000 26 Dallas, Texas (20,000) 15,000 10,000 20,000 18,000 27 Denver, Colorado (32,000) 5,000 12,000 15,000 12,000 28 Detroit, Michigan (34,000) 9,000 6,000 4,000 19,000 29 Durham, North Carolina 128,000) 19,000 20,000 4,000 20,000 30 El Paso, Texas (26,000) 2,000 3,000 17.000 16,000 31 Fort Wayne, Indiana (32,000) 8.000 4,000 18,000 15,000 32 Fort Worth, Texas (37,000) 19,000 20,000 10,000 8,000 33 Fremont; California (21,000) 16,000 3,000 5,000 14,000 34 Fresno; California (34,000) 16,000 15,000 16,000 20,000 35 Garland; Texas (21,000) 5,000 14,000 12,000 2,000 26 Gilbert town, Arizona (40,000) 9,000 17,000 20,000 8,000 37 Glendale: Arizona (31,000) 12,000 20,000 18,000 8,000 Summary Payback NPY IRR Data Tune her 10 34 Fresno; California 35 Garland; Texas 36 Gilbert town, Arizona 37 Glendale; Arizona 38 Greensboro; North Carolina 39 Henderson; Nevada 40 Hialeah; Florida 41 Honolulu; Hawaii 42 Houston, Texas 43 Indianapolis, Indiana 44 Irvine; California 45 Irving; Texas 46 Jacksonville; Florida 47 Jersey, New Jersey 48 Kansas; Missouri 49 Laredo; Texas 50 Las Vegas, Nevada 51 Lexington-Fayette; Kentucky 52 Lincoln; Nebraska 53 Long Beach; California 54 Los Angeles, California 55 Louisville/Jefferson; Kentucky 56 Lubbock; Texas 57 Madison; Wisconsin 58 Memphis, Tennessee 59 Mesa; Arizona 60 Miami, Florida 61 Milwaukee; Wisconsin 62 Minneapolis; Minnesota 63 Nashville-Davidson; Tennessee 64 New Orleans, Louisiana 65 New York City, New York 66 Newark, New Jersey 67 Norfolk, Virginia 68 North Las Vegas, Nevada 69 Oakland; California 70 Oklahoma; Oklahoma 71 Omaha, Nebraska Summary Payback B D E F (34,000) 16,000 15,000 16,000 20,000 (21,000) 6,000 14,000 12,000 2,000 (40,000) 9,000 12,000 20,000 8,000 (31,000) 12,000 20,000 18,000 8,000 (26,000) 12,000 20,000 18,000 5,000 (27,000) 20,000 7,000 16,000 20,000 (23,000) 14,000 17,000 7,000 20,000 (22,000) 3,000 11,000 2,000 2,000 (23,000) 16,000 16,000 13,000 17,000 (27,000) 4,000 2,000 12,000 1,000 (31,000) 2,000 18,000 10,000 1,000 (24,000) 7,000 5,000 2,000 12,000 (20,000) 11,000 10,000 2,000 2,000 (26,000) 18,000 9,000 10,000 20,000 (40,000) 7,000 11,000 16,000 11,000 (25,000) 20,000 5,000 13,000 20,000 (37,000) 3,000 18,000 6,000 11,000 (32,000) 14,000 17,000 15,000 9,000 (33,000) 5,000 18,000 8,000 2,000 (35,000) 10,000 20,000 16,000 20,000 (35,000) 14,000 4,000 11,000 18,000 (24,000) 11,000 1,000 17,000 9,000 (20,000) 19,000 12,000 2,000 15,000 (24,000) 12,000 15,000 4,000 2,000 (29,000) 20,000 13,000 1,000 4,000 (24,000) 10,000 14,000 3,000 14,000 (39,000) 15,000 10,000 6,000 14,000 (20,000) 4,000 5,000 2,000 12,000 (30,000) 14,000 17,000 18,000 4,000 (35,000) 7,000 13,000 11,000 4,000 (26,000) 8,000 20,000 4,000 10,000 (38,000) 17,000 16,000 16,000 16,000 (21,000) 3,000 17,000 12,000 7,000 (29,000) 8,000 5,000 19,000 10,000 (26,000) 14,000 7,000 20,000 5,000 (20,000) 19,000 19,000 17.000 1,000 (28,000) 5,000 17,000 9,000 4,000 (39,000) 20,000 9,000 17.000 9,000 NPV IRR Data 11 Type here to search o H A klahoma; Oklahoma Omaha, Nebraska Orlando, Florida Philadelphia, Pennsylvania Phoenix, Arizona + Pittsburgh; Pennsylvania Plano; Texas Portland; Oregon Raleigh; North Carolina Reno; Nevada Riverside; California Sacramento; California San Antonio, Texas 3 San Bernardino; California San Diego, California 5 San Francisco; California -6 San Jose, California 37 Santa Ana; California 38 Scottsdale; Arizona 339 Seattle, Washington 90 St. Louis, Missouri 91 St. Paul; Minnesota 92 St. Petersburg; Florida 93 Stockton; California 94 Tampa; Florida 95 Toledo; Ohio 96 Tucson; Arizona 97 Tulsa, Oklahoma 98 Virginia Beach; Virginia 99 Washington, DC 100 Wichita; Kansas 101 Winston-Salem; North Carolina 102 103 104 105 B C D E F (28,000) 5,000 17,000 9,000 4,000 (39,000) 20,000 9,000 17,000 9,000 (28,000) 15,000 2,000 9,000 14,000 (30,000) 15,000 17,000 5,000 2,000 (37,000) 2,000 17,000 5,000 20,000 (37,000) 11,000 9,000 10,000 7,000 (38,000) 8,000 9,000 20,000 4,000 (40,000) 6,000 17,000 20,000 9,000 (25,000) 5,000 19,000 15,000 11,000 (34,000) 3,000 15,000 9,000 9,000 (27,000) 18,000 2,000 3,000 5,000 (27,000) 11,000 3,000 12,000 10,000 (40,000) 9,000 5,000 8,000 19,000 (31,000) 3,000 15,000 15,000 1,000 (38,000) 19,000 7,000 4,000 18,000 (33,000) 14,000 19,000 15,000 5,000 (20,000) 19,000 19,000 1,000 14,000 (23,000) 9,000 7,000 7,000 5,000 (20,000) 19,000 15,000 14,000 12,000 (32,000) 9,000 19,000 14,000 11,000 (40,000) 15,000 15,000 9,000 7,000 (29,000) 16,000 7,000 6,000 3,000 (30,000) 8,000 13,000 2,000 14,000 (36,000) 10,000 15,000 8,000 3,000 (30,000) 9,000 8,000 10,000 14,000 (37,000) 2,000 8,000 9,000 5,000 (22,000) 6,000 17,000 2,000 3,000 (25,000) 16,000 11,000 18,000 14,000 (27,000) 15,000 10,000 15,000 19,000 (23,000) 16,000 2,000 1,000 15,000 (21,000) 14,000 6,000 14,000 15,000 (38,000) 14,000 6,000 20,000 4,000 H 1 M N P Rate CY 14% (Formula hint 1: Use an absolute value for the 12 cell reference) Check figure for Albuquerque: $6,094.30 Case Requirements Assume your responsibility is to analyze a pool of investment options from around the country. A capital budget with a cap of $435,000 is available. Using Excel, first, analyze the investments individually using three methods and rank them accordingly. Second, consolidate the results for all three methods on a single worksheet for additional analysis. 1. Use appropriate formulas to indicate whether the investment in each plant should be accepted or rejected for each of the following methods: a payback method (formula provided) b. net present value c. internal rate of return 2. For each of the three methods: a. Sort the data set on each of the three tabs by most favorable to least favorable. b. Add conditional formatting (traffic lights) to the results column. 3. Consolidate the results onto the Summary tab: a. Use VLOOKUP formulas to bring in the data as calculated for the three methods b. Sort the data, using a desired method, by most favorable to least favorable c. Add conditional formatting (flags) individually to each of the three columns 4. On a new tab, identify investments with an NPV in excess of $15,000: a. Copy/paste the Summary results onto a new Analysis tab. b. Remove the conditional formatting Add filters and set a number filter to achieve the desired result d. Add totals for the initial Investment and NPV columns, as filtered (which will appear as Subtotals) 5. Recall that your company has $435,000 to allocate to the list of capital projects. Write a report explaining the options and your recommendations. Include in your report the considerations that factored into your decision 6. Submit both the Excel spreadsheet and the Word document report for review. C 6 H J K L M N o Payback NPV IRR 8,000 B D E F Initial 1. Plant Investment Year 1 Year 2 Year 3 Year 4 2 Albuquerque; New Mexico (20,000) 10,000 8,000 9,000 8,600 3 Anaheim; California (40,000) 20,000 13,000 14,000 16,000 4 Anchorage municipality, Alaska (22,000) 16,000 5,000 4,000 8,000 5 Arlington; Texas (38,000) 16,000 14,000 10,000 14,000 6 Atlanta, Georgia (29,000) 6,000 16,000 3,000 5,000 7 Aurora; Colorado co (20,000) 16,000 15,000 15,000 13,000 8 Austin, Texas (25,000) 7,000 12,000 8,000 9 Bakersfield; California (29,000) 17,000 16,000 8,000 20,000 10 Baltimore; Maryland (32,000) 3,000 17,000 2,000 9,000 11 Baton Rouge; Louisiana (30,000) 18,000 2,000 6,000 6,000 12 Birmingham; Alabama (35,000) 20,000 15,000 7,000 6,000 13 Boise; Idaho 125,000) 17,000 9,000 19,000 11,000 14 Boston, Massachusetts (20,000) 8,000 5,000 11,000 19,000 15 Buffalo, New York (32,000) 13,000 4,000 14,000 16,000 16 Chandler, Arizona (33,000) 19,000 14,000 19,000 14,000 17 Charlotte; North Carolina (26,000) 15,000 14,000 18,000 11,000 18 Chesapeake: Virginia ( (22,000) 7,000 10,000 19,000 15,000 19 Chicago, Illinois (27,000) 11,000 10,000 5,000 11,000 20 Chula Vista, California (27,000) 4,000 12,000 13,000 3,000 21 Cincinnati, Ohio (28,000) 12,000 9,000 2,000 17,000 22 Cleveland; Ohio [22,000) 19,000 18,000 5,000 12.000 23 Colorado Springs, Colorado (24,000) 9,000 10,000 10,000 4,000 24 Columbus, Ohio (32,000) 9,000 7,000 18,000 8,000 25 Corpus Christi; Texas (29,000) 8,000 16,000 15,000 2,000 26 Dallas, Texas (20,000) 15,000 10,000 20,000 18,000 27 Denver, Colorado (32,000) 5,000 12,000 15,000 12,000 28 Detroit, Michigan (34,000) 9,000 6,000 4,000 19,000 29 Durham, North Carolina 128,000) 19,000 20,000 4,000 20,000 30 El Paso, Texas (26,000) 2,000 3,000 17.000 16,000 31 Fort Wayne, Indiana (32,000) 8.000 4,000 18,000 15,000 32 Fort Worth, Texas (37,000) 19,000 20,000 10,000 8,000 33 Fremont; California (21,000) 16,000 3,000 5,000 14,000 34 Fresno; California (34,000) 16,000 15,000 16,000 20,000 35 Garland; Texas (21,000) 5,000 14,000 12,000 2,000 26 Gilbert town, Arizona (40,000) 9,000 17,000 20,000 8,000 37 Glendale: Arizona (31,000) 12,000 20,000 18,000 8,000 Summary Payback NPY IRR Data Tune her 10 34 Fresno; California 35 Garland; Texas 36 Gilbert town, Arizona 37 Glendale; Arizona 38 Greensboro; North Carolina 39 Henderson; Nevada 40 Hialeah; Florida 41 Honolulu; Hawaii 42 Houston, Texas 43 Indianapolis, Indiana 44 Irvine; California 45 Irving; Texas 46 Jacksonville; Florida 47 Jersey, New Jersey 48 Kansas; Missouri 49 Laredo; Texas 50 Las Vegas, Nevada 51 Lexington-Fayette; Kentucky 52 Lincoln; Nebraska 53 Long Beach; California 54 Los Angeles, California 55 Louisville/Jefferson; Kentucky 56 Lubbock; Texas 57 Madison; Wisconsin 58 Memphis, Tennessee 59 Mesa; Arizona 60 Miami, Florida 61 Milwaukee; Wisconsin 62 Minneapolis; Minnesota 63 Nashville-Davidson; Tennessee 64 New Orleans, Louisiana 65 New York City, New York 66 Newark, New Jersey 67 Norfolk, Virginia 68 North Las Vegas, Nevada 69 Oakland; California 70 Oklahoma; Oklahoma 71 Omaha, Nebraska Summary Payback B D E F (34,000) 16,000 15,000 16,000 20,000 (21,000) 6,000 14,000 12,000 2,000 (40,000) 9,000 12,000 20,000 8,000 (31,000) 12,000 20,000 18,000 8,000 (26,000) 12,000 20,000 18,000 5,000 (27,000) 20,000 7,000 16,000 20,000 (23,000) 14,000 17,000 7,000 20,000 (22,000) 3,000 11,000 2,000 2,000 (23,000) 16,000 16,000 13,000 17,000 (27,000) 4,000 2,000 12,000 1,000 (31,000) 2,000 18,000 10,000 1,000 (24,000) 7,000 5,000 2,000 12,000 (20,000) 11,000 10,000 2,000 2,000 (26,000) 18,000 9,000 10,000 20,000 (40,000) 7,000 11,000 16,000 11,000 (25,000) 20,000 5,000 13,000 20,000 (37,000) 3,000 18,000 6,000 11,000 (32,000) 14,000 17,000 15,000 9,000 (33,000) 5,000 18,000 8,000 2,000 (35,000) 10,000 20,000 16,000 20,000 (35,000) 14,000 4,000 11,000 18,000 (24,000) 11,000 1,000 17,000 9,000 (20,000) 19,000 12,000 2,000 15,000 (24,000) 12,000 15,000 4,000 2,000 (29,000) 20,000 13,000 1,000 4,000 (24,000) 10,000 14,000 3,000 14,000 (39,000) 15,000 10,000 6,000 14,000 (20,000) 4,000 5,000 2,000 12,000 (30,000) 14,000 17,000 18,000 4,000 (35,000) 7,000 13,000 11,000 4,000 (26,000) 8,000 20,000 4,000 10,000 (38,000) 17,000 16,000 16,000 16,000 (21,000) 3,000 17,000 12,000 7,000 (29,000) 8,000 5,000 19,000 10,000 (26,000) 14,000 7,000 20,000 5,000 (20,000) 19,000 19,000 17.000 1,000 (28,000) 5,000 17,000 9,000 4,000 (39,000) 20,000 9,000 17.000 9,000 NPV IRR Data 11 Type here to search o H A klahoma; Oklahoma Omaha, Nebraska Orlando, Florida Philadelphia, Pennsylvania Phoenix, Arizona + Pittsburgh; Pennsylvania Plano; Texas Portland; Oregon Raleigh; North Carolina Reno; Nevada Riverside; California Sacramento; California San Antonio, Texas 3 San Bernardino; California San Diego, California 5 San Francisco; California -6 San Jose, California 37 Santa Ana; California 38 Scottsdale; Arizona 339 Seattle, Washington 90 St. Louis, Missouri 91 St. Paul; Minnesota 92 St. Petersburg; Florida 93 Stockton; California 94 Tampa; Florida 95 Toledo; Ohio 96 Tucson; Arizona 97 Tulsa, Oklahoma 98 Virginia Beach; Virginia 99 Washington, DC 100 Wichita; Kansas 101 Winston-Salem; North Carolina 102 103 104 105 B C D E F (28,000) 5,000 17,000 9,000 4,000 (39,000) 20,000 9,000 17,000 9,000 (28,000) 15,000 2,000 9,000 14,000 (30,000) 15,000 17,000 5,000 2,000 (37,000) 2,000 17,000 5,000 20,000 (37,000) 11,000 9,000 10,000 7,000 (38,000) 8,000 9,000 20,000 4,000 (40,000) 6,000 17,000 20,000 9,000 (25,000) 5,000 19,000 15,000 11,000 (34,000) 3,000 15,000 9,000 9,000 (27,000) 18,000 2,000 3,000 5,000 (27,000) 11,000 3,000 12,000 10,000 (40,000) 9,000 5,000 8,000 19,000 (31,000) 3,000 15,000 15,000 1,000 (38,000) 19,000 7,000 4,000 18,000 (33,000) 14,000 19,000 15,000 5,000 (20,000) 19,000 19,000 1,000 14,000 (23,000) 9,000 7,000 7,000 5,000 (20,000) 19,000 15,000 14,000 12,000 (32,000) 9,000 19,000 14,000 11,000 (40,000) 15,000 15,000 9,000 7,000 (29,000) 16,000 7,000 6,000 3,000 (30,000) 8,000 13,000 2,000 14,000 (36,000) 10,000 15,000 8,000 3,000 (30,000) 9,000 8,000 10,000 14,000 (37,000) 2,000 8,000 9,000 5,000 (22,000) 6,000 17,000 2,000 3,000 (25,000) 16,000 11,000 18,000 14,000 (27,000) 15,000 10,000 15,000 19,000 (23,000) 16,000 2,000 1,000 15,000 (21,000) 14,000 6,000 14,000 15,000 (38,000) 14,000 6,000 20,000 4,000 H 1 M N P Rate CY 14% (Formula hint 1: Use an absolute value for the 12 cell reference) Check figure for Albuquerque: $6,094.30