Rob$$s Coffee Ltd$.$ is initiating a retirement benefit scheme for its workforce. The organization plans to commence financing the scheme with a deposit of $$50,000$ on January $1,2018.$ It intends to make an additional investment of $$12,000$ after one year and continue adding funds $($increasing by $$2,000$ annually$)$ on January $1$ of each subsequent year from $2020$ through $2032.$ To secure these disbursements, the corporation aims to procure a variety of bonds. The organization has access to Bond X$,$ Bond Y and Bond Z$.$ Bond X is priced at $$970$ per unit and will yield a $$65$ coupon on January $1$ every year from $2019$ through $2022,$ along with a final payment of $$1,065$ on January $1,2023.$ Bond Y is priced at $$980$ and will generate a $$73$ coupon on January $1$ annually from $2019$ through $2028,$ with a final payment of $$1,073$ on January $1,2029.$ Bond Z is priced at $$1,025$ and will provide an $$85$ coupon on January $1$ each year from $2019$ through $2031,$ along with a final payment of $$1,085$ on January $1,2032.$ The company's cash reserves accrue interest at a rate of $4\mathrm{.}5\%.$ Assuming the company desires to purchase bonds on January $1,2018,$ and is permitted to acquire them in fractional units, what is the optimal amount the company should invest in the various bonds and cash account to finance this scheme until January $1,2032,$ while minimizing costs?

A$.$ Create a spreadsheet model in Microsoft Excel and solve it using the built in Solver.

B$.$ What is the objective $($i$.$e$.$ minimization or maximization$)?$

C$.$ What is the optimal number of Bond X$,$ Bond Y and Bond Z to purchase? $($Note: the optimal solution will result in objective function value of $$246,768\mathrm{.}75)$

Answer rating: 100% (QA)

To solve this problem using Microsoft Excel and Solver well follow these steps A Create a spreadshee... View the full answer