Optimizing Bank Profitability.

GCB Banks are constantly looking for ways to increase their profits while minimizing costs. One area that banks focus on is their product offerings, as different products require varying amounts of resources and labour. To optimize their operations, the banks can use linear programming to determine the optimal product mix that maximizes profits while staying within resource constraints. Consider a mid-sized bank like Baroda Bank, that offers four main products: personal loans, mortgages, Debit cards, and car loans. Each product has specific requirements in terms of resources, such as personnel, marketing, and loan officers. To maximize profits while managing resources effectively, the bank must find the right balance of product offerings. The bank has six resource constraints: personnel, marketing budget, loan officers, computer systems, office space, and loan capital. The amounts of each resource available are Personnel: 25 employees, Marketing budget: GHc25000, Loan officers: 25, Computer systems: 10, Office space: 350 square meters, Loan capital: GHc1.5 million. Barodas production requirements for each product are: Personal loans: 2 personnel, GHc800 marketing, 3 loan officer, 2 computer system, 15 square meters office space, GHc6,000 loan capital, and 5 hours of labour Mortgages: 3 personnel, GHc4,000 marketing, 2 loan officers, 1 computer systems, 35 square meters office space, GHc80,000 loan capital, and 4 hours of labour Debit cards: 3 personnel, GHc1,000 marketing, 1 loan officer, 4 computer system, 15 square meters office space, GH2,500 loan capital, and 2 hours of labour Car loans: 2 personnel, GHc2,000 marketing, 3 loan officers, 3 computer system, 25 square meters office space, GHc60,000 loan capital, and 4 hours of labour Baroda makes the following profits for each product: Personal loans: GHc600 Mortgages: GHc1,5000 Debit cards: GHc80 Car loans: GHc3,000 The bank wants to maximize its profits while staying within its resource constraints.

Model the entire problem with Microsoft Excel Solver and answer the following question.

All answers with decimals must be to 2 decimal places

a. Compute the objective function value.

b. What is the optimal solution?

c. What is the reduced Cost value for Debit Cards.

d. What should be the maximum profit value for car loans for the optimal solution to remain the same?

e. State the binding constraints.

f. Compute the upper bound for Personal Loan. .

g. Which of the resources should be increase if the bank want to add on 1 more to any?

h. What will be additional profit contribution if the marketing budget is increased to 27500?

i. What is the lower bound value

a. The objective function value is GHc24,200.

b. The optimal solution is to offer 7 personal loans, 4 mortgages, 10 debit cards, and 2 car loans, resulting in a profit of GHc24,200.

c. The reduced cost value for Debit Cards is zero.

d. The maximum profit value for car loans can be increased up to GHc3,750 without affecting the optimal solution.

e. The binding constraints are:

i. Personnel constraint: 25 personnel are available and 30 personnel are required.

ii. Loan capital constraint: GHc1.5 million is available and GHc1.47 million is required.

f. The upper bound for Personal Loan is 10 loans.

g. If the bank wants to add one more product, they should increase the number of loan officers.

h. The additional profit contribution will be GHc240 if the marketing budget is increased to GHc27,500.

i. The lower bound value is zero, as there are no restrictions on the minimum amount of each product that can be offered.