follow the instruction to complete excel homework

Problem: Data Exploration and Visualization in Excel
Download the file Quiz1-CreditRisk.xlsx and make the modifications listed below. Then upload the & submit the completed file on Canvas.
1) Define the data as a table. Change its name from the default “Table1” to CreditRiskData. The meaning of loan codes used in column A are given in a separate sheet called “LoanCodeList”. Define the contents of this sheet as a table and name it LoanCodes. 2) Add a conditional formatting to column D to visualize how long the person has been our customer using red bars. Then add another conditional formatting to the same column to put a “star” icon for 40+ months customers. (Hint: under icon sets, go to “more rules” and manually set the star to show when the value is >= number 40, and select “no icon” for other two cases). 3) Insert a new column after Loan Code, and call it Loan Purpose. Enter a VLOOKUP function in this column to look up the loan type corresponding to the loan code listed in column A, using the LoanCodes table. Note: You must use table reference instead of cell range reference. If the LoanCodes table expands in the future, your VLOOKUP formula should automatically adapt to it. 4) Insert a new column after Savings, and call it Total Balance. This column should calculate “Checking + Savings” for each customer. 5) Insert a new column at the right end of the table and call it Risky Customer. Enter an appropriate “IF” formula so that it shows “Yes” for single unskilled customers with high credit risk who have been our customer for only 24 months or less. Your function should write “No” if the customer is not of that kind.
Navigate to spreadsheet called User Interface:
6) In cell C2 put a formula to count how many risky customers there are (using the column you created in part (5)). Note: Your formula should use the flexible “Table[Column]” reference format, instead of referencing to a Excel column label or cell range. 7) In cell C6 we would like the user to enter a Loan Purpose and get the number of customers who applied for that kind of loan in C7 and the average age of those customers in cell C8. Write appropriate functions to accomplish that. We also want to make sure that the user enters a legitimate Loan Purpose. If the user enters some random text, Excel should reject that and show an error. Note: Your formulas should again use the flexible “Table[Column]” reference format.
Business Analytics and Spreadsheet Modeling Fall 2019
Back to the main data: Create the following charts using Pivot tables. For each chart, make visual improvements to the chart to make it as clean / informative as possible (e.g., remove the field buttons, etc. as we did in class).
8) A horizontal bar chart showing how many of each loan type has been given out. Your pivot table and chart should both be sorted to show the most frequent loan type on top. 9) Three pie charts (or donut charts), showing what percentage of our customers have: (1) Different marital status, (2) Different housing status, (3) Different job types. Youâ€d need a separate pivot table for each of these charts. We want the chart to show the “percentages” and not the counts. Itâ€s typically nice to sort the values from largest to smallest on a pie chart. 10) A histogram showing the Age distribution of our customers. Specifically, we would like to know how many customers we have within 20 to 70 years of age, in 5-year increments (that is, your histogram should show the number of customers with an age of <20, 20-25, 25-30, …, 65-70, >70). Note: Youâ€d need to use the “Group” feature of pivot table. Also, there should not be a gap between bars in a histogram, so, adjust the gap width appropriately. 11) Create one more chart (of any kind) that you think would give the management some new and useful insight / perspective into this data. Make sure your chart is properly labeled so itâ€s clear what itâ€s showing.
Finally, create a blank sheet in the file and call it “Dashboard”.
12) Copy/paste all your charts from parts (8)-(11) and arrange them nicely into a dashboard. 13) Add three slicers to this dashboard, for “Loan Type”, “Credit Risk”, and “Risky Customer”. Make sure these slicers are linked to all pivot tables/charts. 14) Arrange your sheets in this order: CreditRiskData, User Interface, Dashboard, [then other sheets in any order]. Have your CreditRiskData sheet on top when saving your file before closing (so itâ€s the first thing I see when opening your file).

Do you need a similar assignment done for you from scratch? We have qualified writers to help you. We assure you an A+ quality paper that is free from plagiarism. Order now for an Amazing Discount! Use Discount Code “Newclient” for a 15% Discount!NB: We do not resell papers. Upon ordering, we do an original paper exclusively for you.

The post follow the instruction to complete excel homework appeared first on Essay Writers.