As part of running a small business, owners are often committed to making payments or to receiving payments in the short-term and will turn to short-term financing options to help them through uncertain times. Promisingly, linear programming can help them determine what would be the most economic way to fulfill these cash commitments. In particular, Sunny Bicycles located in Ottawa has the following short-term cash commitments for the rest of the year. Month July August September October November December Net Cash Flow (in thousands of dollars) -150 -100 200 -200 50 300 In the table above, a negative or positive value denotes that the business is committed to paying or receiving the specified amount respectively at the start of the associated month. After discussing their options with financial specialists, Sunny Bicycles has determined that there are two sources of funds that are available to them to help them through the next few months and finance their cash flow requirements. The first option is to take out a loan from a line of credit that has offered them up to $100,000 per month at an interest rate of 1% per month. Their second option is a government loan program for small businesses that allows them to borrow money for the first three months and bears a total interest of 2% for the three-month period. For example, if they go with this option and borrow $X in July, they would need to pay back in October the loan amount ($X) plus 2% of that (2% * $X). If they start the program in August, then they would have to pay it back in November and so forth. Moreover, at the end of each month, any excess funds (i.e., cash left after fulfilling the cash commitments for that month) can be reinvest at an interest rate of 0.3% per month Your job, as a benevolent Telfer student this summer, is to help determine how Sunny Bicycles should schedule their short-term financing. The owner has asked you to develop a financing plan that will maximize their cash for the end of December. 1. Present the algebraic formulation for this problem. 2. Solve the problem using Excel Solver and remember to include the Excel layout, the answer report, and the sensitivity report. Additionally, the owner has the following questions for you, and you should provide your answers and justification based on the Excel sensitivity report. 3. There is a 6-month loan available only at the beginning of July that requires a total interest of 1% (i.e., every dollar borrowed in July has to be paid back at the end of December in an amount of 1.01 dollar). Would you recommend for Sunny Bicycles to borrow the money or not? 4. Recently, the owner was approached by another bank that can also provide the service of the line of credit. This bank proposed that if Sunny Bicycles switched to its services, it could increase the line of credit up to $200K, doubling the amount that the business currently has access to. The owner is quite satisfied with the current bank overall and wonders if it is worthy to make the switch. Provide your recommendation with detailed justification. 5. The owner foresees that there is a need to transfer out $40K sometime between July and December. The owner wonders which month it should make the transfer so that it has the minimum impact on the cash amount at the end of December. Which month would you recommend and how does that change the final cash amount? Provide detailed answers and analysis based on the sensitivity report. As part of running a small business, owners are often committed to making payments or to receiving payments in the short-term and will turn to short-term financing options to help them through uncertain times. Promisingly, linear programming can help them determine what would be the most economic way to fulfill these cash commitments. In particular, Sunny Bicycles located in Ottawa has the following short-term cash commitments for the rest of the year. Month July August September October November December Net Cash Flow (in thousands of dollars) -150 -100 200 -200 50 300 In the table above, a negative or positive value denotes that the business is committed to paying or receiving the specified amount respectively at the start of the associated month. After discussing their options with financial specialists, Sunny Bicycles has determined that there are two sources of funds that are available to them to help them through the next few months and finance their cash flow requirements. The first option is to take out a loan from a line of credit that has offered them up to $100,000 per month at an interest rate of 1% per month. Their second option is a government loan program for small businesses that allows them to borrow money for the first three months and bears a total interest of 2% for the three-month period. For example, if they go with this option and borrow $X in July, they would need to pay back in October the loan amount ($X) plus 2% of that (2% * $X). If they start the program in August, then they would have to pay it back in November and so forth. Moreover, at the end of each month, any excess funds (i.e., cash left after fulfilling the cash commitments for that month) can be reinvest at an interest rate of 0.3% per month Your job, as a benevolent Telfer student this summer, is to help determine how Sunny Bicycles should schedule their short-term financing. The owner has asked you to develop a financing plan that will maximize their cash for the end of December. 1. Present the algebraic formulation for this problem. 2. Solve the problem using Excel Solver and remember to include the Excel layout, the answer report, and the sensitivity report. Additionally, the owner has the following questions for you, and you should provide your answers and justification based on the Excel sensitivity report. 3. There is a 6-month loan available only at the beginning of July that requires a total interest of 1% (i.e., every dollar borrowed in July has to be paid back at the end of December in an amount of 1.01 dollar). Would you recommend for Sunny Bicycles to borrow the money or not? 4. Recently, the owner was approached by another bank that can also provide the service of the line of credit. This bank proposed that if Sunny Bicycles switched to its services, it could increase the line of credit up to $200K, doubling the amount that the business currently has access to. The owner is quite satisfied with the current bank overall and wonders if it is worthy to make the switch. Provide your recommendation with detailed justification. 5. The owner foresees that there is a need to transfer out $40K sometime between July and December. The owner wonders which month it should make the transfer so that it has the minimum impact on the cash amount at the end of December. Which month would you recommend and how does that change the final cash amount? Provide detailed answers and analysis based on the sensitivity report