Simmy assessment goes here. Project 1 of the MO-211 practice exam: workbook options and settings. When a student misses a task, Simmy builds the AI prompt (the task as worded, what the student did, and the correct result).

Starting data. Two workbooks.

Branch Report.xlsm (opens with the Security Warning bar), sheets Deposits and Settings. Deposits (A1:E14): Month (Jan–Dec), Checking, Savings, Certificates, Total (=SUM(B2:D2)); row 14 totals. Cells B2:D13 are input cells. Settings: B2 holds the branch name.

Macro Library.xlsm: contains Module1 with a macro named Stamp_Date that types today's date in cell A1 of the active sheet.

Regional Rates.xlsx (open): sheet Rates, cell C5 holds the current savings rate (0.0425).

Tasks.

1. Enable the macros in the Branch Report workbook. Expected result: content enabled. Objective: Enable macros in a workbook.
2. Copy the Stamp_Date macro from the Macro Library workbook to the Branch Report workbook. Expected result: Branch Report's VBA project contains a module with Stamp_Date. Objective: Copy macros between workbooks.
3. In cell B4 of the Settings worksheet, enter a formula that returns the savings rate from cell C5 of the Rates worksheet in the Regional Rates workbook. Expected result: Settings!B4 ='[Regional Rates.xlsx]Rates'!$C$5. Objective: Reference data in other workbooks.
4. Protect the Deposits worksheet so that users can edit only the monthly amounts in B2:D13 and can sort data. Do not use a password. Expected result: B2:D13 unlocked; sheet protected with Sort allowed; no password. Objective: Protect worksheets and cell ranges.
5. Prevent users from inserting, deleting or renaming worksheets. Use the password Lakeview1. Expected result: workbook structure protected with password Lakeview1. Objective: Protect workbook structure.
6. Configure the workbook so that formulas are recalculated only when the user requests it. Expected result: Calculation Options = Manual. Objective: Configure formula calculation options.
7. Configure the workbook so that users are prompted to open it as read-only. Expected result: Always Open Read-Only on (or Read-only recommended). Objective: Restrict editing.

Simmy assessment goes here. Project 2 of the MO-211 practice exam: filling, formatting and validating data. When a student misses a task, Simmy builds the AI prompt (the task as worded, what the student did, and the correct result).

Starting data. Workbook Harvest Co-op with sheets Members, Deliveries and Schedule.

Members (A1:D41): Member (formatted "LAST, First", for example "GARCIA, Elena"), Phone (10 digits, no punctuation, for example 5558203147), Share Type (Basic, Family, Farm), Joined. 40 rows; 5 rows exactly repeat earlier rows.

Deliveries (A1:E31), a plain range: Farm, Crop, Date, Pounds, Price/lb; 30 rows across 5 farms, not sorted.

Schedule: A1 "Pickup day"; A2 holds Monday 4/5/2027; A3:A40 empty. C1 "Sample".

Tasks.

1. On the Members worksheet, insert a column to the right of Member named First Name. Fill it with each member's first name by following an example, without using a formula. Expected result: B "First Name" with Elena, … (Flash Fill). Objective: Fill cells by using Flash Fill.
2. Format the phone numbers so that 5558203147 is displayed as (555) 820-3147, without changing the values. Expected result: custom number format (000) 000-0000 on the Phone column. Objective: Create custom number formats.
3. Remove the duplicate member records from the Members worksheet. A record is a duplicate only if every column matches. Expected result: 5 duplicate rows removed; 35 records remain. Objective: Remove duplicate records.
4. Allow only Basic, Family or Farm to be entered in the Share Type column, chosen from a drop-down list. Display the input message Choose a share type when a cell is selected. Expected result: List validation with source Basic,Family,Farm; in-cell drop-down; input message "Choose a share type". Objective: Configure data validation.
5. On the Schedule worksheet, fill A3:A40 with dates that continue from A2 and include only weekdays. Expected result: Fill Series, Date, Weekday, step 1; A3 = 4/6/2027; A40 = 5/27/2027. Objective: Fill cells by using advanced Fill Series options.
6. In cell C2 of the Schedule worksheet, enter a formula that returns 5 rows and 2 columns of random whole numbers from 100 to 500. Expected result: C2 =RANDARRAY(5,2,100,500,TRUE). Objective: Generate numeric data by using RANDARRAY().
7. On the Deliveries worksheet, show the total pounds delivered by each farm, with a subtotal row after each farm's deliveries and a grand total. Expected result: data sorted by Farm; Subtotal at each change in Farm, Sum of Pounds; outline levels 1–3. Objective: Calculate data by inserting subtotals and totals; Group and ungroup data.

Simmy assessment goes here. Project 3 of the MO-211 practice exam: conditional formatting and logical formulas. When a student misses a task, Simmy builds the AI prompt (the task as worded, what the student did, and the correct result).

Starting data. Workbook Sterling Logistics with one sheet, Shipments, holding a table named Shipments (A1:H31): ShipID, Carrier (Apex, Bolt, Crane), Origin (DEN, PHX, SLC), Service (G = Ground, E = Express, O = Overnight), Weight (lb), Days, Cost, Damaged (Yes/No). 30 rows. Two existing conditional formatting rules on Shipments[Days]: a red fill for values greater than 5 and a yellow fill for values greater than 3, with the yellow rule above the red rule.

Cells K2:K7 hold labels for a summary: Apex shipments, Avg Bolt cost (Express), Heaviest SLC shipment, Lightest undamaged shipment, Crane cost with fuel.

Tasks.

1. Change the order of the conditional formatting rules on the Days column so that shipments over 5 days appear red. Expected result: red rule (> 5) moved above the yellow rule (> 3). Objective: Manage conditional formatting rules.
2. Apply a rule that fills the entire row of every damaged shipment in light orange. Apply it to the data rows only. Expected result: formula rule =$H2="Yes" applied to A2:H31 with a light orange fill. Objective: Create conditional formatting rules that use formulas.
3. Add a column named Service Name that returns "Ground", "Express" or "Overnight" for G, E and O, and "Unknown" for anything else, using a function that compares the service code with a list of values. Expected result: I "Service Name" =SWITCH([@Service],"G","Ground","E","Express","O","Overnight","Unknown"). Objective: Perform logical operations by using SWITCH().
4. Add a column named Priority that returns "High" if the shipment is Overnight or weighs more than 500 lb, "Medium" if it's Express, and "Low" otherwise. Use a single function without nesting IF. Expected result: J "Priority" =IFS(OR([@Service]="O",[@Weight]>500),"High",[@Service]="E","Medium",TRUE,"Low"). Objective: Perform logical operations by using IFS(), OR().
5. In L2, enter a formula that counts Apex shipments that were not damaged. In L3, enter a formula that returns the average cost of Bolt's Express shipments. Expected result: L2 =COUNTIFS(Shipments[Carrier],"Apex",Shipments[Damaged],"No"); L3 =AVERAGEIFS(Shipments[Cost],Shipments[Carrier],"Bolt",Shipments[Service],"E"). Objective: COUNTIFS(); AVERAGEIFS().
6. In L4, return the heaviest shipment from SLC. In L5, return the lightest shipment that was not damaged. Expected result: L4 =MAXIFS(Shipments[Weight],Shipments[Origin],"SLC"); L5 =MINIFS(Shipments[Weight],Shipments[Damaged],"No"). Objective: MAXIFS(); MINIFS().
7. In L6, enter a formula that adds a 6 percent fuel charge to Crane's total cost, then subtracts 500 if the result is more than 10,000. Assign the name withFuel to the cost with the fuel charge. Expected result: L6 =LET(withFuel,SUMIF(Shipments[Carrier],"Crane",Shipments[Cost])*1.06,IF(withFuel>10000,withFuel-500,withFuel)). Objective: Perform logical operations by using LET(), SUMIF().

Simmy assessment goes here. Project 4 of the MO-211 practice exam: lookups, dates, financial functions, FILTER and SORTBY. When a student misses a task, Simmy builds the AI prompt (the task as worded, what the student did, and the correct result).

Starting data. Workbook Aspen Valley Realty with sheets Listings, Agents, Commission, Loan and Holidays.

Listings: a table named Listings (A1:H26): MLS, Address, AgentID, Town (Aspen, Basalt, Carbondale, Snowmass), Beds, Price, Listed (date), Status (Active, Pending, Sold). 25 rows.

Agents (A1:C9): AgentID, Agent Name, Phone; 8 agents.

Commission (A1:E3): row 1 tier labels in B1:E1 (Tier 1, Tier 2, Tier 3, Tier 4); row 2 "Min price" 0, 500,000, 1,000,000, 2,500,000; row 3 "Rate" 0.030, 0.027, 0.025, 0.022.

