PLEASE ANSWER ALL OF Part b. FOR THIS QUESTION

Part b.

The upper management of CAL-BUS Mutual Funds also considering another scenario with the following budgetary and managerially imposed constraints

Cal-Bus should be investing all available $10,000,000.

The Europe investments cannot exceed the 20% of the overall budget.

Total Oil Investments cannot exceed the 30% of the total budget.

Total Steel Investments cannot exceed the 50% of the total budget.

Cal-Bus should invest on Pittsburgh funds at least 50% of the budget.

Cal-Bus should invest on New York City funds at least 25% of the total budget.

Cal-Bus should invest on Europe funds at least 15% of the total budget.

At most 45% of the budget can be spent on government bonds.

Oil sector investments in Europe should be less than Steel sector investments in Europe.

- Please submit two files: 1) Report file in Microsoft Word format 2) All of the solutions in Microsoft Excel format.

- Your report file must include all necessary output/graphs. All graphs, figures etc. should have a caption.

- In your report (MS Word File), please include your mathematical model, excel output, and list the amount of investments for each investment opportunity in a table and interpret your findings.

As a business analyst in a consulting firm, you are asked to:

1. Analyze an investment problem for CAL-BUS Mutual Funds so that they can determine the best strategy to maximize the total profit.

2. Create a spreadsheet model for a retirement planning problem

The upper management has provided you as much information as they have, and it is up to you to determine the best methods to answering their questions. As a business analyst, it is crucial that you explain the analysis and calculations (if you did not apply a procedure, explain why you did not use it) in such a way that a group of people unfamiliar with quantitative analysis can understand your findings. If you just provide a numerical answer to their questions, while still helpful, this will not meet the expectations for the project.

Your manager has requested that you answer their questions in the form of a business report in Microsoft Word format, you are also required to provide the solution file(s) in Microsoft Excel format. Simply typing in answers to their questions below will be construed as a lack of professionalism. The writing must be free of errors and easy to understand for someone unfamiliar with quantitative analysis. The exact design of the report is up to you, as upper management does not really care about the aesthetics as long as you answer all of their questions in an organized and coherent report. The upper management specifically requests that you include any output files or graphs (Microsoft Excel files) that you had done to be completely transparent. Any graphs or diagrams can be included in the body of the report with an appropriate title.

Linear Optimization

Cal-Bus Mutual Funds, Inc. located in Pittsburgh. Cal-Bus just obtained $10,000,000 by converting industrial bonds to cash and is now looking for other investment opportunities for these funds. Based on Cal-Buss current investments, the firms top financial analyst recommends that all new investments be made in the oil industry, steel industry, or government bonds in US and Europe. The investments, locations and rates of return are shown in the following table

What portfolio recommendations investments and amounts should be made for the available $10,000,000 to maximize the projected rate of return?

