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.
Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.
You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.Read more
Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.Read more
Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.Read more
Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.Read more
By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.Read more