1. Dean Yamaguchi is a development officer for the city of Honu Point in Hawaii. Dean is analyzing city development proj

[answerhappygod]

1. Dean Yamaguchi is a development officer for the city of HonuPoint in Hawaii. Dean is analyzing city development projects thathave been completed, are in progress, and have been proposed. Heasks for your help in using Excel tables to complete the analysis.Go to the Completed Projects worksheet, which lists the developmentprojects that were completed in Honu Point in 2021. Create a tableas follows so that Dean can summarize and filter the data anddisplay projects with the highest funding amounts: a. Format thecompleted projects data (range A1:F12) as a table using Brown,Table Style Medium 7. b. Use CompletedProjects as the name of thetable. c. Filter the table using a custom AutoFilter to displayprojects with a Funding amount greater than $10,000. 2. Go to theCurrent Projects worksheet, which contains the CurrentProjectstable listing city development projects that are under review or indevelopment. The city recently received a proposal for a newdevelopment project. Insert a new row after the Lahua StreetApartment record in the CurrentProjects table for a new recordcontaining the data shown in Table 1. Table 1: New Record for theCurrentProjects Table Project Name Orchid Drive Zoning Start Date11/20/2021 Number of Days 30 Project Type Public Funding Type LoanFunding $2,000 Approved? No In Development? No 3. Sort theCurrentProjects table in ascending order by funding amount so thatDean can quickly identify the projects by funding amount. 4. Deanwants to list the projects that are in development in a separatepart of the worksheet. Use an advanced filter as follows to listthese projects in a new range: a. In cell H17, type Yes as thevalue to filter on in the criteria range. b. Create an advancedfilter using the CurrentProjects table (range A1:H12) as the Listrange. c. Use the range A16:H17 as the Criteria range. d. Copy theresults to another location, starting in the range A19:H19. 5. As acontrast, Dean also wants to list the projects that are not indevelopment. In the CurrentProjects table, use the filter arrows tolimit the table display to projects that are not in development. 6.Go to the Proposed Projects worksheet, which lists projects thatwere proposed in 2021. Dean suspects the ProposedProjects table hasa duplicate record. Identify the duplicate as follows: a. Clear thefilter from the ProposedProjects table to display all the records.b. In the range A2:A14, create a conditional formatting HighlightCells Rule that displays cells with duplicate values using LightRed Fill and Dark Red Text. c. Delete the second instance of theduplicate record so that you can summarize the data accurately. 7.The city of Honu Point wants to fast-track mixed-use developmentprojects that use loans for funding. Add a column to theProposedProjects table, and determine which projects meet thecriteria as follows: a. In cell G1, type Fast Track as the columnheading. b. In cell G2, enter a formula using the AND function thatincludes structured references to display TRUE if a project has a[@[Project Type]] of "Mixed Use" and a [@[Funding Type]] of "Loan".Fill the range G3:G13 with the formula in cell G2 if Excel does notdo so automatically. 8. Add a Total Row to the ProposedProjectstable, which automatically counts the number of Fast Track values.Using the total row, display the sum of the funding amounts. 9.Dean asks you to identify the projects that require 120 days ormore to complete, those that require 60 days or more to complete,and those that require less than 60 days to complete. a. In theNumber of Days column (range C2:C13), create a new Icon Setconditional formatting rule using the 3 Signs icons. b. Reverse theicon order. c. Display the red diamond icon in cells with a Numbertype value greater than or equal to 120. d. Display the yellowtriangle icon in cells with a Number type value greater than orequal to 60. e. Display the green circle icon in cells with aNumber type value less than 60. 10. Dean also wants to compare thefunding amounts visually. In the Funding column (range F2:F13),create a new Data Bars conditional formatting rule using OrangeGradient Fill data bars. 11. Wrap the text in cell J1 to displaythe complete contents of the cell. 12. Dean wants to summarize thenumber of projects proposed by the project type and calculate theirfunding amounts and average funding amounts. Calculate thisinformation for Dean as follows: a. In cell J2, enter a formulausing the COUNTIF function that counts the number of proposedCommercial projects, using ProposedProjects[Project Type] as therange and cell I2 as the criteria. b. Fill the range J3:J5 with theformula in cell J2. c. In cell K2, enter a formula using the SUMIFfunction that totals the funding for proposed Commercial projects,using ProposedProjects[Project Type] as the range, cell I2 as thecriteria, and ProposedProjects[Funding] as the sum_range. d. Fillthe range K3:K5 with the formula in cell K2. e. In cell L2, enter aformula using the AVERAGEIF function that averages the funding forproposed Commercial projects, using ProposedProjects[Project Type]as the range, cell I2 as the criteria, andProposedProjects[Funding] as the average_range. f. Fill the rangeL3:L5 with the formula in cell L2. 13. In the range I8:L12, Deanneeds to insert a summary of the city development projects from theprevious year. Insert this data as a table as follows: a. Insert atable in the range I8:L12, specifying that the data has headers. b.In the new table, enter the data shown in Table 2. c. AutoFit thecontents of columns I:L to display the complete cell contents. d.Apply Brown, Table Style Medium 7 to the new table to match theformatting of the ProposedProjects table. Table 2: Data for the NewTable Project Type Started Completed Funding Commercial 5 3 45,500Mixed Use 4 2 57,800 Public 4 3 33,750 Residential 3 3 41,325 14.Go to the Funding Totals worksheet, which lists all the current andproposed development projects. Dean wants to display the data byfunding type and then list the projects by start date. Sort thedata in the table in ascending order first by funding type and thenby start date. 15. Dean also wants to calculate subtotals for eachfunding type (Hint: You must complete all actions of this stepcorrectly to receive full credit.): a. Convert the table to arange. b. Insert a subtotal at each change in the Funding Typevalue. c. Use the Sum function to calculate the subtotals. d. Addsubtotals to the Funding values only. e. Include a summary belowthe data. f. Collapse the outline to display only the subtotals foreach funding type and the grand total. 16. Go to the Lookupworksheet, which lists project details, including the ID code thatstaff in the Development Division use to refer to the projects.Dean wants to find a simple way to look up a project name based onits ID. Create a formula that provides this information as follows:a. In cell H3, begin to enter a formula using the VLOOKUP function.b. Use the Project ID (cell H2) as the lookup value. c. Use theLookup table (range A2:E23) as the table_array. d. Use the ProjectName column (column 2) as the col_index_num. e. Specify an exactmatch (FALSE) for the range_lookup. 17. Dean also wants to look upthe start date of each project. Instead of using the VLOOKUPfunction, he suggests using the INDEX and MATCH functions, whichare faster with large amounts of data. Create a formula thatprovides the start date of a project as follows: a. In cell H4,begin to enter a formula using the INDEX function. b. Use theLookup table (range A2:E23) as the array. c. For the row_numargument, use the MATCH function. d. Use the Project ID (cell H2)as the lookup_value for the MATCH function. e. Use the ID column(range A2:A23) as the lookup_array for the MATCH function. f.Specify an exact match (0) for the MATCH function. g. Use the StartDate column (column 4) as the column_num for the INDEX function.18. Dean also wants to identify the number of projects that haveless than $5,000 of funding and calculate the average fundingamount of commercial projects. Create formulas that provide thisinformation as follows: a. In cell H8, create a formula using theDCOUNT function to count the number of projects with fundingamounts less than $5,000, using the Lookup table (range A1:E23) asthe database, "Funding" as the field, and the range G6:G7 as thecriteria. b. In cell H13, create a formula using the DAVERAGEfunction to average the funding amounts for Commercial projects,using the Lookup table (range A1:E23) as the database, "Funding" asthe field, and the range G11:G12 as the criteria. Your workbookshould look like the Final Figures on the following pages. Saveyour changes, close the workbook, and then exit Excel. Follow thedirections on the SAM website to submit your completedproject.