PLEASE USE THE EXCEL WITH THE FORMULAS:

2. In the Expansion worksheet, type in the following terms of the loan, using the Accounting format for the loan amount and Percent Style formatting with a single decimal showing for the interest rate. $ Amount of Loan Period (years) Interest Rate (per year) 200,000 15 4.9% 3. In cell B6, create a PMT() function that calculates the monthly payment for the loan as a positive value. (Hint: Be sure arguments in your function are converted into months to calculate the monthly payment.) 4. Format the Payment using Accounting Number Format. 5. In B7 of the Summary worksheet, create a cell reference linking to the monthly payment from the Expansion worksheet. 6. In B8 of the Summary worksheet, create a formula that calculates the total cost of warehouse space. Project Update: Now that you know the costs associated with expanding, you decide to talk with a financial advisor about your plans. After looking at your sales history, he advises you that your total costs for warehouse space should not exceed 5% of the total value of your inventory. You are going to use this ratio to decide whether or not to expand at this time. First, you need to determine the total value of your inventory. Then you will determine the ratio of warehouse space to inventory on hand. Analyze the Data 1. In column of the Inventory worksheet under the Value header, calculate the value of the items in your inventory, using the information you have about Price and Quantity. 2. In cell 073, calculate the total value of all items in your inventory. 3. Return to the Summary worksheet, and create a reference in B3 that links it to the cell containing the total value of all items from your Inventory worksheet. 4. In B10 of the Summary worksheet, write a formula that divides the Total Cost of your warehouse space by the value of your Inventory. Follow-up: If your Warehouse Ratio is above 5%, you shouldn't expand. However, loans can be negotiated, and you decided not to give up on your project. Instead, you follow up with the bank, and ask that they change the terms of the loan from a 15-year payback period to a 30-year payback period. Adjust your work to reflect this change. 1. Return to the Expansion worksheet and change the Period from 15 years to 30. 2. Go to the Summary worksheet, and see if you are below the 5% threshold for your Warehouse Ratio. 3. Preview all three worksheets and make any changes needed so they each print on one page. 4. Save the workbook. Submit the workbook as directed by your instructor. 3 Page PROTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing A13 fx D E F G H I J K L B 1 Climb-It Outfitters 2 3 Inventory 4 5 Warehouse 6 Existing Space $ 600.00 7 Expansion 8 Total Cost 9 10 Warehouse Ratio 11 Only Expand if Below 5% 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 Summary Inventory Expansion + File Home Insert Page Layout Formulas Data Review View Help PROTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing D73 D F F G H 1 J K L Value A B 1 Item Price Quantity 2 Green Emerald Active Cam Protection $ 13.46 10 3 Green Emerald Ascender $39.95 4 4 Green Emerald Cam Protection $ 12.71 3 5 Green Emerald Carabiner $10.95 4 6 Green Emerald Carabiner - 3 Pack $ 10.95 5 7 Green Emerald Carabiner - Magnetic $ 10.95 12 8 Green Emerald Carabiner - Screwgate $10.95 6 9 Green Emerald Climbing Harness $ 115.95 12 10 Green Emerald Colour Climbing Harness $ 119.95 12 11 Green Emerald Contact Crampons - Pair $ 99.95 5 12 Green Emerald Crampon Bag $ 29.95 12 13 Green Emerald Dynamite Runner (One) $ 39.95 12 14 Green Emerald Express Ice Screws (One) $ 39.95 10 15 Green Emerald Guide Belay & Rappel Device $199.95 8 16 Green Emerald Harness $ 115.95 7 17 Green Emerald Movement Climbing Harness $ 124.95 12 18 Green Emerald Movement Climbing Package $ 41.21 5 19 Green Emerald Movement DS Climbing Harness $ 129.95 9 20 Green Emerald Movement DS Combo Package $44.95 13 21 Green Emerald Nylon Runner (One) $44.95 8 22 Green Emerald Offset Cams $ 13.95 29 23 Green Emerald Personal Anchor $ 39.95 23 24 Green Emerald Pro Ice Axe $ 49.95 17 25 Green Emerald Pulley $44.95 9 26 Green Emerald Rackpack $ 39.95 5 27 Green Emerald Robo Crampons - Pair (Pro or Clip) $ 99.95 7 28 Green Emerald Rope Bag $ 29.96 22 29 Green Emerald Rope Bag & Tarp $ 31.95 11 30 Green Emerald Rotor Swivel $ 49.95 7 31 Green Emerald Stopper Sets $ 49.95 24 32 Green Emerald Turbo Ice Screw $ 49.95 12 33 Green Emerald Ultralight Active Cam Protection $ 12.95 49 34 Green Emerald XPert Belay & Rappel Device $ 199.95 3 35 Green Emerald XPert Package - Belay & Rappel $ 209.95 3 36 Mammoth Belay / Rappel Device $ 52.95 21 37 Mammoth Belay Chain 12.0 $ 52.46 70 38 Mammoth Belay Sling 19.0 $ 52.95 9 39 Mammoth Carabiner - Single $10.95 12 40 Mammoth Climbing Harness $ 137.95 3 41 Mammoth Contact Sling 8.0 $ 54.95 4 Summary Inventory Expansion 8 7 10 9 2 3 28 20 9 12 19 6 12 5 3 42 Mammoth Key Lock Carabiner 43 Mammoth Light Belay Device 44 Mammoth Rappel Device 45 Mammoth Rappel Kit 46 Mammoth Rescue Device 47 Mammoth Rope Bag 48 Mammoth Rope Bag 49 Mammoth Screw Gate Carabiner 50 Pretzel Ascender 51 Pretzel Belay / Rappel Gloves - Pair 52 Pretzel Belay Device 53 Pretzel Carabiner 54 Pretzel Climbing Harness 55 Pretzel Crampons - Pair 56 Pretzel Dual Connect Adjust 57 Pretzel Emergency Ascender 58 Pretzel Foot Loop 59 Pretzel H-frame Carabiner 60 Pretzel Ice Screw Sharpener 61 Pretzel Ice Tool 62 Pretzel Knife 63 Pretzel Light Ice Screw 64 Pretzel Locking Carabiner 65 Pretzel Nylon Sling 66 Pretzel Pro Pulley 67 Pretzel Pulley 68 Pretzel Rope Grab 69 Pretzel Self-Braking Belay Device 70 PTERO'DACTYL Chalk Bag - Small 71 PTERO'DACTYL Rope Bag 72 73 Total $11.95 $ 54.95 $ 55.95 $ 59.95 $ 59.95 $ 31.95 $ 33.71 $ 11.95 $ 59.95 $ 59.95 $ 69.95 $ 11.96 $ 139.95 $ 99.95 $ 59.95 $ 69.95 $ 64.95 $11.95 $ 67.46 $ 64.95 $ 69.95 $ 64.95 $ 11.96 $ 59.95 $ 69.95 $ 69.00 $ 69.95 $ 63.96 $19.95 $21.95 12 4 12 7 11 10 32 28 6 6 4 4 7 8 8 PROTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Pr B3 fx E F G H A B D 1 Terms of Warehouse Expansion Loan 2 3 Amount of Loan 4 Period (years) 5 Interest Rate (per year) 6 Payment (per month) 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 Summary Inventory Expansion 2. In the Expansion worksheet, type in the following terms of the loan, using the Accounting format for the loan amount and Percent Style formatting with a single decimal showing for the interest rate. $ Amount of Loan Period (years) Interest Rate (per year) 200,000 15 4.9% 3. In cell B6, create a PMT() function that calculates the monthly payment for the loan as a positive value. (Hint: Be sure arguments in your function are converted into months to calculate the monthly payment.) 4. Format the Payment using Accounting Number Format. 5. In B7 of the Summary worksheet, create a cell reference linking to the monthly payment from the Expansion worksheet. 6. In B8 of the Summary worksheet, create a formula that calculates the total cost of warehouse space. Project Update: Now that you know the costs associated with expanding, you decide to talk with a financial advisor about your plans. After looking at your sales history, he advises you that your total costs for warehouse space should not exceed 5% of the total value of your inventory. You are going to use this ratio to decide whether or not to expand at this time. First, you need to determine the total value of your inventory. Then you will determine the ratio of warehouse space to inventory on hand. Analyze the Data 1. In column of the Inventory worksheet under the Value header, calculate the value of the items in your inventory, using the information you have about Price and Quantity. 2. In cell 073, calculate the total value of all items in your inventory. 3. Return to the Summary worksheet, and create a reference in B3 that links it to the cell containing the total value of all items from your Inventory worksheet. 4. In B10 of the Summary worksheet, write a formula that divides the Total Cost of your warehouse space by the value of your Inventory. Follow-up: If your Warehouse Ratio is above 5%, you shouldn't expand. However, loans can be negotiated, and you decided not to give up on your project. Instead, you follow up with the bank, and ask that they change the terms of the loan from a 15-year payback period to a 30-year payback period. Adjust your work to reflect this change. 1. Return to the Expansion worksheet and change the Period from 15 years to 30. 2. Go to the Summary worksheet, and see if you are below the 5% threshold for your Warehouse Ratio. 3. Preview all three worksheets and make any changes needed so they each print on one page. 4. Save the workbook. Submit the workbook as directed by your instructor. 3 Page PROTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing A13 fx D E F G H I J K L B 1 Climb-It Outfitters 2 3 Inventory 4 5 Warehouse 6 Existing Space $ 600.00 7 Expansion 8 Total Cost 9 10 Warehouse Ratio 11 Only Expand if Below 5% 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 Summary Inventory Expansion + File Home Insert Page Layout Formulas Data Review View Help PROTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing D73 D F F G H 1 J K L Value A B 1 Item Price Quantity 2 Green Emerald Active Cam Protection $ 13.46 10 3 Green Emerald Ascender $39.95 4 4 Green Emerald Cam Protection $ 12.71 3 5 Green Emerald Carabiner $10.95 4 6 Green Emerald Carabiner - 3 Pack $ 10.95 5 7 Green Emerald Carabiner - Magnetic $ 10.95 12 8 Green Emerald Carabiner - Screwgate $10.95 6 9 Green Emerald Climbing Harness $ 115.95 12 10 Green Emerald Colour Climbing Harness $ 119.95 12 11 Green Emerald Contact Crampons - Pair $ 99.95 5 12 Green Emerald Crampon Bag $ 29.95 12 13 Green Emerald Dynamite Runner (One) $ 39.95 12 14 Green Emerald Express Ice Screws (One) $ 39.95 10 15 Green Emerald Guide Belay & Rappel Device $199.95 8 16 Green Emerald Harness $ 115.95 7 17 Green Emerald Movement Climbing Harness $ 124.95 12 18 Green Emerald Movement Climbing Package $ 41.21 5 19 Green Emerald Movement DS Climbing Harness $ 129.95 9 20 Green Emerald Movement DS Combo Package $44.95 13 21 Green Emerald Nylon Runner (One) $44.95 8 22 Green Emerald Offset Cams $ 13.95 29 23 Green Emerald Personal Anchor $ 39.95 23 24 Green Emerald Pro Ice Axe $ 49.95 17 25 Green Emerald Pulley $44.95 9 26 Green Emerald Rackpack $ 39.95 5 27 Green Emerald Robo Crampons - Pair (Pro or Clip) $ 99.95 7 28 Green Emerald Rope Bag $ 29.96 22 29 Green Emerald Rope Bag & Tarp $ 31.95 11 30 Green Emerald Rotor Swivel $ 49.95 7 31 Green Emerald Stopper Sets $ 49.95 24 32 Green Emerald Turbo Ice Screw $ 49.95 12 33 Green Emerald Ultralight Active Cam Protection $ 12.95 49 34 Green Emerald XPert Belay & Rappel Device $ 199.95 3 35 Green Emerald XPert Package - Belay & Rappel $ 209.95 3 36 Mammoth Belay / Rappel Device $ 52.95 21 37 Mammoth Belay Chain 12.0 $ 52.46 70 38 Mammoth Belay Sling 19.0 $ 52.95 9 39 Mammoth Carabiner - Single $10.95 12 40 Mammoth Climbing Harness $ 137.95 3 41 Mammoth Contact Sling 8.0 $ 54.95 4 Summary Inventory Expansion 8 7 10 9 2 3 28 20 9 12 19 6 12 5 3 42 Mammoth Key Lock Carabiner 43 Mammoth Light Belay Device 44 Mammoth Rappel Device 45 Mammoth Rappel Kit 46 Mammoth Rescue Device 47 Mammoth Rope Bag 48 Mammoth Rope Bag 49 Mammoth Screw Gate Carabiner 50 Pretzel Ascender 51 Pretzel Belay / Rappel Gloves - Pair 52 Pretzel Belay Device 53 Pretzel Carabiner 54 Pretzel Climbing Harness 55 Pretzel Crampons - Pair 56 Pretzel Dual Connect Adjust 57 Pretzel Emergency Ascender 58 Pretzel Foot Loop 59 Pretzel H-frame Carabiner 60 Pretzel Ice Screw Sharpener 61 Pretzel Ice Tool 62 Pretzel Knife 63 Pretzel Light Ice Screw 64 Pretzel Locking Carabiner 65 Pretzel Nylon Sling 66 Pretzel Pro Pulley 67 Pretzel Pulley 68 Pretzel Rope Grab 69 Pretzel Self-Braking Belay Device 70 PTERO'DACTYL Chalk Bag - Small 71 PTERO'DACTYL Rope Bag 72 73 Total $11.95 $ 54.95 $ 55.95 $ 59.95 $ 59.95 $ 31.95 $ 33.71 $ 11.95 $ 59.95 $ 59.95 $ 69.95 $ 11.96 $ 139.95 $ 99.95 $ 59.95 $ 69.95 $ 64.95 $11.95 $ 67.46 $ 64.95 $ 69.95 $ 64.95 $ 11.96 $ 59.95 $ 69.95 $ 69.00 $ 69.95 $ 63.96 $19.95 $21.95 12 4 12 7 11 10 32 28 6 6 4 4 7 8 8 PROTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Pr B3 fx E F G H A B D 1 Terms of Warehouse Expansion Loan 2 3 Amount of Loan 4 Period (years) 5 Interest Rate (per year) 6 Payment (per month) 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 Summary Inventory Expansion