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.
=SUM(LaborCost)First define the named range 'LaborCost' = Jobs!H2:H81 * Jobs!I2:I81 * Jobs!J2:J81 (array). Then B2 sums that named range to produce total labor cost across all jobs.
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)
=COUNTIF(Jobs!M2:M81,"Overdue")COUNTIF scans Jobs!M2:M81 for cells equal to 'Overdue' and returns the count.
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.
=SUMIF(Jobs!$E$2:$E$81,"Q1",Jobs!$L$2:$L$81)SUMIF checks each row of column E for the matching quarter label and sums the corresponding InvoiceAmount in column L. Absolute references allow the ranges to be reused across B6:B9 by changing only the criteria string.
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.
=IFERROR(VLOOKUP(N2,SiteDirectory!$A$2:$E$81,3,FALSE),"Unknown")VLOOKUP uses the JobSiteID in column N as the lookup value, searches SiteDirectory!A2:E81 for a match, and returns the 3rd column (ContractTier). IFERROR catches any #N/A errors and replaces them with 'Unknown'.
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.
=IF((L2-K2-(H2*I2*J2))/L2>0.40,"High",IF((L2-K2-(H2*I2*J2))/L2>0.20,"Medium","Low"))The outer IF checks if the profit margin exceeds 40%; if true returns 'High'. The inner IF checks if margin exceeds 20%; if true returns 'Medium', otherwise 'Low'. Margin = (InvoiceAmount - MaterialCost - LaborCost) / InvoiceAmount.
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.
=XLOOKUP("SITE-042",SiteDirectory!A2:A81,SiteDirectory!B2:B81,"Site Not Found")XLOOKUP searches SiteDirectory!A2:A81 for 'SITE-042' and returns the matching value from SiteDirectory!B2:B81 (SiteName). The fourth argument provides the if-not-found text.
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.
Insert a PivotTable from Jobs!A1:N81 onto Dashboard starting at A1. Set Rows = Region, Columns = JobType, Values = Sum of InvoiceAmount. Insert a Clustered Bar Chart from the PivotTable data. Apply Top 10% Conditional Formatting rule with green fill to the value area 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.
=IFERROR(INDEX(Jobs!L2:L81,MATCH(MAX(Jobs!K2:K81),Jobs!K2:K81,0)),"N/A")MAX(Jobs!K2:K81) finds the largest MaterialCost. MATCH locates its position within K2:K81 using exact match (0). INDEX uses that position to return the corresponding InvoiceAmount from L2:L81. IFERROR catches any unexpected errors and returns 'N/A'.