Loan: B1 Price (865,000), B2 Down payment % (0.20), B3 Annual rate (0.0675), B4 Years (30), B6 Monthly budget (4,200).

Holidays: a table named Holidays with a Date column listing 2027 federal holidays.

Tasks.

1. Add a column to the Listings table named Agent that returns each listing's agent name from the Agents worksheet by using the AgentID. Expected result: I "Agent" =XLOOKUP([@AgentID],Agents!$A$2:$A$9,Agents!$B$2:$B$9) (VLOOKUP or INDEX/MATCH also correct). Objective: Look up data by using XLOOKUP(), VLOOKUP(), INDEX(), MATCH().
2. Add a column named Rate that returns the commission rate for the listing's price. Use a function that searches across the first row of a range, and find the closest match that is less than or equal to the price. Expected result: J "Rate" =HLOOKUP([@Price],Commission!$B$2:$E$3,2,TRUE). Objective: Look up data by using HLOOKUP().
3. Add a column named Close By that returns the date 30 working days after the listing date, excluding the dates in the Holidays table. Expected result: K "Close By" =WORKDAY([@Listed],30,Holidays[Date]), formatted as a date. Objective: Calculate dates by using WORKDAY().
4. Add a column named Weekend Listing that returns "Yes" if the listing date fell on a Saturday or Sunday and "No" otherwise. Expected result: L "Weekend Listing" such as =IF(WEEKDAY([@Listed],2)>5,"Yes","No"). Objective: Calculate dates by using WEEKDAY().
5. In cell N1 of the Listings worksheet, enter a formula that returns the addresses of all Active listings in Basalt, sorted from highest price to lowest. Expected result: =SORTBY(FILTER(Listings[Address],(Listings[Status]="Active")*(Listings[Town]="Basalt")),FILTER(Listings[Price],(Listings[Status]="Active")*(Listings[Town]="Basalt")),-1). Objective: Filter data by using FILTER(); Sort data by using SORTBY().
6. On the Loan worksheet, in cell B8, calculate the monthly payment for a loan of the price minus the down payment, at the annual rate in B3 over the years in B4. Display the result as a positive number. Expected result: B8 =PMT(B3/12,B4*12,-B1*(1-B2)) ≈ 4,488. Objective: Calculate financial data by using the PMT() function.
7. In cell B10 of the Loan worksheet, enter a formula that returns "Affordable" if the monthly budget in B6 pays off the loan in 30 years or less and the down payment is at least 20 percent, and "Not affordable" otherwise. Expected result: B10 =IF(AND(NPER(B3/12,-B6,B1*(1-B2))<=360,B2>=0.2),"Affordable","Not affordable") → "Not affordable" (the budget is below the 30-year payment). Objective: Forecast data by using the AND(), IF(), and NPER() functions.
8. In cell D1 of the Loan worksheet, enter a formula that returns the current date and time. Expected result: D1 =NOW(). Objective: Reference date and time by using the NOW() and TODAY() functions.

Simmy assessment goes here. Project 5 of the MO-211 practice exam: data analysis, troubleshooting and macros. When a student misses a task, Simmy builds the AI prompt (the task as worded, what the student did, and the correct result).

Starting data. Workbook Coastal Research Lab.xlsm (macros already enabled) with sheets Site A, Site B, Site C, Totals, Grant and Log.

Site A, Site B, Site C (A1:D7 each): Species, Count, Avg Length, Samples; six species each, in different orders, with some species missing from some sites.

Totals: empty.

