Lookup, conditional logic, summarisation and charting over a season of landscaping jobs.
Download the GreenScape Pro starter workbook and complete all eight tasks exactly as described, entering formulas in the cells specified on each sheet. Use formulas for every answer — a typed-in number will not receive credit even if it is numerically correct. Do not insert, delete, or rename any sheets or move any existing data. When finished, save your file as Lastname_Firstname_GreenScape.xlsx and upload it to the course portal before the deadline.
GreenScape Pro's operations manager has asked a junior analyst to dig into the company's job records for the past fiscal year. The analyst needs to evaluate crew labor costs, invoice performance by quarter and region, and identify which job sites are the most and least profitable. The findings will be presented to senior leadership to guide staffing and contract renewal decisions.
1. On the 'Analysis' sheet, first create a Named Range called 'LaborCost' that refers to the calculated labor cost for every job. Labor cost per job = HoursWorked × HourlyRate × CrewSize (columns I, J, and H on the Jobs sheet). Then, in cell B2 of the 'Analysis' sheet, use a SUM formula referencing the named range 'LaborCost' to display the total labor cost across all 80 jobs. (8 pts)
Hint: Think about which three columns together define how much was spent on crew labor for a single job, then consider how a named range can capture that product across all rows.
2. In cell B3 of the 'Analysis' sheet, use a COUNTIF formula to count the number of jobs in the 'Jobs' sheet where the PaymentStatus (column M) is 'Overdue'. (7 pts)
3. In cells B6, B7, B8, and B9 of the 'Analysis' sheet, use SUMIF formulas to calculate the total InvoiceAmount (column L of the 'Jobs' sheet) for each quarter: Q1 in B6, Q2 in B7, Q3 in B8, and Q4 in B9. The Quarter field is in column E of the 'Jobs' sheet. Use absolute references for the criteria range and sum range. (12 pts)
Hint: You need the same two ranges for each formula — only the quarter label changes. Locking ranges with $ signs makes it easier to copy the formula down.
4. In cell O2 of the 'Jobs' sheet, enter a formula using IFERROR wrapped around VLOOKUP to retrieve the ContractTier (column C of 'SiteDirectory') for each job, matching on JobSiteID (column N of 'Jobs' against column A of 'SiteDirectory'). If no match is found, display the text 'Unknown'. Fill the formula down through O81 to cover all 80 data rows. (12 pts)
Hint: ContractTier is the third column in the SiteDirectory table. Wrap your lookup in an error-handling function so unmatched site codes do not break the sheet.
5. In cell P2 of the 'Jobs' sheet, write a nested IF formula to assign a Profitability Rating to each job based on the profit margin. First calculate the gross profit as InvoiceAmount (L) minus MaterialCost (K) minus labor cost (CrewSize H × HoursWorked I × HourlyRate J). Then divide gross profit by InvoiceAmount (L) to get the margin. If the margin is greater than 0.40 display 'High', if greater than 0.20 display 'Medium', otherwise display 'Low'. Fill the formula down through P81. (12 pts)
Hint: Calculate gross profit by subtracting both cost components from the invoice amount, then divide by the invoice amount to convert to a margin percentage before comparing thresholds.
6. In cell B12 of the 'Analysis' sheet, use an XLOOKUP formula to find the SiteName from the 'SiteDirectory' sheet for the job site with JobSiteID 'SITE-042'. Look up 'SITE-042' in column A of 'SiteDirectory' and return the corresponding value from column B (SiteName). If not found, return 'Site Not Found'. (12 pts)
Hint: XLOOKUP takes a lookup value, a lookup array, a return array, and an optional not-found value — in that order.
7. On the 'Dashboard' sheet, create a PivotTable using the data from the 'Jobs' sheet (A1:N81). Place the PivotTable with its top-left corner at cell A1 of the 'Dashboard' sheet. Configure the PivotTable so that Region (column B) is the Row field, JobType (column C) is the Column field, and the Values area shows the Sum of InvoiceAmount (column L). Then, based on the PivotTable results, insert a Clustered Bar Chart on the 'Dashboard' sheet that visualises total InvoiceAmount by Region. Finally, apply Conditional Formatting to the PivotTable's value cells so that the top 10% of values are highlighted with a green fill. (17 pts)
Hint: Use Insert > PivotTable and specify the source range and destination. For the chart, select the PivotTable's region totals column before inserting. Conditional Formatting > Top/Bottom Rules > Top 10% can be applied to the value cells of the PivotTable.
8. In cell B15 of the 'Analysis' sheet, write a formula using INDEX and MATCH (with IFERROR for error handling) to find the InvoiceAmount (column L of 'Jobs') for the job that has the single highest MaterialCost (column K of 'Jobs') across all 80 rows. The formula should first identify which row contains the maximum MaterialCost, then return that row's InvoiceAmount. If no value is found, display 'N/A'. Do not use a helper column — the entire logic must be contained in cell B15. (20 pts)
Hint: Think about using MAX inside MATCH to locate the row of interest, then use INDEX to retrieve a value from a different column of that same row. Wrap the whole thing in an error handler.