You are considering an investment which would earn 3% interest per year, over 8 years, and...

  

Transcribed Image Text:

You are considering an investment which would earn 3% interest per year, over 8 years, and which would require you to invest nothing up front and $400 per month. (The "nothing up front" investment means the present value of this investment is 0.) Assume all interest is compounded at the beginning of the period. What is the future value of this investment? [Hint: Remember in Excel, when you put money into an investment, it is recorded as a negative amount. Your $400 payments here should be recorded as -$400.] Set your input data up in the blue cells below, and put your answer in the green cell D7 for FV. If the information is above, it should be included in cells D2:D5. For this problem, if there are time conversions, due them in the blue cells D2:D5, not in the FV equation in cell D7. Q2) Copy your FV inputs and answer from cells D2:D7 in Question 1 to cells D10:D15 for Question 2. Use Goal Seek (not Solver) to determine what montly payment you would need to have if the future value of this investment were to be exactly $75,000. For this problem, if there are time conversions, due them in the blue cells D10:D13, not in the FV equation in cell D15. Q3) Use Solver to determine the following. (Just leave your Solver solution set up in there.) The equation below gives the EOQ (Economic Order Quantity) as a function of the Cost of Ordering, the annual Demand, and the Holding Cost. AD EOQ= 2. Cost Enter that equation in the green cell F20. Do not change this equation; use it as your starting point. Q4) Copy the colored cells in row 20 to the cells in row 25. Use Solver determine what the Cost of Ordering would have to be in order for the EOQ to be 300 units. Before clicking "OK" in your Solver Parameters dialogue box, please enter your set objective cell in yellow cell 018, your to max/min/value of in yellow cells 120/120/P20 (as appropriate), and which cell you are changing in yellow cell 123. Use relative not absolute cell references. Q5) Copy the colored cells in row 20 to the cells in row 30. Use Solver determine what the Annual Demand would have to be in order for the EOQ to be 400 units. Before clicking "OK" in your Solver Parameters dialogue box, please enter your set objective cell in yellow cell 030, your to max/min/value of in yellow cells 132/L32/P32 (as appropriate), and which cell you are changing in yellow cell 135. Use relative not absolute cell references. Q1) Rate 3% 8 -4800 Number of periods Payment Present value FV $43,963.71 Q2) Number of periods Rate 3% 8 -4800 Payment Present value FV $43,963.71 Q3) Q4 Cost of Annual Ordering Demand Holding Cost (Cost) (AD) 100 (HC) 300 Cost of Annual Ordering Demand Holding Cost (Cost) (AD) (HC) Q5) Cost of Annual Ordering Demand Holding Cost (Cost) (AD) (HC) For Q4: Solver Parameters: Set Objective: EOQ 5 ? To: Max Min Value EOQ ? By Changing Variable Cells: For 05: Solver Parameters: EOQ ? Set Objective: To: Max Min Value By Changing Variable Cells: You are considering an investment which would earn 3% interest per year, over 8 years, and which would require you to invest nothing up front and $400 per month. (The "nothing up front" investment means the present value of this investment is 0.) Assume all interest is compounded at the beginning of the period. What is the future value of this investment? [Hint: Remember in Excel, when you put money into an investment, it is recorded as a negative amount. Your $400 payments here should be recorded as -$400.] Set your input data up in the blue cells below, and put your answer in the green cell D7 for FV. If the information is above, it should be included in cells D2:D5. For this problem, if there are time conversions, due them in the blue cells D2:D5, not in the FV equation in cell D7. Q2) Copy your FV inputs and answer from cells D2:D7 in Question 1 to cells D10:D15 for Question 2. Use Goal Seek (not Solver) to determine what montly payment you would need to have if the future value of this investment were to be exactly $75,000. For this problem, if there are time conversions, due them in the blue cells D10:D13, not in the FV equation in cell D15. Q3) Use Solver to determine the following. (Just leave your Solver solution set up in there.) The equation below gives the EOQ (Economic Order Quantity) as a function of the Cost of Ordering, the annual Demand, and the Holding Cost. AD EOQ= 2. Cost Enter that equation in the green cell F20. Do not change this equation; use it as your starting point. Q4) Copy the colored cells in row 20 to the cells in row 25. Use Solver determine what the Cost of Ordering would have to be in order for the EOQ to be 300 units. Before clicking "OK" in your Solver Parameters dialogue box, please enter your set objective cell in yellow cell 018, your to max/min/value of in yellow cells 120/120/P20 (as appropriate), and which cell you are changing in yellow cell 123. Use relative not absolute cell references. Q5) Copy the colored cells in row 20 to the cells in row 30. Use Solver determine what the Annual Demand would have to be in order for the EOQ to be 400 units. Before clicking "OK" in your Solver Parameters dialogue box, please enter your set objective cell in yellow cell 030, your to max/min/value of in yellow cells 132/L32/P32 (as appropriate), and which cell you are changing in yellow cell 135. Use relative not absolute cell references. Q1) Rate 3% 8 -4800 Number of periods Payment Present value FV $43,963.71 Q2) Number of periods Rate 3% 8 -4800 Payment Present value FV $43,963.71 Q3) Q4 Cost of Annual Ordering Demand Holding Cost (Cost) (AD) 100 (HC) 300 Cost of Annual Ordering Demand Holding Cost (Cost) (AD) (HC) Q5) Cost of Annual Ordering Demand Holding Cost (Cost) (AD) (HC) For Q4: Solver Parameters: Set Objective: EOQ 5 ? To: Max Min Value EOQ ? By Changing Variable Cells: For 05: Solver Parameters: EOQ ? Set Objective: To: Max Min Value By Changing Variable Cells: