PLEASE WRITE OUT FORMULATION AND ALL EXCEL FUNCTIONS

Eiji has just inherited the Hokama Industries and is excited to run the business. In order to rein in costs, he has negotiated with the employees union to let some employees retire early and others transition away from Hokama over the next six years. As part of this agreement, he needs to make sure Hokama has sufficient cash to make the following agreed annual payments for these departing employees. The amounts are in thousands of dollars.

year	1	2	3	4	5	6
payment	190	220	250	285	320	460

Table 1: Hokama Industries Payments

The annual payments must be made at the beginning of each year. The union will approve an amount that, along with earnings on its investment, will cover the annual payments. To minimize risk, in- vestment of the funds will be limited to savings (at 4% annually) and government securities, at prices quoted on The Wall Street Journal. Eiji is excited to use the knowledge learned from this class to create a plan for making the annual payments by investing in the following securities (par value = 1000). Any amounts not invested in these securities will be placed in savings.

security	current price	rate %	years to maturity
1	$1050	6.750	3
2	$1000	5.025	4
3	$1010	5.350	3

Table 2: Hokama Industries Payments Assume that interest is paid annually. Once Eiji finalizes the plan, he will present it to the union for final approval before implementation. Then, Hokama will deposit the amount with a trustee to fund the plan.

(a) Write down a formulation that will help Eiji implement this plan. (8 points) 6

(b) Using Excel, solve the formulation you wrote down above to find the minimum cash settlement needed to fund this plan. (4 points)

(c) Suppose Eiji can negotiate with the union to have the annual payments made at the end of the year instead. Reformulate this problem to accommodate this change. How much would Hokama save if the union agrees to this change? (8 points)