problem in excel

questions based on problem

please show steps in excel

The credit union for state employees is planning the allocation of funds for the coming year. The credit union makes four types of loans available to its members. In addition, the credit union invests in risk-free securities to stabilize income. The various revenue producing investments together with annual rates of return are as follows: The credit union will have $2 million available for investment during the coming year. State laws and credit union policies impose the following restrictions on the composition of the loans and investments. - Risk-free securities may not exceed 30% of the total funds available for investment. - Signature loans may not exceed 10% of the funds invested in all loans (automobile, furniture, other secured, and signature loans). - Furniture loans plus other secured loans may not exceed the automobile loans. - Other secured loans plus signature loans may not exceed the funds invested in risk-free securities. How should the $2 million be allocated to each loan/investment alternatives to maximize total annual return? What is the projected total annual return? Create Answer, Sensitivity, and Limits reports. Hints: - You need a guess when completing problems using Solver. For this problem I will provide a guess: what is the return if you have $200,000 in auto loans, $300,000 in furniture loans, $400,000 other secured loans, $500,000 in Signature loans, and $600,000 in risk-free securities? - Securities are not loans. Be carefull - This problem emphasizes constraints. Your constraints don't have to be right next to your model. You can put them somewhere else on the spreadsheet. - If Solver is giving you error messages, see the Selected Solver Messages (pdf) document to see what may be wrong with your model. A common error is "does not converge". That typically means you are missing a constraint, look at the pdf to help find which one. Complete your model (Excel file) and complete the Model Quiz. You may repeat the Model Quiz after we review the model in class. Your final grade for the assignment will be the average of the two attempts. Complete your model showing the calculations in an Excel file. Use proper color-coding and do not hardcode. Check your model Look at the Answer report. How many constraints are listed at the bottom of the report? Select one: a. 1 b. 2 c. 3 d. 4 e. 5 On the Answer report, how many of the constraints are binding? Select one: a. 1 b. 2 c. 3 d. 4 e. 5 On the Answer Report, what is the Final Value of the Objective Cell? Round your answer to the nearest whole number. Example: $1,2341234 Answer: What is the total amount allocated to loans (not including securities)? Select one: a. $1,400,000 b. $2,000,000 c. $600,000 d. $630,000 Which of these would have the biggest impact on total return according to the Sensitivity report? Select one: a. Increasing the Risk-Free Securities by $1,000. b. Increasing the Automobile Loans by $1,000. c. Increasing the $2,000,000 funds available by $1,000. d. Increasing the Signature Loans by $1,000. For Questions 6-10, make the following changes to your model and rerun Solver: - The amount available to invest is $2,500,000. - Risk-Free Securities may not exceed 25% of the total funds available for investment. - Signature loans may not exceed 20% of the funds invested in all loans (automobiles, furniture, other secured, and signature loans). How much should be allocated to Automobile Loans? Select one: a. $630,000 b. $60,000 C. $750,000 d. $1,130,000 For Questions 6-10, make the following changes to your model and rerun Solver: - The amount available to invest is $2,500,000 - Risk-Free Securities may not exceed 25% of the total funds available for investment. - Signature loans may not exceed 20% of the funds invested in all loans (automobiles, furniture, other secured, and signature loans): How much should be allocated to Furniture Loans? Select one: a. $170,000 b. $17,000 c. $204,000 d. $500,000 For Questions 610, make the following changes to your model and rerun Solver: - The amount availoble to invest is $2,500,000. - Risk-Free Securities may not exceed 25% of the total funds avalable for investment. - Signature loans may not exceed 20% of the funds invested in all loans (autornobiles, furniture, other secured, and signature loons). How much should be allocated to Other Secured Loans? Round to the nearest $. Examples of accepted answers $1,234 1,234 Answer For Questions 6-10, make the following changes to your model and rerun Solver: - The amount available to invest is $2,500,000. - Risk-Free Securities may not exceed 25% of the total funds available for investment. - Signature loans may not exceed 20% of the funds invested in all loans (automobiles, furniture, other secured, and signature loans). On the Answer Report, what is the Final Value of the Objective Cell? Examples of accepted answers: $1,234 1,234 Answer: For Questions 6-10, make the following changes to your model and rerun Solver: - The amount available to invest is $2,500,000. - Risk-Free Securities may not exceed 25% of the total funds available for investment. - Signature loans may not exceed 20% of the funds invested in all loans (automobiles, furniture, other secured, and signature loans). To maximize their total return with the limitations specified in the problem, the credit union should allocate the most money to what loan/investment? Select one; a. Automobile Loans b. Furniture Loans c. Other Secured Loans d. Signature Loans e. Risk-Free Securities