Grant (A1:B12). Inputs: B2 Researchers (4), B3 Salary each (62,000), B4 Equipment (48,000), B5 Travel (15,500), B6 Overhead rate (0.26). Results: B8 Direct costs =B2*B3+B4+B5; B9 Overhead =B8*B6; B10 Total request =B8+B9; B11 Cost per researcher =B10/B12 (B12 is empty, so B11 shows #DIV/0!).

Log: an activity log, A1:C40. Module1 contains:

Sub Add_Header()
    Range("A1").Value = "Activity Log"
    Range("A1").Font.Size = 12
End Sub

Tasks.

1. On the Totals worksheet, starting at cell A1, create a summary that adds the counts and samples for each species across the three site worksheets. Use the labels from the top row and left column. Expected result: Consolidate with Sum; three references; Top row and Left column checked. Objective: Summarize data from multiple ranges by using the Consolidate feature.
2. On the Grant worksheet, find the equipment amount that brings the total request to exactly 400,000. Keep the result. Expected result: Goal Seek: Set cell B10, To value 400000, By changing B4; B4 ≈ 53,960 (direct costs must be 400,000 ÷ 1.26 ≈ 317,460). Objective: Perform what-if analysis by using Goal Seek.
3. Create a scenario named Expanded Team that sets the researchers to 6 and travel to 24,000. Then create a scenario summary report that shows the total request. Expected result: scenario Expanded Team, changing cells B2 and B5 (values 6, 24,000); Scenario Summary sheet with result cell B10. Objective: Perform what-if analysis by using Scenario Manager.
4. Display arrows that show which cells use the value in cell B6. Expected result: dependent arrows traced from B6 (to B9, then B10, B11 if traced further). Objective: Trace precedence and dependence.
5. Add cell B10 to a window that shows its value while you work on other worksheets. Expected result: Watch Window contains Grant!B10. Objective: Monitor cells and formulas by using the Watch Window.
6. Correct the formula in cell B11 so that it divides the total request by the number of researchers. Expected result: B11 =B10/B2. Objective: Evaluate formulas; Validate formulas by using error checking rules.
7. Edit the Add_Header macro so that it makes the text bold and uses a font size of 16. Expected result: code includes Range("A1").Font.Bold = True and Range("A1").Font.Size = 16. Objective: Edit simple macros.
8. Record a macro named Clear_Log that is stored in this workbook and runs when the user presses Ctrl+Shift+L. The macro should clear the contents of A2:C40 on the Log worksheet. Expected result: macro Clear_Log in this workbook, shortcut Ctrl+Shift+L, clearing Log!A2:C40. Objective: Record simple macros; Name simple macros.

Simmy assessment goes here. Project 6 of the MO-211 practice exam: advanced charts, PivotTables and PivotCharts. When a student misses a task, Simmy builds the AI prompt (the task as worded, what the student did, and the correct result).

Starting data. Workbook Summit Outdoor with sheets Trend, Ages, Budget and Sales.

Trend (A1:C13): Month, Visitors (12,000–41,000), Conversion Rate (0.018–0.034). A clustered column chart plots both series on one axis.

Ages (A1:A121): Customer Age, 120 values from 16 to 78.

Budget (A1:B8): Start budget 250,000; Marketing -42,000; Product -96,500; Grants 35,000; Staff -88,000; Events -21,500; End budget 37,000.

Sales: a table named Sales (A1:G301), 300 orders: Date (2026–2027), Region, Store, Category, Units, Revenue, Cost. Simmy should generate the rows with a fixed seed.

Tasks.

1. On the Trend worksheet, change the chart so that Conversion Rate is shown as a line on a secondary vertical axis and Visitors remains as columns. Expected result: combo chart; Conversion Rate line on the secondary axis. Objective: Create and modify dual-axis charts; Combo charts.
2. On the Ages worksheet, create a chart that shows how many customers fall in each 10-year age range. Group all ages above 70 in a single bin. Expected result: histogram of A2:A121, bin width 10, overflow bin 70. Objective: Histogram charts.
3. On the Budget worksheet, create a waterfall chart of the data. Display the start and end budgets as totals. Expected result: waterfall of A1:B8; Start budget and End budget Set as Total. Objective: Waterfall charts.
4. Create a PivotTable on a new worksheet that shows total revenue for each category by region, with categories down the side and regions across the top. Show grand totals for rows only. Expected result: Rows Category; Columns Region; Values Sum of Revenue; Grand Totals On for Rows Only. Objective: Create PivotTables; Modify field selections and options.
5. Add a field named Margin that subtracts cost from revenue, and display it as Currency with no decimal places. Expected result: calculated field Margin =Revenue-Cost in Values; number format Currency, 0 decimals. Objective: Add calculated fields; Configure value field settings.
6. Add the Date field to the PivotTable's rows under Category, grouped by years. Expected result: Date in Rows below Category, grouped by Years only. Objective: Group PivotTable data.
7. Insert a slicer that filters the PivotTable by store. Expected result: Store slicer connected to the PivotTable. Objective: Create slicers.
8. Create a stacked column PivotChart from the PivotTable, apply the Style 8 chart style, and collapse the chart to show only categories. Expected result: stacked column PivotChart; style 8; Date field collapsed (categories only). Objective: Create PivotCharts; Apply styles to PivotCharts; Drill down into PivotChart details.