INFS 401 Information Management EXCEL PROJECT - GolfRUS You have been employed as a consultant to Golf R Us, Inc. The company has been having problems with account receivables and they want to use various dunning methods to better their receivables. But first, they need to compile all the customers who have not paid so that they can demand payment that includes finance charges. Two files which contain what products were bought, how many balls were bought, when the balls have been bought, and payment history have been extracted out of different database tables. Rong Wright, the manager of Golf R Us, has asked you to combine the different files into one Excel worksheet and clean up the data. He wants you to name the worksheet as "Customers". One of the files contains a Customer ID column. The Customer_ID is a result of an algorithm that is employed by the company. The algorithm used by the company is as follows: The format of ID for each customer needs to be "Iastname" followed by an underscore followed by a number starting with "O001". The company added the character " D " to the end of a created ID if there is a repeat of any last name. (For example, let us say that we have an ID "Madigan_0001"; if there are multiple Madigan as last names, the first Madigan will be "Madigan_0001", the next Madigan will be "Madigan_0002D", the following one will be "Madigan_0003D", and so on.The next name Mae will be Mae_0004). You must replicate this algorithm first using text manipulation techniques. Then, you can use this new ID (for example, Madigan_0002D, etc) to find the number of balls purchased by that individual from the text file. The price catalog of the products is as follows: If any purchases are shown in the text files later than 10/14/2023, you can disregard that data as they are planned future purchases. The finance charges for delinquent accounts as of October 14, 2023, are as follows (assume that the day that you are preparing the delinquent charge is October 14, 2023): Note: To calculate the finance charges, you may need to find the number of days (NDays) between purchased date and the day that you are preparing the delinquent charge. 1. Set up the excel model using the data (20 points). The model must contain worksheets named "Title", "Assumptions", "Customers", "FC_Model", "Chart". Please list any assumptions you have made in the "Assumptions" worksheet. 2. To consider only the customers who purchased golf balls, you must use the input data that is available in two text files. (60 points). The Excel model worksheet named "FC_Model" should contain only the following data: ID, NoofBallsPurchased, Product, PurchasedDate, SalesAmountNotPaid, NDays, FinanceChargeAmount . Note: You must use data manipulation, vlookup, if or nested if, functions and formula to come up with the data on "FC_Model" tab. 3. Prepare an appropriate chart to compare the number of "paid" customers and "not-paid" customers. (20 points). The chart must be in the "Chart" tab