- [30 points] Paulos Pizza Palace is a small but growing pizza chain. The management of the company has decided to build a new location in Charleston, Illinois, and wants to establish a construction fund (or sinking fund) to pay for the new facility. Construction of the restaurant is expected to take six months and cost $400,000. Paulos contract with the construction company requires him to make payments of $125,000 at the beginning of the third and fifth months, and a final payment of $150,000 at the beginning of the seventh month when the restaurant is completed. The company can use four investment opportunities to establish the construction fund. These investments are summarized in the following table.
| Investment | Available in Month | Months to Maturity | Yield at Maturity |
| A | 1,2,3,4,5,6 | 1 | 1.65% |
| B | 1,3,5 | 2 | 3.32% |
| C | 1,4 | 3 | 5.06% |
| D | 1 | 6 | 10.28% |
This table indicates that investment A will be available at the beginning of each of the next six months, and funds invested in this manner mature in one month (at the beginning of month 2, the beginning of month 3, at the beginning of month 4, the beginning of month 5, the beginning of month 6, or the beginning of month 7) with a yield of 1.65%. Funds can be placed in investment B only at the beginning of months 1 and/or 3 and/or 5, and mature in two months (at the beginning of month 3, the beginning of month 5, or the beginning of month 7) with a yield of 3.32%. Funds can be placed in investment C only at the beginning of months 1 and/or 4, and mature in three months (at the beginning of month 4, or the beginning of month 7) with a yield of 5.06%. Funds can be invested in investment D only at the beginning of month 1, and mature in 6 months (at the beginning of month 7) with a yield of 10.28%.
The management of Paulos Pizza Palace wants to determine the investment plan that allows them to meet the required schedule of payments while placing the least amount of money in the construction fund in month 1. In other words, they must plan which investment alternatives to use at various times during the next six months in order to meet the required schedule of payments while placing the least amount of money in the construction fund in month 1.
This is for my quantitative modeling class. Components in the model: Inputs should be (blue), Decision variables (red), Target cell (grey) and use constraints like and please answer all questions using excel and a model. I am attaching the 3 excel files as images for part a b and c. Please use excel to solve it.
3A
3B
Problem 3 Data on investments Month ofinvestment Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 Invest A1 $1.00 $0.00 $0.00 $0.00 $0.00 $0.00 Invest A2 $0.00 $1.00 $0.00 $0.00 $0.00 $0.00 Invest A3 $0.00 $0.00 $1.00 $0.00 $0.00 $0.00 Invest A4 $0.00 $0.00 $0.00 $1.00 $0.00 $0.00 Invest A5 $0.00 $0.00 $0.00 $0.00 $1.00 $0.00 Invest AB $0.00 $0.00 $0.00 $0.00 $0.00 $1.00 Invest B1 $1.00 $0.00 $0.00 $0.00 $0.00 $0.00 Invest B3 $0.00 $0.00 $1.00 $0.00 $0.00 $0.00 Invest B5 $0.00 $0.00 $0.00 $0.00 $1.00 $0.00 Invest C1 $1.00 $0.00 $0.00 $0.00 $0.00 $0.00 Invest C4 $0.00 $0.00 $0.00 $1.00 $0.00 $0.00 Invest D1 $1.00 $0.00 $0.00 $0.00 $0.00 $0.00 Retums (per dollar) from investments Invest A2 Invest A3 Invest A4 hvest A5 Invest AB Invest B1 Invest B3 Invest B5 Invest C1 Invest C4 Invest D1 Invest A1 $1.0166 $1.0165 $1.0332 $1.0185 $1.0508 Month 2 Month 3 Month 4 Month 5 Month 6 Month 7 $1.0166 $1.0332 $1.0165 $1.0185 $1.0332 $1.0500 $1. 1028 Invest A1 Invest A2 Invest A3 Invest A4 hvest AS Invest AB Invest B1 Invest B3 Invest B5 Invest C1 Invest C4 Invest D1 Investments Amounts to invest Money allocated invested in month 1 Amount hested Cash after Investing Constraints on investments Returns fom Beginning of month Investments Month 2 Month 3 Month 4 Month 5 Month 8 Month 7 Required Payments $0.00 $125,000.00 $0.00 $125,000.00 $0.00 $150,000.00 Problem 3 Data on investments Month of investment Invest A1 Invest A2 Invest A3 Invest A4 Invest A5 Invest AB Invest B1 Invest B3 Invest B5 Invest C1 Invest C4 Invest D1 Month 1 $1.00 $0.00 50.00 $0.00 $0.00 $0.00 $1.00 $0.00 $0.00 $1.00 $0.00 $1.00 Month 2 $0.00 $1.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 Month 3 $0.00 $0.00 $1.00 $0.00 $0.00 $0.00 $0.00 Month 4 $0.00 $0.00 $0.00 $1.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $1.00 $0.00 Month 5 $0.00 $0.00 $0.00 $0.00 $1.00 $0.00 $0.00 $0.00 $1.00 $0.00 $0.00 $0.00 Month 6 $0.00 $0.00 $0.00 $0.00 $0.00 $1.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $1.00 $0.00 $0.00 $0.00 $0.00 Returns (per dollar) from investments Month 1 Month 3 Month 4 Month 5 Month 8 Month 2 $1.0165 Month 7 $1.0165 $1.0165 $1.0165 $1.0165 $1.0165 Invest A1 Invest A2 Invest A3 Invest A4 Invest A5 Invest AB Invest B1 Invest B3 Invest B5 Invest C1 Invest C4 Invest D1 $1.0332 $1.0332 $1.0332 $1.0506 $1.0506 $1.1028 Investments Invest A1 Invest A2 Invest A3 Invest A4 Invest A5 Invest AB Invest B1 Invest B3 Invest B5 Invest C1 Invest C4 Invest D1 Money allocated/invested in month 1 Constraints to meet payments Month Amount available 2 4 5 6 7 Amount required $0.00 $125,000.00 $0.00 $125,000.00 $0.00 $150,000.00 Problem 3 Data on investments Month of investment Invest A1 Invest A2 Invest A3 Invest A4 Invest AS Invest A6 Invest B1 Invest B3 Invest B5 Invest C1 Invest C4 Invest D1 Month 1 $1.00 $0.00 $0.00 $0.00 $0.00 $0.00 $1.00 $0.00 $0.00 $1.00 $0.00 $1.00 Month 2 $0.00 $1.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 S0.00 $0.00 $0.00 $0.00 Month 3 $0.00 S0.00 $1.00 $0.00 $0.00 S0.00 $0.00 $1.00 S0.00 $0.00 $0.00 $0.00 Month 4 $0.00 $0.00 $0.00 $1.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $1.00 $0.00 Month 5 $0.00 $0.00 $0.00 $0.00 $1.00 $0.00 $0.00 $0.00 $1.00 $0.00 $0.00 $0.00 Month 6 S0.00 $0.00 S0.00 $0.00 $0.00 $1.00 $0.00 S0.00 $0.00 $0.00 $0.00 S0.00 Retums (per dollar) fom investments Month 1 Month 3 Month 4 Month 5 Month 6 Month 7 Month 2 $1.0165 $1.0165 $1.0165 $1.0165 $1.0165 $1.0165 Invest A1 Invest A2 Invest A3 Invest A4 Invest AS Invest A6 Invest B1 Invest B3 Invest BS Invest C1 Invest C4 Invest D1 $1.0332 $1.0332 $1.0332 $1.0506 $1.0506 $1.1028 Investments Invest A1 Invest A2 Invest A3 Invest A4 Invest A5 Invest A6 Invest B1 Invest B3 Invest B5 Invest C1 Invest C4 Invest D1 Money allocated month 1 Objective to minimize, also a decision variable cell Constraints to meet payments Month Amount available 1 2 3 5 6 7 Amount required $0.00 $125,000.00 $0.00 $125,000.00 S0.00 $150,000.00 Problem 3 Data on investments Month ofinvestment Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 Invest A1 $1.00 $0.00 $0.00 $0.00 $0.00 $0.00 Invest A2 $0.00 $1.00 $0.00 $0.00 $0.00 $0.00 Invest A3 $0.00 $0.00 $1.00 $0.00 $0.00 $0.00 Invest A4 $0.00 $0.00 $0.00 $1.00 $0.00 $0.00 Invest A5 $0.00 $0.00 $0.00 $0.00 $1.00 $0.00 Invest AB $0.00 $0.00 $0.00 $0.00 $0.00 $1.00 Invest B1 $1.00 $0.00 $0.00 $0.00 $0.00 $0.00 Invest B3 $0.00 $0.00 $1.00 $0.00 $0.00 $0.00 Invest B5 $0.00 $0.00 $0.00 $0.00 $1.00 $0.00 Invest C1 $1.00 $0.00 $0.00 $0.00 $0.00 $0.00 Invest C4 $0.00 $0.00 $0.00 $1.00 $0.00 $0.00 Invest D1 $1.00 $0.00 $0.00 $0.00 $0.00 $0.00 Retums (per dollar) from investments Invest A2 Invest A3 Invest A4 hvest A5 Invest AB Invest B1 Invest B3 Invest B5 Invest C1 Invest C4 Invest D1 Invest A1 $1.0166 $1.0165 $1.0332 $1.0185 $1.0508 Month 2 Month 3 Month 4 Month 5 Month 6 Month 7 $1.0166 $1.0332 $1.0165 $1.0185 $1.0332 $1.0500 $1. 1028 Invest A1 Invest A2 Invest A3 Invest A4 hvest AS Invest AB Invest B1 Invest B3 Invest B5 Invest C1 Invest C4 Invest D1 Investments Amounts to invest Money allocated invested in month 1 Amount hested Cash after Investing Constraints on investments Returns fom Beginning of month Investments Month 2 Month 3 Month 4 Month 5 Month 8 Month 7 Required Payments $0.00 $125,000.00 $0.00 $125,000.00 $0.00 $150,000.00 Problem 3 Data on investments Month of investment Invest A1 Invest A2 Invest A3 Invest A4 Invest A5 Invest AB Invest B1 Invest B3 Invest B5 Invest C1 Invest C4 Invest D1 Month 1 $1.00 $0.00 50.00 $0.00 $0.00 $0.00 $1.00 $0.00 $0.00 $1.00 $0.00 $1.00 Month 2 $0.00 $1.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 Month 3 $0.00 $0.00 $1.00 $0.00 $0.00 $0.00 $0.00 Month 4 $0.00 $0.00 $0.00 $1.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $1.00 $0.00 Month 5 $0.00 $0.00 $0.00 $0.00 $1.00 $0.00 $0.00 $0.00 $1.00 $0.00 $0.00 $0.00 Month 6 $0.00 $0.00 $0.00 $0.00 $0.00 $1.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $1.00 $0.00 $0.00 $0.00 $0.00 Returns (per dollar) from investments Month 1 Month 3 Month 4 Month 5 Month 8 Month 2 $1.0165 Month 7 $1.0165 $1.0165 $1.0165 $1.0165 $1.0165 Invest A1 Invest A2 Invest A3 Invest A4 Invest A5 Invest AB Invest B1 Invest B3 Invest B5 Invest C1 Invest C4 Invest D1 $1.0332 $1.0332 $1.0332 $1.0506 $1.0506 $1.1028 Investments Invest A1 Invest A2 Invest A3 Invest A4 Invest A5 Invest AB Invest B1 Invest B3 Invest B5 Invest C1 Invest C4 Invest D1 Money allocated/invested in month 1 Constraints to meet payments Month Amount available 2 4 5 6 7 Amount required $0.00 $125,000.00 $0.00 $125,000.00 $0.00 $150,000.00 Problem 3 Data on investments Month of investment Invest A1 Invest A2 Invest A3 Invest A4 Invest AS Invest A6 Invest B1 Invest B3 Invest B5 Invest C1 Invest C4 Invest D1 Month 1 $1.00 $0.00 $0.00 $0.00 $0.00 $0.00 $1.00 $0.00 $0.00 $1.00 $0.00 $1.00 Month 2 $0.00 $1.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 S0.00 $0.00 $0.00 $0.00 Month 3 $0.00 S0.00 $1.00 $0.00 $0.00 S0.00 $0.00 $1.00 S0.00 $0.00 $0.00 $0.00 Month 4 $0.00 $0.00 $0.00 $1.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $1.00 $0.00 Month 5 $0.00 $0.00 $0.00 $0.00 $1.00 $0.00 $0.00 $0.00 $1.00 $0.00 $0.00 $0.00 Month 6 S0.00 $0.00 S0.00 $0.00 $0.00 $1.00 $0.00 S0.00 $0.00 $0.00 $0.00 S0.00 Retums (per dollar) fom investments Month 1 Month 3 Month 4 Month 5 Month 6 Month 7 Month 2 $1.0165 $1.0165 $1.0165 $1.0165 $1.0165 $1.0165 Invest A1 Invest A2 Invest A3 Invest A4 Invest AS Invest A6 Invest B1 Invest B3 Invest BS Invest C1 Invest C4 Invest D1 $1.0332 $1.0332 $1.0332 $1.0506 $1.0506 $1.1028 Investments Invest A1 Invest A2 Invest A3 Invest A4 Invest A5 Invest A6 Invest B1 Invest B3 Invest B5 Invest C1 Invest C4 Invest D1 Money allocated month 1 Objective to minimize, also a decision variable cell Constraints to meet payments Month Amount available 1 2 3 5 6 7 Amount required $0.00 $125,000.00 $0.00 $125,000.00 S0.00 $150,000.00