Introduction As a business analyst in a consulting firm, you are asked to analyze 1. Analyze two investment problems for CAL-BUS Mutual Funds. The upper management of CAL-BUS Mutual Funds is willing to determine the best strategy to maximize the total profit. 2. Create a spreadsheet model for a retirement planning problem The upper management has provided you as much information as they have, and it is up to you to determine the best methods to answering their questions. As a business analyst, it is crucial that you explain the analysis and calculations (if you did not apply a procedure, explain why you did not use it) in such a way that a group of people unfamiliar with quantitative analysis can understand your findings. If you just provide a numerical answer to their questions, while still helpful, this will not meet the expectations for the project. Deliverable Your manager has requested that you answer their questions in the form of a business report in MS Word format, you are also required to provide the solution file(s). Simply typing in answers to their questions below will be construed as a lack of professionalism. The writing must be free of errors and easy to understand for someone unfamiliar with quantitative analysis. The exact design of the report is up to you, as upper management does not really care about the aesthetics as long as you answer all of their questions in an organized and coherent report. The upper management specifically requests that you include any output files or graphs (MS Excel files) that you had done to be completely transparent. Any graphs or diagrams can be included in the body of the report with an appropriate title. Questions (Please note that there are two parts in this question: Part a and b) 1. Linear Optimization Cal-Bus Mutual Funds, Inc, located in Pittsburgh. Cal-Bus just obtained $10,000,000 by converting industrial bonds to cash and is now looking for other investment opportunities for these funds. Based on Cal-Bus's current investments, the fin's top financial analyst recommends that all new investments be made in the oil industry, steel industry, or government bonds in US and Europe. The investments, locations and rates of retum are shown in the following table. Investment Opportunities for Cal-Bus Mutual Funds Location Investment Projected Rate of Return% B&E Steel Oakland Oil Pittsburgh Steel Atlantic Oil Pacific Oil Huber Steel Midwest Steel Goverment Bonds Buckingham Steel Eiffel Oil Florence Oil EU Government Bonds 5.3 8,6 6.8 Europe New York City Pittsburgh 8.9 10,5 6.8 6.5 4.5 8.2 7.2 7.6 What portfolio recommendations - investments and amounts - should be made for the available $10,000,000 to maximize the projected rate of return? Please note that you need to develop two mathematical models (two separate excel spreadsheet) for part a and b. D 7.3 A B 1 Investment Opportunities for Cal-Bus Mutual Funds 2 Location Investment Projected Rate of Return 3 Pittsburgh B&E Steel 4 Pittsburgh Oakland Oil 8.6 5 Pittsburgh Pittsburgh Steel 6.8 6 New York City Atlantic Oil 8.9 7 New York City Pacific Oil 10.5 8 New York City Huber Steel 6.8 9 New York City Midwest Steel 6.5 10 New York City Government Bonds 4.5 11 Europe Buckingham Steel 8.2 12 Europe Eiffel Oil 7.2 13 Europe Florence Oil 7.6 14 Europe EU Government Bonds 5.3 15 Introduction As a business analyst in a consulting firm, you are asked to analyze 1. Analyze two investment problems for CAL-BUS Mutual Funds. The upper management of CAL-BUS Mutual Funds is willing to determine the best strategy to maximize the total profit. 2. Create a spreadsheet model for a retirement planning problem The upper management has provided you as much information as they have, and it is up to you to determine the best methods to answering their questions. As a business analyst, it is crucial that you explain the analysis and calculations (if you did not apply a procedure, explain why you did not use it) in such a way that a group of people unfamiliar with quantitative analysis can understand your findings. If you just provide a numerical answer to their questions, while still helpful, this will not meet the expectations for the project. Deliverable Your manager has requested that you answer their questions in the form of a business report in MS Word format, you are also required to provide the solution file(s). Simply typing in answers to their questions below will be construed as a lack of professionalism. The writing must be free of errors and easy to understand for someone unfamiliar with quantitative analysis. The exact design of the report is up to you, as upper management does not really care about the aesthetics as long as you answer all of their questions in an organized and coherent report. The upper management specifically requests that you include any output files or graphs (MS Excel files) that you had done to be completely transparent. Any graphs or diagrams can be included in the body of the report with an appropriate title. Questions (Please note that there are two parts in this question: Part a and b) 1. Linear Optimization Cal-Bus Mutual Funds, Inc, located in Pittsburgh. Cal-Bus just obtained $10,000,000 by converting industrial bonds to cash and is now looking for other investment opportunities for these funds. Based on Cal-Bus's current investments, the fin's top financial analyst recommends that all new investments be made in the oil industry, steel industry, or government bonds in US and Europe. The investments, locations and rates of retum are shown in the following table. Investment Opportunities for Cal-Bus Mutual Funds Location Investment Projected Rate of Return% B&E Steel Oakland Oil Pittsburgh Steel Atlantic Oil Pacific Oil Huber Steel Midwest Steel Goverment Bonds Buckingham Steel Eiffel Oil Florence Oil EU Government Bonds 5.3 8,6 6.8 Europe New York City Pittsburgh 8.9 10,5 6.8 6.5 4.5 8.2 7.2 7.6 What portfolio recommendations - investments and amounts - should be made for the available $10,000,000 to maximize the projected rate of return? Please note that you need to develop two mathematical models (two separate excel spreadsheet) for part a and b. D 7.3 A B 1 Investment Opportunities for Cal-Bus Mutual Funds 2 Location Investment Projected Rate of Return 3 Pittsburgh B&E Steel 4 Pittsburgh Oakland Oil 8.6 5 Pittsburgh Pittsburgh Steel 6.8 6 New York City Atlantic Oil 8.9 7 New York City Pacific Oil 10.5 8 New York City Huber Steel 6.8 9 New York City Midwest Steel 6.5 10 New York City Government Bonds 4.5 11 Europe Buckingham Steel 8.2 12 Europe Eiffel Oil 7.2 13 Europe Florence Oil 7.6 14 Europe EU Government Bonds 5.3 15