Complete the assignment below using Microsoft Excel.
You have been hired by an engineering firm needing a senior level data analyst. Upon accepting this position, your supervisor has a variety of data analysis task for you to complete using PMT and What-IF-Analysis functions. In this process, your supervisor wants you to complete all steps below:
- Open a new Microsoft Excel workbook and rename any sheet tab of choice to “My Car” or “My Truck.”
- Use the illustration and further steps below to set up and format your worksheet.
- Merge and center cells A1 and I1 and then enter a title “My Car” or “My Truck.”
- Using a color of choice, fill in cells C3 through I10 and in this colored region, include any picture of choice to represent a car or truck of choice. Nicely format and place this picture within this area.
- Then from cell A4 through A10, enter the following labels:
- Purchase Price
- Down Payment
- Trade-in
- Amount Financed
- Term of loan (in years)
- Annual Interest Rate
- Monthly Payment
- Then from cell B4 through B6, enter a purchase price, down payment, and trade-in value of choice. Then create a formula to calculate amount financed in cell B7. For example, a formula to use in this case would be “=B4-(B5+B6)”.
- Then from cell B8 through B9, enter the term of loan in years and an interest rate of choice. Then use a PMT function to determine the monthly payment in cell B10. For example, a formula to use in this case for cell B10 would be “=-PMT(B9/12,B8*12,B7)”.
- The illustration below will offer more support on input of all required formulas along with sample data input:
- Once all data and calculations are working, do any other housekeeping to include professional format of choice to enhance presentation of data.
- Repeat steps 4 through 12 creating another similar model for a house of choice and place this loan analysis model in the same workbook but in a new worksheet naming the worksheet “My Home.”
- Once both PMT_Models are complete, go back to My Car or My Truck worksheet and use the illustration below and further enhance the loan analysis using What-IF-Analysis.
- In cell A12, type the label Annual Interest Rate. Then merge and center cells B12 through I12 and enter the label Term of Loan (in years).
- In cell A13, equal this to cell reference B10. Fill this cell with a color of choice and use the same color for the text color to hide this value.
- Starting with cell A14 and ending with A35, enter some interest rates. Make sure data is centered and formatted as a percentage.
- Starting with cell B13 through I13, enter some additional term of loan options in years and make sure data is centered.
- Using your mouse, select all cells in the range from A13 through I35. Then select the data tab and then the What-If-Analysis tool. From the What-If-Analysis options select Data Table. At this point, a small dialog window will appear asking for Row and Column input cells. For the Row Input, select the value associated with years in the loan which would be an absolute reference as $B$8. For the Column Input, select the value associated with interest rate which would be an absolute reference as $B$9.
- To get a better understanding of the purpose and uses of the What-If-Analysis, be sure to explore www.youtube.com and the keywords “Microsoft Excel What-If-Analysis” to see additional demonstrations.
- Once all data and calculations are working, do any other housekeeping to include professional format of choice to enhance presentation of data. Below is an illustration of what your My Car or My Truck worksheet will look similar too:
- Repeat steps 11 through 18 creating another similar What-IF-Analysis model for the My House worksheet.
- Save and be sure the name of the Microsoft Excel Workbook is “M7_YourLastName”.
Assignment Expectations
Structure and Format: Follow all 20 steps using ideally Microsoft Excel.
File name: Name your saved file according to your first initial, last name, and the assignment number (for example, “RHall Assignment 1.xlsx”)