- - O file://C:/Users/ssterling/Downloads/MGMT2004GroupProjectSummerSchool2021_22620(2).pdf Requirements 1. Create an input section in a new worksheet and name the worksheet Input. According to the information given in the case, include all information you deem appropriate for this worksheet. 2. Create the Pawn Register Report [PawnRegisterReport] using appropriate functions and/or formulae. 3. Develop a customer loan schedule report [CustLoanSchedule] in a new sheet based on the sample given in Figure. 1.4. 4. In a new worksheet [Q4TotalUnapprovedLoans]Use your model to: a. Find the total value of unapproved loans. b. Highlight unapproved loans (Conditional formatting) 5. In a new worksheet [Q5PopPawnChart] highlight the most popular item being pawned by customers. Use a pie chart to display the percentage. 6. Create a new worksheet [Q6MostApprovedClients] displaying the band that has the most approved clients. Create a new worksheet [Q7TotalsFilter] allowing the Goodes to filter for total loans by category, model, condition and value. In a new worksheet [Q8LoanSchedule] find the end payment period if a lump sum of $70,000.00 is paid for transaction ID 10031. hp October DecemberSection A [45 MARKS] Since the Goode's are newcomers to the industry they have approached you to build a spreadsheet model using the following information (Item Valuation data can be found on the "ItemCategory" worksheet of Goodes_Pawn_DataFile.xlsx workbook): Figure 1.1 Item Valuation: Item Code Category Item Description Demand Item Value Level AUDIMTR Auto Parts Audi Engine 2015 Audi A8 4.0L Engine | Low J$1,647,120.78 BMWX6MTR Auto Parts BMW X6 Engine LOW I$1,037,000.00 HONDARMTR Auto Parts Honda Ridgeline 3.5L 6 Cyl Engine LOW JS 341,600.00 DELLINS Computer Inspiron 15 5000 Series High J$70789.00 MACAIR13 Computer MacBook Air 15 inch Medium $122130.00 PS4 Gaming Console | Sony PlayStation 4 LOW J$39724.00 XBOX1 Gaming Consple Microsoft Xbox One Low $33,456.00 SAM32TV Appliance Samsung 32 inch Smart TV Medium $ 60,000.00 SAM40TV Appliance Samsung 40 inch Smart TV Medium 1$70,000.00 SAM60TV Appliance Samsung 60 inch Smart TV Low $150,900.00 925 Jewellery Sterling Silver High J$743.00 8KG Jewellery 8 Karat Gold High $1,300.00 10 KG Jewellery 10 Karat Gold High IS1,617.00 12KG Jewellery 12 Karat Gold Low $1,890.00 14KG Jewellery 14 Karat Gold Low $2,241.00 15KG Jewellery 16 Karat Gold Low J$2,383.00 18KG Jewellery 18 Karat Gold Low IS2,908.00 21KG Jewellery 21.6 Karat LOW J$3,474.00 22KG Jewellery 22 Karat Medium $3,522.00 23KG Jewellery 23 Karat Medium J$3,700.00 24KG Jewellery 24 Karat Medium I$3,886.00 IPHONESS Cell Phone Apple Iphone 5S Medium $75,000.00 IPHONE6S Cell Phone Apple Iphone 6S Medium 1$90,000.00 SAMS5 cell Phone Samsung Galaxy $5 High J$35,000.00 SAMS6 Cell Phone Samsung Galaxy S6 High J$44,000.00 3 M 24 21 26 27 20 October November DecemberC file:///C:/Users/ssterling/Downloads/MGMT2004GroupProjectSummerSchool2021_22%20(3).pdf J$ 100,000 -J$ 499,999 30% J$ 500,000 25% 3 J$ 100,000 - JS 499,999 30% 2 - JS 100,000 35% The Goode's require a Pawn Log Report (shown in figure 1.4) and a Customer Loan Schedule (shown in figure 1.6). For the Pawn Log Report the Goode's will provide data (see "PawnRegisterReport" worksheet located in the Goodes_Pawn_DataFile_SS-xIsx workbook ) for Transaction ID. Customer ID, Item Code, Start Date, Item Quantity, Item Condition and Loan Amount, however, you are to create calculations to determine category, Item Description, Item Value, Secured Amount, Monthly Payments hp mbe