Based on the case, Answer my question.

Case: Equuleus Car Sharing.: Revenue Management

Linear Programming Model. Develop a linear programming model to maximize the net present value of net income. Submit the model in its mathematical form in the Appendix section. Use LINGO or Excel Solver to solve the model and answer the following questions.

Modeling Tips:

Decision variables: There should be 9 main decision variables: the number of each car type, the amount of the down payment, the amount of payment in Year 1 and Year 2, and the amount of interest in Year 1 and Year 2. You can define additional definitional decision variables to make the model easier to read.

Net present values: the NPV of the two-year revenue streams for each vehicle type is obtained by discounting the first-year revenues by the discount rate = 10% and the second-year revenues twice by 10%. These are the coefficients required to set up the objective function. As an example, _ = 15000/1+ + 28000/(1 + )2 = 36776.86

The objective function: the objective is to maximize the NPV of the net income. It consists of the discounted revenues less the discounted expenses. The expenses include the down payment, first- year payment, second-year payment, and interest (years 1 and 2). Constraints: there are a number of constraints to be formulated. They include o The budget o The minimum and maximum proportional requirements for each car type. o The payment schedule to the total purchase price of the cars. o The 20% down payment and 50% first-year payment requirements. o The amount of interest paid at the end of the first and second years. o Constraints that ensure that the cash flows from car rentals equals or exceeds the requisite payment schedule for the first and second years. o Some decision variables can assume only integer values. Use @GIN() in LINGO to define integer decision variables

Question:

Decision Variables clearly define the decision variables Objective Function Use the decision variables defined above to define the objective Constraints state each constraint in its mathematical form (e.g., A + B >= 3) and add comments to improve the models readability

Linear Programming model Template:

