"I have asked the same question, but I cannot get the answer I want, so I ask again. Add pictures to get the correct answer."

Solve the problem through the excel solver, please.

42. Based on Sartoris and Spruill (1974). Wivco produces two products, which it sells on both a cash and credit basis. Revenues from credit sales will not have been received but are included in determining profit earned during the current 6-month period. Sales during the next 6 months can be made either from units produced during the next 6 months or from beginning inventory. Relevant information about products 1 and 2 is as follows.

During the next 6 months, at most 150 units of product 1 can be sold on a cash basis, and at most 100 units of product 1 can be sold on a credit basis. It costs $35 to produce each unit of product 1, and each sells for $40. A credit sale of a unit of product 1 yields $0.50 less profit than a cash sale (because of delays in receiving payment). Two hours of production time are needed to produce each unit of product 1. At the beginning of the 6-month period, 60 units of product 1 are in inventory.

During the next 6 months, at most 175 units of product 2 can be sold on a cash basis, and at most 250 units of product 2 can be sold on a credit basis. It costs $45 to produce each unit of product 2, and each sells for $52.50. A credit sale of a unit of product 2 yields $1.00 less profit than a cash sale. Four hours of production time are needed to produce each unit of product 2. At the beginning of the 6-month period, 30 units of product 2 are in inventory.

During the next 6 months, Wivco has 1000 hours for production available. At the end of the next 6 months, Wivco incurs a 10% holding cost on the value of ending inventory (measured relative to production cost). An opportunity cost of 5% is also assessed against any cash on hand at the end of the 6-month period.

a. Develop and solve an LP model that yields Wivcos maximum profit during the next 6 months. What is Wivcos ending inventory position? Assuming an initial cash balance of $0, what is Wivcos ending cash balance?

b. Because an ending inventory and cash position of $0 is undesirable (for ongoing operations), Wivco is considering other options. At the beginning of the 6-month period, Wivco can obtain a loan (secured by ending inventory) that incurs an interest cost equal to 5% of the value of the loan. The maximum value of the loan is 75% of the value of the ending inventory. The loan will be repaid one year from now. Wivco has the following goals (listed in order of priority):

Goal 1: Make the ending cash balance of Wivcocome as close as possible to $75.

Goal 2: Make profit come as close as possible to the profit level obtained in part a.

Goal 3: At any time, Wivcos current ratio is defined to be

Current ratio = (Wivco's assets) / (Wivco's liabilities)

Assuming initially that current liabilities equal $150, 6 months from now Wivcos current ratio will equal

Current ratio = (CR + AR + EI) / (150 + Size of loan)

where CB is the ending cash balance, AR is the value of accounts receivable, and EI is the value of the ending inventory. Six months from now, Wivco wants the current ratio to be as close as possible to 2.

Use goal programming to determine Wivcos production and financial strategy.

B D E F $0 5% 5% 75% $150 Product 1 Product 2 60 30 1 Wivco sales 2 3 Beginning cash 4 Opportunity cost on ending cash 5 6 Interest rate on loan 7 Max loan % of end inventory value) 8 9 Initial current liabilities 10 11 12 Beginning inventory 13 14 Unit costs, revenues 15 Production cost 16 fd 17 Revenue from cash sale 18 Revenue from credit sale 19 20 21 Unit production times 22 23 24 Units produced 25 26 Units sold 27 Cash sales 28 Credit sales 29 Total 30 31 Available 20 32 33 Hour constraint Product 1 Product 2 $35.00 $45.00 $3.50 $4.50 $40.00 $52.50 $39.50 $51.50 Product 1 Product 2 2.00 4.00 Product 1 Product 2 Product 1 Product 2 Upper limits 150 175 250