Operations Research

...

Total 329,000 54,800

The second quarter cash flow balance equation is,

131.25SI,2 + 35SI,1 + 160SM,2 + 22SM,1 + L2 + 1.015C1

– 100PI,2 – 110PM,2 – AI,2 – AM,2 – 1.04L1 – C2 = 2,921,600

where the coefficients of SI,2 and SM,2 are determined as in the first quarter. The coefficients of SI,1 and SM,1 are the first quarters’ collections and are found in an analogous manner: (0.2)(175) = 35 and (0.11)(200) = 22. The right-hand side is calculated as for the first quarter using the collections, $78,400, given in the following table.

Second Quarter Cash Flow

Quarter IBM Macintosh

Sales Collections Sales Collections

Units $ % $ Units $ % $

1995-4 8,000 1,400,000 0.04 56,000 1,600 320,000 0.07 22,400

For the third quarter, the cash flow balance equation is,

131.25SI,3 + 35SI,2 + 7SI,1 + 160SM,3 + 22SM,2 + 14SM,1 + L3 + 1.015C2

– 100PI,3 –110PM,3 – AI,3 – AM,3 – 1.04L2 – C3 = 3,000,000

where the coefficients of SI,3, SM,3, SI,2, and SM,2 are determined as in the second quarter. The coefficients of SI,1 and SM,1 are: (0.04)(175) = 7 and (0.07)(200) = 14.

Finally, the last cash flow balance equation is similar to that in the third quarter

131.25SI,4 + 35SI,3 + 7SI,2 + 160SM,4 + 22SM,3 + 14SM,2 + L4 + 1.015C3

– 100PI,4 –110PM,4 – AI,4 – AM,4 – 1.04L3 – C4 = 3,000,000

(vi) Line of Credit. The last group of constraints has to do with the line of credit restrictions. There are eight constraints.

Lj < 750,000

for quarter j = 1, 2, 3, and 4.

(b) The spreadsheet table summarizing the formulation is shown on the next two pages.

2. The solution summary table on the two pages that follow the formulation gives the solutions, obtained by Excel’s Solver, to all parts of the case. All answers have been rounded off to the nearest whole number. The value of the objective function for this question is $12,960,954. Subtracting the $12 million fixed operating expenses and the $400,000 annual depreciation yields a net profit of $560,954.

3. (a) If prices are a little weak the objective function and constraints 33 through 36 change (cells M8:T8, M41:M43, N42:N44, O43:O44, P44, Q41:Q43, R42:R44, S43:S44; T44). The solution summary table shows that production and sales remain the same although the value of the objective function decreases 3% to $12,605,841. The net profit after fixed operating costs and depreciation decreases 63% to $205,841.

(b) If prices decrease to $150 and $175 for the IBM and Macintosh adapters the objective function and constraints 33 through 36 change (cells M8:T8, M41:M43, N42:Q44, O43:O44, P44, Q41:Q43, R42:R44, S43:S44; T44). The calculations of the new coefficients are shown in the table below. The solution is infeasible. This means that the company does not have sufficient resources to satisfy all the constraints, that is, sufficient cash to produce the adapters.

(c) If prices increase to $180 and $210 for the IBM and Macintosh adapters the objective function and constraints 33 through 36 change. The new coefficients are shown in the following table.

Constraint SI,1 SI,2 SI,3 SI,4 SM,1 SM,2 SM,3 SM,4

Objective 178.2 200.9

Constraint 33 135 164

Constraint 34 36 135 22.55 164

Constraint 35 7.2 36 135 14.35 22.55 164

Constraint 36 7.2 36 135 14.35 22.55 164

The solution summary table shows that production and sales remain the same although the value of the objective function increases 7% to $13,828,764. The net profit after fixed operating expenses and depreciation increases 155% to $1,428,764.

4. (a) If production costs are larger due to an error the objective function and constraints 33 through 36 change (cells E8:L8, U8:AB8, E41, F42, G43, H44, I41, J42, K43, L44). The coefficients of the production variables in the objective function and the four cash flow constraints, 33 through 36, now are -102 and -112 for the IBM and Macintosh adapters, respectively. In addition, the objective function coefficients of the inventory variables change to -3.06 and -3.36 for the IBM and Macintosh adapters,