At the August 2017 board meeting of Equuleus Car Sharing Inc. (ECS), the primary focus was to establish ground rules for the firm's annual fleet purchasing schedule and to approve the planned expansion into new markets. ECS currently operated in three American cities (Seattle, Denver, and San Francisco). However, ECS's business model called for expanding into three much larger markets (New York, Boston, and Toronto) in the coming months. Typically, ECS replaced approximately half of its rental car fleet on an annual basis. This replacement cycle was consistent with ECS's mission of offering its customers up-todate vehicles at competitive prices. ECS's management team, headed by the chief executive officer, Doris Johnson, had established a budget of US $10 million 1 for funding the vehicle purchases that year. Traditionally, the firm paid cash when purchasing new cars; however, the current very low interest rates had caught the eye of the chief financial officer, Johnny Debonair. According to Debonair, preliminary discussions with a local finance company had revealed that ECS could make a down payment for the cars and then pay the remaining balance over a two-year period. In response to this, the vice-chair, Ben Johnston asked, "Why would we wish to pay interest when we can pay cash?" Debonair respond adroitly by saying, "By buying on time we can purchase many more vehicles, which should more than offset the paid interest based on maximizing net present income." He added, "The number of vehicles available at our planned three new locations would be severely limited under the cash model." As the meeting concluded, Johnson directed Debonair to conduct an analysis of both options, cash versus finance, using ECS's new analyticsbased resource management system, and to report back within two weeks. RENTAL CAR INDUSTRY The global rental car industry was estimated to reach approximately $125 billion in revenues by 2022 , which represented a compounded annual growth rate of 13.5 per cent from 2017 to 2022.2 Typically, the rental car industry was segmented into four broad sectors: rental, lease, purchase, and sharing. The carsharing business represented a small but growing component of this market. Basically, the car-sharing model focused on customers who wished to (1) utilize a vehicle for short periods of time, often by the hour; (2) replace corporate fleets; and (3) employ different types of vehicles for specific functions (e.g., minivan for a sporting event). Some of the major players in the global rental car industry included Enterprise Holdings, Inc.; Hertz Global Holdings, Inc.; Avis Rent a Car System, LLC; Europcar Groupe SA (France); and Sixt AG (Germany). Enterprise Holdings, Inc., the world's largest rental car firm, alone employed approximately 1.5 million cars. Additionally, most industrial nations had local car rental firms (e.g., Park24 Co., Ltd., in Japan) that primarily concentrated on the domestic market. The continued expansion of the car rental industry was driven primarily by growth in tourism on a global basis. However, there was growing concern in the environmental community regarding the impact of carbon dioxide emissions associated with the car rental industry. As a result, many of the major car rental firms were looking to go green, which involved increased use of all electric and hybrid vehicles. Another major challenge facing the rental car industry was the growing role of ridesharing firms like Uber Technologies Inc. (Uber) and Lyft, Inc. (Lyft). Their market share was continuing to grow as customers shifted from traditional car rental in the metropolitan areas to ridesharing, which eliminated the need for parking and driving in unfamiliar environs. The car rental firms had responded to this new threat by downsizing their car fleets. The dawn of the age of autonomous vehicles (i.e., self-driving cars) represented an even more serious threat to the established rental car business model. To that end, some of the larger rental car firms were in the process of inking agreements with Apple Inc. and Google, two of the current autonomous vehicle powerhouses, as well as with Uber and Lyft. 3 COMPANY BACKGROUND ECS, which was founded in 2014, offered several car-sharing options in three metropolitan areas-Seattle, Denver, and San Francisco. Specifically, it offered a city-wide service that rented vehicles by the hour, and a program that provided residents of large apartment complexes and universities access to vehicles on demand. Currently, customers could choose from four different vehicle types: minivan, sport utility vehicle, sedan, and economy. ECS had experienced significant growth over the past few years, with sales approaching $40 million annually. As a result, ECS planned to expand in the New York, Boston, and Toronto markets in the coming months. ECS's management believed that ridesharing represented a new zeitgeist for the rental car industry, and that ECS would be well positioned to take advantage of these new trends. Debonair understood, based on his preliminary investigation, that the finance company required at least a 20 per cent down payment, and that at least 50 per cent of the purchase price plus interest had to be paid by the end of the first year. The financing company was currently charging 4 per cent interest. ECS used a 10 per cent discount rate for financial planning, and based on inputs from operations, Debonair believed that all of the cars that were purchased could be rented. However, to meet overall market demand, Debonair wanted each vehicle category represent at least 15 per cent and no more than 50 per cent of the total number of vehicles purchased. Cars purchased were depreciated and paid off for over a two-year period, after which they were sold in a secondary market (see Exhibit 1 ). EXHIBIT 1: WHOLESALE PURCHASE PRICE AND REVENUE SUMMARY (US\$) Question 1. What vehicle purchase plan maximizes the NPV of net income, and what is the corresponding value of the objective function? Question 2. What is the payment schedule and interest paid per year based on the optimal solution? Exhibit 1: Wholesale Purchase Price and Revenue Summary (US\$) \begin{tabular}{|l|r|r|r|} \hline Car Type & Price (\$) per car & \begin{tabular}{c} Revenue per car \\ 1st Year (\$) \end{tabular} & \begin{tabular}{c} Revenue per car \\ 2nd Year (\$) \end{tabular} \\ \hline Minivan & $27,000.00 & $15,000.00 & $28,000.00 \\ \hline SUV & $22,000.00 & $10,000.00 & $19,000.00 \\ \hline Sedan & $18,000.00 & $9,000.00 & $21,000.00 \\ \hline Economy & $14,000.00 & $7,000.00 & $15,000.00 \\ \hline \end{tabular} Interest Rate and Discount Rate Interest Rate Discount Rate Decision Variables Decision Variables \# of Minivan purchased \# of SUV purchased \# of Sedan purchased \# of Economy purchased Down payment (\$) Principal Payment in Year 1 (\$) Interest Payment in Year 1 (\$) Principal Payment in Year 2 (\$) Interest Payment in Year 2 (\$) Constraints about the Purchasing Schedule Constraints about the Purchasing Schedule Constraint 1: each vehicle category represents at least 15% of the total number of vehicles purchased. \begin{tabular}{cl|l|l} & >= & 15% of total number \\ Minivan & & >= \\ SUV & & >= \\ Sedan & >= \\ Economy & >= \end{tabular} Constraint 2: each vehicle category represents no more than 50% of the total number of vehicles purchased. Minivan SUV Sedan Economy 0.04==> the rate may change depending on the questions. 0.12 = the rate may change depending on the questions. Definitional Decision Variables to help set up the next few constraints Total purchase price = Total amount borrowed = = Total purchase price - down payment Constraints about the Payment Plan Constraint 3: Down payment to the financing company cannot exceed the available budget $10 million: Hint: down payment = & 20% of total purchase price \\ \hline & \multicolumn{3}{|c|}{>=} \\ \hline \multicolumn{4}{|c|}{ Constraint 5: Total payment in Year 1 must be at least 50% of (the purchase price + interest) } \\ \hline Hint: & Total payment in Y1 & >= & 50% of (total purchase price + interest) \\ \hline & \multicolumn{3}{|c|}{>=} \\ \hline & & & \\ \hline \multicolumn{4}{|c|}{ Constraint 6: Interest in Year 1 is 4% of the remaining balance. } \\ \hline Hint: & Interest in Y1 & = & 4% of remaining balance \\ \hline & \multicolumn{3}{|c|}{=} \\ \hline \multicolumn{4}{|c|}{ Constraint 7: Interest in Year 2 is 4% of the remaining balance. } \\ \hline Hint: & Interest in Y2 & = & 4% of remaining balance \\ \hline & \\ \hline & & & \\ \hline \multicolumn{4}{|c|}{ Constraint 8: Need enough revenue in Year 1 to cover re-payment (principal + interest) } \\ \hline Hint: & Revenue in Y1 & >= & principal and interest in Y1 \\ \hline & \multicolumn{3}{|c|}{>=} \\ \hline & & & \\ \hline \multicolumn{4}{|c|}{ Constraint 9: Need enough revenue in Year 2 to cover re-payment (principal + interest) } \\ \hline Hint: & Revenue in Y2 & >= & principal and interest in Y2 \\ \hline & \multicolumn{3}{|c|}{>=} \\ \hline \multicolumn{4}{|c|}{ Constraint 10: Must payoff at the end of Year 2: the amount borrowed = total principal in Year 1 and Year 2} \\ \hline Hint: & total amount borrowed & = & principal in Y1 and Y2 \\ \hline & & = & \\ \hline \end{tabular} At the August 2017 board meeting of Equuleus Car Sharing Inc. (ECS), the primary focus was to establish ground rules for the firm's annual fleet purchasing schedule and to approve the planned expansion into new markets. ECS currently operated in three American cities (Seattle, Denver, and San Francisco). However, ECS's business model called for expanding into three much larger markets (New York, Boston, and Toronto) in the coming months. Typically, ECS replaced approximately half of its rental car fleet on an annual basis. This replacement cycle was consistent with ECS's mission of offering its customers up-todate vehicles at competitive prices. ECS's management team, headed by the chief executive officer, Doris Johnson, had established a budget of US $10 million 1 for funding the vehicle purchases that year. Traditionally, the firm paid cash when purchasing new cars; however, the current very low interest rates had caught the eye of the chief financial officer, Johnny Debonair. According to Debonair, preliminary discussions with a local finance company had revealed that ECS could make a down payment for the cars and then pay the remaining balance over a two-year period. In response to this, the vice-chair, Ben Johnston asked, "Why would we wish to pay interest when we can pay cash?" Debonair respond adroitly by saying, "By buying on time we can purchase many more vehicles, which should more than offset the paid interest based on maximizing net present income." He added, "The number of vehicles available at our planned three new locations would be severely limited under the cash model." As the meeting concluded, Johnson directed Debonair to conduct an analysis of both options, cash versus finance, using ECS's new analyticsbased resource management system, and to report back within two weeks. RENTAL CAR INDUSTRY The global rental car industry was estimated to reach approximately $125 billion in revenues by 2022 , which represented a compounded annual growth rate of 13.5 per cent from 2017 to 2022.2 Typically, the rental car industry was segmented into four broad sectors: rental, lease, purchase, and sharing. The carsharing business represented a small but growing component of this market. Basically, the car-sharing model focused on customers who wished to (1) utilize a vehicle for short periods of time, often by the hour; (2) replace corporate fleets; and (3) employ different types of vehicles for specific functions (e.g., minivan for a sporting event). Some of the major players in the global rental car industry included Enterprise Holdings, Inc.; Hertz Global Holdings, Inc.; Avis Rent a Car System, LLC; Europcar Groupe SA (France); and Sixt AG (Germany). Enterprise Holdings, Inc., the world's largest rental car firm, alone employed approximately 1.5 million cars. Additionally, most industrial nations had local car rental firms (e.g., Park24 Co., Ltd., in Japan) that primarily concentrated on the domestic market. The continued expansion of the car rental industry was driven primarily by growth in tourism on a global basis. However, there was growing concern in the environmental community regarding the impact of carbon dioxide emissions associated with the car rental industry. As a result, many of the major car rental firms were looking to go green, which involved increased use of all electric and hybrid vehicles. Another major challenge facing the rental car industry was the growing role of ridesharing firms like Uber Technologies Inc. (Uber) and Lyft, Inc. (Lyft). Their market share was continuing to grow as customers shifted from traditional car rental in the metropolitan areas to ridesharing, which eliminated the need for parking and driving in unfamiliar environs. The car rental firms had responded to this new threat by downsizing their car fleets. The dawn of the age of autonomous vehicles (i.e., self-driving cars) represented an even more serious threat to the established rental car business model. To that end, some of the larger rental car firms were in the process of inking agreements with Apple Inc. and Google, two of the current autonomous vehicle powerhouses, as well as with Uber and Lyft. 3 COMPANY BACKGROUND ECS, which was founded in 2014, offered several car-sharing options in three metropolitan areas-Seattle, Denver, and San Francisco. Specifically, it offered a city-wide service that rented vehicles by the hour, and a program that provided residents of large apartment complexes and universities access to vehicles on demand. Currently, customers could choose from four different vehicle types: minivan, sport utility vehicle, sedan, and economy. ECS had experienced significant growth over the past few years, with sales approaching $40 million annually. As a result, ECS planned to expand in the New York, Boston, and Toronto markets in the coming months. ECS's management believed that ridesharing represented a new zeitgeist for the rental car industry, and that ECS would be well positioned to take advantage of these new trends. Debonair understood, based on his preliminary investigation, that the finance company required at least a 20 per cent down payment, and that at least 50 per cent of the purchase price plus interest had to be paid by the end of the first year. The financing company was currently charging 4 per cent interest. ECS used a 10 per cent discount rate for financial planning, and based on inputs from operations, Debonair believed that all of the cars that were purchased could be rented. However, to meet overall market demand, Debonair wanted each vehicle category represent at least 15 per cent and no more than 50 per cent of the total number of vehicles purchased. Cars purchased were depreciated and paid off for over a two-year period, after which they were sold in a secondary market (see Exhibit 1 ). EXHIBIT 1: WHOLESALE PURCHASE PRICE AND REVENUE SUMMARY (US\$) Question 1. What vehicle purchase plan maximizes the NPV of net income, and what is the corresponding value of the objective function? Question 2. What is the payment schedule and interest paid per year based on the optimal solution? Exhibit 1: Wholesale Purchase Price and Revenue Summary (US\$) \begin{tabular}{|l|r|r|r|} \hline Car Type & Price (\$) per car & \begin{tabular}{c} Revenue per car \\ 1st Year (\$) \end{tabular} & \begin{tabular}{c} Revenue per car \\ 2nd Year (\$) \end{tabular} \\ \hline Minivan & $27,000.00 & $15,000.00 & $28,000.00 \\ \hline SUV & $22,000.00 & $10,000.00 & $19,000.00 \\ \hline Sedan & $18,000.00 & $9,000.00 & $21,000.00 \\ \hline Economy & $14,000.00 & $7,000.00 & $15,000.00 \\ \hline \end{tabular} Interest Rate and Discount Rate Interest Rate Discount Rate Decision Variables Decision Variables \# of Minivan purchased \# of SUV purchased \# of Sedan purchased \# of Economy purchased Down payment (\$) Principal Payment in Year 1 (\$) Interest Payment in Year 1 (\$) Principal Payment in Year 2 (\$) Interest Payment in Year 2 (\$) Constraints about the Purchasing Schedule Constraints about the Purchasing Schedule Constraint 1: each vehicle category represents at least 15% of the total number of vehicles purchased. \begin{tabular}{cl|l|l} & >= & 15% of total number \\ Minivan & & >= \\ SUV & & >= \\ Sedan & >= \\ Economy & >= \end{tabular} Constraint 2: each vehicle category represents no more than 50% of the total number of vehicles purchased. Minivan SUV Sedan Economy 0.04==> the rate may change depending on the questions. 0.12 = the rate may change depending on the questions. Definitional Decision Variables to help set up the next few constraints Total purchase price = Total amount borrowed = = Total purchase price - down payment Constraints about the Payment Plan Constraint 3: Down payment to the financing company cannot exceed the available budget $10 million: Hint: down payment = & 20% of total purchase price \\ \hline & \multicolumn{3}{|c|}{>=} \\ \hline \multicolumn{4}{|c|}{ Constraint 5: Total payment in Year 1 must be at least 50% of (the purchase price + interest) } \\ \hline Hint: & Total payment in Y1 & >= & 50% of (total purchase price + interest) \\ \hline & \multicolumn{3}{|c|}{>=} \\ \hline & & & \\ \hline \multicolumn{4}{|c|}{ Constraint 6: Interest in Year 1 is 4% of the remaining balance. } \\ \hline Hint: & Interest in Y1 & = & 4% of remaining balance \\ \hline & \multicolumn{3}{|c|}{=} \\ \hline \multicolumn{4}{|c|}{ Constraint 7: Interest in Year 2 is 4% of the remaining balance. } \\ \hline Hint: & Interest in Y2 & = & 4% of remaining balance \\ \hline & \\ \hline & & & \\ \hline \multicolumn{4}{|c|}{ Constraint 8: Need enough revenue in Year 1 to cover re-payment (principal + interest) } \\ \hline Hint: & Revenue in Y1 & >= & principal and interest in Y1 \\ \hline & \multicolumn{3}{|c|}{>=} \\ \hline & & & \\ \hline \multicolumn{4}{|c|}{ Constraint 9: Need enough revenue in Year 2 to cover re-payment (principal + interest) } \\ \hline Hint: & Revenue in Y2 & >= & principal and interest in Y2 \\ \hline & \multicolumn{3}{|c|}{>=} \\ \hline \multicolumn{4}{|c|}{ Constraint 10: Must payoff at the end of Year 2: the amount borrowed = total principal in Year 1 and Year 2} \\ \hline Hint: & total amount borrowed & = & principal in Y1 and Y2 \\ \hline & & = & \\ \hline \end{tabular}