Open the ClassicGardens-06 start file. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it.Create a nested INDEX and MATCH function to display the number of responses from a city. Click the Mailings sheet tab and select and name cells A3:D28 as Responses. Click the Mailing Stats sheet tab. Click cell B21 and type Carthage. Click cell C21, start an INDEX function, and select the first argument list option. Choose or type the Responses range name for the Array argument. Click the Row_num box and nest a MATCH function. Select cell B21 for the Lookup_value and cells A3:A28 on the Mailings sheet for the Lookup_array. Click the Match_type argument box and type 0. Click INDEX in the Formula bar and click the Insert Function (Fx) button. Click the Column_num box and nest a second MATCH function to look up cell D3 on the Mailings sheet in the lookup array A3:D3. Click the Match_type box and type 0 (Figure 6-106) and click Done. The result displays as 343.00.Figure 6-106 Nested MATCH and INDEX functions Format the results to show zero decimal places. Type Smyrna in cell B21.Use DSUM to summarize mailing data. Select the Mailings sheet and note that number mailed is located in the third column and response data is in the fourth column. Click the Criteria sheet tab. Select cell B2 and type lan* to select data for the Landscape Design department. Type law* in cell B5 for the Lawn & Maintenance department. Type pat* in cell B8 for the Patio & Furniture department. Click the Mailing Stats sheet tab and select cell B7. Use DSUM with the range name Responses as the Database argument. Type 3 for the Field argument (# Mailed column), and enter an absolute reference to cells B1:B2 on the Criteria sheet as the Criteria argument. Copy the formula to cell C7 and edit the Field argument to use the fourth column (# Responses). Use DSUM in cells B8:C9 to calculate results for the two remaining departments. Use SUM in cells B10:C10. Format all values as Comma Style with no decimal places.Create an IFS function to display a response rating. Note: If your version of Excel does not include the IFS function, build the following nested IF function =IF(C7/B7>=20%,$C$15,IF(C7/B7>=15%,$C$16,IF(C7/B7>=11%,$C$17,$C$18))) to display the rating. Click cell D7. The response rate and ratings are shown in rows 14:18. Start an IFS function and select C7 for the Logical_test1 argument. Type / for division and select cell B7. Type >=20% to complete the test. Click the + button to reveal the Value_if_true1 box, select C15, and press F4 (FN+F4) (Figure 6-107).Figure 6-107 First Logical_test and Value_if_true arguments Click the + button to reveal the Logical_test2 box, select C7, type /, select cell B7, and type >=15%. Click the + to reveal the Value_if_true2 box, click cell C16, and press F4 (FN+F4). Complete the third and fourth logical tests and Value_if_true arguments (Figure 6-108).Figure 6-108 Completed IFS function arguments Copy the formula in cell D7 to cells D8:D10.Use SUMIFS to total insurance claims and dependents by city and department. Click the Employee Insurance sheet tab and select cell E25. Use SUMIFS with an absolute reference to cells F4:F23 as the Sum_range argument. The Criteria_range1 argument is an absolute reference to cells E4:E23. The Criteria1 argument is bre* to select the city of Brentwood. The Criteria_range2 argument is an absolute reference to cells D4:D23, the department column, with criteria of lan* to select the Landscape Design department. Click Done. The result for cell E25 is 10. Build SUMIFS formulas for cells E26:E28 based on the criteria displayed in cells C26:C28. Format borders to remove inconsistencies, if any, and adjust column widths to display data.Use TEXTJOIN to display names. Note: If your version of Excel does not include the TEXTJOIN function, use CONCAT or CONCATENATE to build the function. Click the Full Names sheet tab and select cell E4. Start a TEXTJOIN function and press Spacebar for the Delimiter argument. Click the Text1 box and select cell C4. Complete the Text2 and Text3 arguments to show middle and last names and click Done (Figure 6-109).Figure 6-109 Delimiter is a space Copy the formula to display full names in column E. Save and close the ClassicGardens-06 Excel workbook (Figure 6-110).Figure 6-110 Completed worksheets for Excel 6-5 Upload and save the ClassicGardens-06 Excel workbook. Submit project for grading.Reset Progress Back to top

Read KIPP Houston Public Schools, Case 9, pages C-97 through C-111.For this case analysis, you should prepare a written analysis of 3 to 5 pages that uses the text and other credible sources as appropriate. Your paper should have 1" margins, be double spaced, and use APA formats fortopic and subtopic headingscitationsreferencesYour case must minimally address the following issues / questions:Summarize the organizational strengths and core competencies which distinguish KIPP Houston from its competitors. Does the new regional structure advance or interfere with the goals of the organization?What are the weaknesses of KIPP Houston’s internal operations? Compare the organization’s performance to the achievements of its competitors. What steps have been taken to manage its problem areas?Describe the factors in the external environment which affect the charter organization’s ability to meet its objectives. Discuss KIPP Houston’s current expansion goals.Describe and consider Ali’s strategic posture on the major issues facing KIPP Houston. Based on your analysis, do you agree with her judgment? What additional recommendations can you make to guide the organization’s strategy and actions?This is an individual assignment. It is due no later than 11:59 PM CT Sunday.RubricKipp Houston Public SchoolsKipp Houston Public SchoolsCriteriaRatingsPtsThis criterion is linked to a Learning OutcomeAnalysis of internal strengths and core competencies6.0 ptsSuperior: Exceeds expectations and thoughtfully assesses the potential impact of the expanding regional structure.5.0 ptsProficient: Analysis thoroughly describes the relevant strengths and core competencies which distinguish KIPP Houston from its competitors.4.0 ptsCompetent: Analysis includes significant strengths and core competencies which distinguish KIPP Houston from its competitors.2.0 ptsNeeds Improvement: Analysis excludes some of the relevant strengths and core competencies which distinguish KIPP Houston from its competitors.6.0 ptsThis criterion is linked to a Learning OutcomeAnalysis of internal weaknesses6.0 ptsExceeds expectations, fully exploring and recognizing competitor achievements which impact KIPP's success in the marketplace.5.0 ptsAnalysis thoroughly describes KIPP Houston's internal weaknesses and demonstrates a full understanding of the steps being taken to overcome them.4.0 ptsAnalysis includes significant weaknesses of KIPP Houston's internal operations and the steps being taken to manage these weaknesses.2.0 ptsAnalysis excludes some of the weaknesses of KIPP Houston's internal operations and/or the steps being taken to manage these weaknesses.6.0 ptsThis criterion is linked to a Learning OutcomeAnalysis of external environmental conditions6.0 ptsExceeds expectations and provides an outstanding critique of the organization's current expansion goals.5.0 ptsAnalysis fully and accurately describes the external factors which affect the organization's ability to meet its objectives.4.0 ptsAnalysis describes external factors which affect the organization's ability to meet its objectives.2.0 ptsAnalysis fails to describe some of the external factors which affect the organization's ability to meet its objectives6.0 ptsThis criterion is linked to a Learning OutcomeStrategic intent and recommendations7.0 ptsExceeds expectations and offers extraordinary suggestions to guide the organization's strategy and actions.6.0 ptsResponse fully considers Ali's strategic posture on major issues facing KIPP Houston, citing her experience, training, and body of results to credit her judgment.5.0 ptsResponse considers Ali's strategic posture on major issues facing KIPP Houston.3.0 ptsResponse does not fully consider Ali's strategic posture on major issues facing KIPP Houston.7.0 ptsThis criterion is linked to a Learning OutcomeGrammar, Writing, APA Format5.0 ptsInformation is appropriately sequenced with topical headings and subheadings. Language use, grammar, spelling, and punctuation are at an appropriate graduate level, with no errors. Uses paragraph form with topical sentences that are supported by the remainder of the paragraph, and effective transitions. Writing is clear, concise, and in correct voice and tense. Correct application of APA format. NO errors in citations and references.4.0 ptsInformation is appropriately sequenced with topical headings. Language use, grammar, spelling, and punctuation are at an appropriate graduate level. Uses paragraph form with topical sentences that are supported by the remainder of the paragraph. Writing is clear and concise. Somewhat correct application of APA format. Two to three errors in citations and references.3.0 ptsThere is some order to the information with some heading usage. Presentation has two to four misspellings or grammar errors, or two or three word use errors. Uses paragraph form but sentences within the paragraph may not be related. Somewhat correct application of APA format. Four to six errors in citations and references.1.0 ptsLimited structure or no topical headings used. Presentation has more than four misspellings or grammar errors, or more than four word use errors. Language use makes it difficult to understand the intended meaning. Incorrect application of APA format. Several errors in or missing citations and references.5.0 ptsTotal Points: 30.0