Case Study Part V: Optimization and Forecasting
Download the Case Study Part V Banking Case Study Sample dataset provided for use in this case study.
Part 1: Optimization
Several of United Savings Bank’s (USB’s) branches want to optimize the amount of return on their bond investments. Create a model, using Microsoft Excel’s Solver, that will help them find the right investment amount for each of the below bonds given the following:
Total Amount to Invest: $500,000
| Bond | Annual Return | Maturity | Risk | Tax Free |
| Bond 1 | 8.50% | Long | High | Yes |
| Bond 2 | 8.00% | Long | Low | No |
| Bond 3 | 7.00% | Short | Low | Yes |
| Bond 4 | 8.00% | Long | High | Yes |
| Bond 5 | 8.00% | Short | High | No |
Constraints:
At least 50% of investments must be in short term investments
No more than 50% of investments are to be made in high risk investments
At least 30% of investments need to be made in tax free investments
Determine the amount that should be invested in each of the bonds to yield the largest return on investment, given the constraints.
Part 2: Forecasting
USB needs to set new car sales goals for the coming year. The CEO has asked you to forecast the number of new car sales based on past sales.
Using Excel, create a forecasting model that will allow USB to forecast approximately how many new cars will be sold, in total, in the next five years.
Plot your data and determine the regression line equation and R2.
Are there additional variables you might want to include in your model? Clean and prepare these variables, if needed, and create a new regression model in Excel using the directions from your Module 5 ebook readings- (Foreman) Chapter 6, The Granddaddy of Supervised Artificial Intelligence- Regression and (Black) Chapter 12 Simple Regression Analysis and Correlation in your custom ebook.
Provide a one-page executive summary that encapsulates the forecasting model that you have created and identify any forecasting errors and any trending analyses that you are able to determine.