Swamped with your writing assignments? Take the weight off your shoulder!
PROJECT 2: WHAT-IF ANALYSIS
The purpose of this project is to demonstrate your understanding of the following course concepts:
1. Analyzing data with Goal Seek.
2. Using What-If Analysis tools.
3. Using Solver.
4. Using Scenarios.
5. Summary Reports.
The automatic recalculation feature of Excel is a powerful tool that can be used to analyze worksheet data. What-if analysis is a decision-making tool in which changing input values recalculate formulas, in order to predict various possible outcomes. When new data is entered, Goal seek is a problem-solving method in which you specify a solution and then find the input value that produces the answer you want.
You are assisting Tony Sanchez, the office manager at Riverwalk Medical Clinic, with a disaster recovery plan for the medical records at the clinic. As part of that plan you are looking at two options for storing backup medical records. The first option is to purchase a storage facility, and the second is to use a third-party backup storage company that offers off-site small, medium, and large storage areas. In both cases, you want to keep your monthly payments below $17,000. You decide to use Goal Seek to look at various interest rates for purchasing a storage facility and to use Solver to help find the best possible combination of third-party storage areas. You will be working with the attached file: EX_2.1.xlsx.
1. Open the fie EX_2.1xlsx and save it as Project2.1_Records.xlsx.
2. With the Purchase Sheet active, use Goal Seek to find the payment for an interest rate of 5.75% on a Loan amount of $750,000 for 60 months. Use an additional loan amount of your choice with an interest rate of 6% to find the monthly payment for 48 months.
3. Activate the Rental Sheet. Open Solver, then use the Set Objective To option to maximize the storage amount in cell B12. (Hint: Select Max in the Set the Objective To area.).
4. In Solver, use the quantity, cells B6:D6, as the changing cells.
5. Add a constraint to Solver specifying the quantity in cells B6:D6 must be integers.
6. Add a constraint specifying that the total monthly payment amount in cell B11 is less than or equal to 17,000.
7. Generate a solution using Solver and accept Solverâ€™s solution.
8. Save the workbook and submit to the Assignment Folder.
As a senior financial analyst at North Shore CPA Services, you are researching various options for financing a $250,000 loan for the purchase of a new estate-planning facility. You havenâ€™t decided whether to finance the project for three, four, or five years. Each loan term carries a different interest rate. To help with the comparison, you summarize these options using a Scenario Summary. You will be working with the file EX_2.2.xls.
1. Open the fie EX_2.2.xlsx and save it as Project2.2_Loans.xlsx.
2. Create cell names for cells B4:B11 based on the labels in cells A4:A11, using the Create Names from Selection dialog box.
3. Use Scenario Manager to create scenarios that calculate the monthly payment on a loan amount of your choice under the three sets of loan possibilities listed below in Figure 1. For instance, in Figure 2 below, the loan amount of $250,000 was used. Note: You cannot use the loan amount of $250,000. (Hint: Create three scenarios, using cells B5:B6 as the changing cells.
FIGURE 1: SCENARIO, INTEREST RATES & TERMS
5% 5 year
4% 4 year
3% 3 year
FIGURE 2: NORTH SHORE CPA SERVICES
NORTH SHORE CPA SERVICES
Annual Interest Rate
Term in Months
4. View each scenario and confirm that it performs as intended, then display the 5% 5 Year scenario.
5. Generate a scenario summary titled Finance Options. Use cells B9:B11 as the Result cells.
6. Format the range B9:B11 as currency with two decimal places.
7. Delete the Current Values column in the report and the notes at the bottom of the report, Row 1, and column A.
8. Rename the sheet Estate Planning Project and assign a color of your choice.
9. Enter your name in the center footer section of both worksheets.
10. Save the workbook and submit to the Assignment Folder.
Please submit the following to your Assignments Folder:
1. The completed Project2.1_Records.xlsx.file.
2. The completed Project2.2_Loans.xlsx.file.
3. Submit a Project Reflection (see below).
Answer the following questions when you submit your assignments files.
a. What is the main purpose of using goal seek?
b. What were your additional amounts for monthly payments (in Part 1, Item 2), and what were the corresponding interest rates that Goal Seek found for each?
c. What decisions would you need to make when creating a worksheet to evaluate and analyze data?
d. What is one advantage of using scenarios?
e. What should you do before creating a scenario report to make the entries on the report easier to interpret?
f. What are the changing cells in a scenario?
Please submit your Project Reflection as a Microsoft Word document or just copy and paste your answers in your Assignments Folder’s text box when you submit your assignment along with the rest of the required project files.
HARD DUE DATE: LAST DAY OF CLASS | 11:59 PM EASTERN TIME (ET)
Late Period: This project will NOT be accepted after the end of the course. The Assignments Folder will be closed THE LAST DAY OF CLASS at 11:59PM ET (EASTERN TIME), and at this point you will not be able to physically submit the assignment. Exceptions may be allowed, on a case-by-case basis, for life situations (military deployment, medical illness, death in the family, etc). In all cases, timely notification of a “life situation” is critical to the approval of any extensions. All exceptions must be accompanied by official documentation, which is subject to inspection and approval. Work load, course load, vacations, or bad memory are not acceptable excuses.
Exceptions may be allowed, on a case-by-case basis, for life situations (military deployment, medical illness, death in the family, etc). In all cases, timely notification of a “life situation” is critical to the approval of any extensions. All exceptions must be accompanied by official documentation, which is subject to inspection and approval. Work load, course load, vacations, or bad memory are not acceptable excuses.
Running Close to the Deadline?: Please do not wait until the last minute to submit your assignment. Give yourself at least a 5-hour window to account for any technical difficulties that might arise. If you experience technical difficulties beyond your control that do not allow you to successfully complete the assignment, immediately follow the steps below:
Step 1: Contact UMGC 360 Help and Support. Inform them off the problem you are having. Get a problem ticket number from them to document the situation.
Step 2: E-mail me ASAP and include a descriiption of the problem you are having and your problem ticket number from 360 Help and Support, so I can investigate the situation.
Step 3: Attempt to attach your assignment to a message to me inside of LEO.
Step 4: E-mail the assignment to me.
This project is worth 40 points or 40% of your total grade for the course.
You will lose points for issues such as: not following directions, not submitting your work on time, and failure to include all required elements. Each omission will result in a partial point deduction.
SUBMITTING YOUR ASSIGNMENT
To complete this project and receive full credit, you must submit your completed presentation to your Assignments Folder unless you encounter problems–discussed above).
CYA (COPY YOUR ASSIGNMENT)
Please make sure you keep a copy of your project stored on your computer. Technical difficulties do happen–you may need to resubmit your assignment for a number of reasons. It is always a good policy to CYA!
Please contact me in advance if you are having problems understanding what is required of you.
DO YOUR OWN WORK
UMGC has strict policies regarding turning in work that is not 100% your own creation. I will enforce these policies.
VERIFY YOUR ASSIGNMENT HAS BEEN POSTED (*** VERY IMPORTANT ***)
It is your responsibility to make sure you have posted your assignment CORRECTLY! Once you have posted your assignment, immediately attempt to view it, just to make sure your post was accepted by LEO, that it is formatted correctly, and you have posted the correct file.
NOTE 1: You will be held responsible for posting your assignments correctly.