Formula Magic Slides Unit 3: The Market Analyst
The =SUM of All Parts
Lesson 26: Spreadsheet Formulas & Efficiency
DATA LAB SERIES
The Human Calculator Challenge
Human Calculator
Uses handheld calculator
Adds 10 random prices
High risk of "fat-finger" typos
The Machine (Sheets)
Uses =SUM
Instant calculation
Zero typos if the data is right
"In business, speed and accuracy equal profit."
The Equals Sign (=)
It is the Magic Wand of spreadsheets.
= 15 + 25
Without the =, the cell just thinks you're typing text!
Don't Type Numbers. Type Addresses.
The "Static" Way (Bad)
= 10 + 15
If your data changes, your answer is WRONG.
The "Dynamic" Way (Great)
= B2 + B3
If the price in B2 changes, the total updates instantly.
A B 1 Apples 10 2 Oranges 15 3 Total =B1+B2
The "Big Three" Functions
=SUM
Adds everything up for a total.
Example: =SUM(B2:B20)
=AVERAGE
Finds the "Middle" or Mean score.
Example: =AVERAGE(C2:C50)
=COUNT
Counts how many boxes have data.
Example: =COUNT(A2:A100)
The Power of the Fill Handle
Why write the same formula 100 times?
Look for the small blue square in the bottom right corner of a cell.
CLICK the blue square
DRAG it down
=SUM(A2:B2)
Mission: Calculate the Score
Part 1: Survey Analysis
1 Open Product_Survey_Data
2 Use =AVERAGE to find the mean appeal score.
3 Use =COUNT to see how many people responded.
Part 2: Budget Table
1 Create a table with 3 expenses (Materials, Marketing, Shipping).
2 Input random costs for each.
3 Use =SUM to find the Total Cost.
The "What-If" Test
Change one of the numbers in your budget table. What happened to the Total?
Submission: "I changed my [Expense] and the total automatically updated to [New Total]."
Data Detective Slides Unit 3: The Market Analyst
Data Detective
Sorting, Filtering & Trends
LEVEL 2 ANALYST
Finding the Needle
Imagine a spreadsheet with 10,000 rows of customers.
"Who is the 45th person who likes the color Blue?"
How long would it take to find them by scrolling?
Scroll... scroll... scroll...
Sorting (Organizing Chaos)
A to Z / 1 to 10
Arranges data in a specific order without hiding anything.
Put names in Alphabetical Order
Find your Highest Scores first
Organize by Date (Oldest to Newest)
A → Z
High to Low
Filtering (X-Ray Vision)
Filtering hides the data you don't want to see right now.
The Filter Goal
"Only show me responses from 7th graders who said 'Yes'."
The Button
Look for the Funnel icon to turn on "Filter Views."
Conditional Formatting
Make the spreadsheet change color automatically based on the answer.
If Score > 10, turn Green.
If Score < 5, turn Red.
It's Visual Data Analysis!
15 (Awesome!)
8 (Okay)
2 (Needs Work)
12 (Great)
Mission: The Target Segment Search
1
SORT
Sort by "Rating" (Highest to Lowest).
2
FILTER
Only show people who said "Yes" they would buy it.
3
FORMAT
Price Column: If over $10, turn Green.
Bonus Challenge: Can you find a "Market Trend"? What do the "Yes" people have in common?
The Discovery
After filtering your data, what is one thing that the people who liked your product have in common?
Type your discovery as a Comment in your spreadsheet.
Analyst Lab Teacher Guide Teacher Facilitation Guide
The Analyst Lab
Lessons 26 & 27: Formula Mastery & Data Detection
Unit 3: The Market Analyst
Total Time: 80 Minutes
Standards & Objectives
CTE Standard: I can use digital tools to organize and compare data.
Objective 1: Apply =SUM, =AVERAGE, and =COUNT functions to survey datasets.
Objective 2: Use Sort, Filter, and Conditional Formatting to identify target audience trends.
Prep & Tools
Google Sheets (Student Accounts)
Handheld Calculator (for L26 Demo)
Student File: Product_Survey_Data
Demo Spreadsheet with 200+ rows (for L27)
26
Formula Magic: The =SUM of All Parts
The Hook
6 Mins
The Human Calculator vs. The Machine
Pick a volunteer. Hand them a physical calculator. Read off 10 prices (e.g., $12.50, $8.99, $24.00, etc.). You type them into Sheets. The Moment of Truth: Use =SUM(A1:A10). You will beat the student every time. Key Insight: In business, manual math leads to "fat-finger" errors. Formulas are the insurance policy for accuracy.
Instruction
9 Mins
Technical Check-Points
THE EQUALS SIGN
Teach that = is the trigger. Without it, Sheets treats the cell as a text label. Call it the "Formula Gateway."
CELL REFERENCING
"Use addresses, not numbers."
Demonstrate changing a number in B2 and watching the SUM in B3 update automatically.
Watch Out
Common Pitfalls
Students often forget the colon in a range (e.g., typing SUM(A1 A10) instead of A1:A10). Remind them that the colon means "through."
27
Data Detective: Organizing Chaos
The Hook
6 Mins
Finding the Needle
Display a messy sheet with 200+ rows. Ask: "Who can find the 7th grader named 'Blue'?" Watch them scroll frantically. The Shift: Show them the Filter Funnel . Within 3 clicks, the list shrinks from 200 to 1. Point: Sorting/Filtering are "X-Ray Vision" for analysts.
Instruction
9 Mins
Mastering the Analysis Tools
1. Sorting
A-Z vs Z-A. Explain that sorting moves the whole row , not just one column (if done correctly through Data > Sort Range).
Formula Practice Worksheet Formula Fundamentals
Lesson 26 Activity Reference
Name:
Date:
Formula Cheat Sheet
=SUM(range)
Adds all numbers in the selected range together.
=AVERAGE(range)
Calculates the mean (average) of the numbers.
=COUNT(range)
Counts how many cells in the range contain numbers.
Task 1: Survey Analysis
Open your Product_Survey_Data sheet and perform these calculations at the bottom of your data columns.
1. Find the Average Appeal Score:
=AVERAGE(
2. Count the Total Responses:
=COUNT(
Pro Tip: Ranges
A "Range" is the group of cells you want to calculate. If your data starts in cell B2 and ends in B21, your range is B2:B21.
Task 2: The Budget Table
In a new area of your sheet, build a mini-budget. Use your imagination for the costs!
Expense Item Projected Cost Materials & Production Social Media Marketing Shipping & Distribution Total Cost (Use =SUM)
The What-If Challenge
Change one of your projected costs above in your spreadsheet. What happens?
I changed my:
The new Total is:
Trend Hunter Worksheet Data Detective Log
Lesson 27: Sorting, Filtering & Trends
Name:
Date:
Sorting
Reordering your data (A-Z or Low-High) to see patterns in ranks.
Filtering
Hiding rows that don't match your search criteria. It "cleans" your view.
Formatting
Making cells change color based on their value (e.g., $ > 10 = Green).
Investigation Checklist
The Power Sort
Click on your Rating column. Go to Data → Sort Range . Organize from Highest to Lowest (Z-A).
The Target Filter
Select your data. Click the icon. Filter the "Would you Buy?" column to show only Yes .
The Traffic Light Test
Highlight your Price column. Format → Conditional Formatting. Set a rule: If value is greater than $10, fill color = Green.
Trend Discovery Log
Look at the filtered list of "Yes" responses. What patterns do you see?
Common Trait Observed (e.g., Grade level, Hobby, Age):
My Discovery Statement:
"I noticed that most people who want to buy my product also..."
Submit this trend as a comment in your spreadsheet.
Formula Magic Slides Unit 3: The Market Analyst
The =SUM of All Parts
Lesson 26: Spreadsheet Magic & Accuracy
Data Lab
The Human Calculator
Human
Manual entry
High risk of typos
"Fat-finger" errors
Machine
Instant math
Zero typos
Absolute speed
The Equals Sign (=)
Without the =, the cell just thinks you're typing regular text.
= 10 + 20
Use Addresses, Not Numbers.
Static Way (Bad)
= 15 + 25
Change your data? Your total is WRONG.
Dynamic Way (Great)
= B2 + B3
Change B2? The total updates INSTANTLY.
A B 1 Price A 10 2 Price B 15 3 Total =B1+B2
The Efficiency Hack
Why write the same formula 100 times? Use the Fill Handle.
Click and drag the blue square in the bottom right corner of any cell.
=SUM(A2:B2)
The Little Blue Box of Speed
MISSION: CALCULATE
1. Survey Data
A. Open Product_Survey_Data
B. Use =AVERAGE for appeal score.
C. Use =COUNT for total entries.
2. Budgeting
A. List 3 main product expenses.
B. Input random cost values.
C. Use =SUM to find the Total.
Analyst Lab Teacher Guide Teacher Facilitation Guide
The Analyst Lab
Lessons 26 & 27 Master Guide
Unit 3: Market Analyst
Total Duration: 80 Minutes
Learning Objectives
Objective 1: Apply core functions (SUM, AVERAGE, COUNT) to analyze survey data.
Objective 2: Utilize the Fill Handle for rapid formula replication across large datasets.
Objective 3: Execute multi-level data analysis via Sorting, Filtering, and Conditional Formatting.
Materials Required
Google Sheets (Student & Teacher Accounts)
Physical handheld calculator (for Hook 26)
File: Product_Survey_Data
Demo Sheet with 200+ rows (for Hook 27)
26
Formula Magic: The =SUM of All Parts
The Hook
The Human Calculator Challenge (6 min)
Hand a handheld calculator to a volunteer. Read 10 prices ($14.25, $8.99, etc.). Type them into a Sheet. Demonstration: Use =SUM(range). Core Lesson: Speed + Accuracy = Professionalism. Formulas prevent manual entry errors.
Instruction
Technical Mastery Points (9 min)
Cell Referencing
Why =B2+B3? Show how changing B2 updates the total instantly. This is "Dynamic Math."
The Big Three
Introduce SUM, AVERAGE, and COUNT. Explain that COUNT is used to verify the sample size.
27
Data Detective: Organizing Chaos
The Hook
Finding the Needle (6 min)
Display a sheet with 200 random names/colors. Ask: "Who can find the 45th person who likes Blue?" The Pivot: Show the Filter View . Demonstrate how it isolates data without deleting it.
Instruction
Mastering the X-Ray Tools (9 min)
Filtering vs Sorting
Sorting rearranges. Filtering hides. Use both to find "Market Trends."
Visual Cues
Apply Conditional Formatting to make outliers "pop." Red = High Cost, Green = High Interest.
Assessment & Checklist
Lesson 26 Evidence
Formula bar shows cell referencing (not just numbers).
Budget table includes Material, Marketing, and Shipping costs.
Exit Ticket Check: Student can verify the "What-If" update.
Formula Practice Worksheet Formula Fundamentals
Lesson 26: Building Smart Spreadsheets
Name:
Date:
Formula Cheat Sheet
=SUM(range)
Adds all numbers together.
=AVERAGE(range)
Calculates the mean score.
=COUNT(range)
Counts entries in the range.
Task 1: Survey Analysis
Open Product_Survey_Data. Complete the formulas below:
1. Calculate Average Appeal:
=AVERAGE(
2. Count Total Responses:
=COUNT(
Pro Tip: Ranges
If your data starts in cell B2 and ends in B21 , your range is B2:B21.
Task 2: The Budget Table
Create this table in your spreadsheet. Use your own estimated costs.
Expense Item Projected Cost ($) Materials & Production Social Media Marketing Shipping & Distribution Total Budget (Use =SUM)
The What-If Challenge
In your spreadsheet, change one cost. Record what happens to your Total below.
Expense Changed:
Old Total:
New Total:
Formula Magic Slides Unit 3: The Market Analyst
The =SUM of All Parts
Lesson 26: Spreadsheet Magic & Accuracy
Data Lab
The Human Calculator
Human
Manual entry
High risk of typos
"Fat-finger" errors
Machine
Instant math
Zero typos
Absolute speed
The Equals Sign (=)
Without the = the cell just thinks you are typing regular text.
= 10 + 20
Use Addresses, Not Numbers.
Static Way (Bad)
= 15 + 25
Change your data? Your total is WRONG.
Dynamic Way (Great)
= B2 + B3
Change B2? The total updates INSTANTLY.
A B 1 Apples 10 2 Oranges 15 3 Total =B1+B2
The Efficiency Hack
Why write the same formula 100 times? Use the Fill Handle.
Drag the blue square in the bottom right corner of any cell.
=B1+C1
The Little Blue Box of Speed
Analyst Lab Teacher Guide Teacher Facilitation Guide
The Analyst Lab
Lessons 26 & 27 Master Guide
Unit 3: Market Analyst
Total Duration: 80 Minutes
Learning Objectives
Objective 1: Apply core functions (SUM, AVERAGE, COUNT) to analyze survey data.
Objective 2: Utilize the Fill Handle for rapid formula replication across large datasets.
Objective 3: Execute multi-level data analysis via Sorting, Filtering, and Conditional Formatting.
Materials Required
Google Sheets (Student & Teacher Accounts)
Physical handheld calculator (for Hook 26)
File: Product_Survey_Data
Demo Sheet with 200+ rows (for Hook 27)
26
Formula Magic: The =SUM of All Parts
The Hook
The Human Calculator Challenge (6 min)
Hand a handheld calculator to a volunteer. Read 10 prices ($14.25, $8.99, etc.). Type them into a Sheet. Demonstration: Use =SUM(range). Core Lesson: Speed + Accuracy = Professionalism. Formulas prevent manual entry errors.
Instruction
Technical Mastery Points (9 min)
Cell Referencing
Why =B2+B3? Show how changing B2 updates the total instantly. This is "Dynamic Math."
The Big Three
Introduce SUM, AVERAGE, and COUNT. Explain that COUNT is used to verify the sample size.
27
Data Detective: Organizing Chaos
The Hook
Finding the Needle (6 min)
Display a sheet with 200 random names/colors. Ask: "Who can find the 45th person who likes Blue?" The Pivot: Show the Filter View . Demonstrate how it isolates data without deleting it.
Instruction
Mastering the X-Ray Tools (9 min)
Filtering vs Sorting
Sorting rearranges. Filtering hides. Use both to find "Market Trends."
Visual Cues
Apply Conditional Formatting to make outliers "pop." Red = High Cost, Green = High Interest.
Assessment & Checklist
Lesson 26 Evidence
Formula bar shows cell referencing (not just static numbers).
Budget table includes Material, Marketing, and Shipping costs.
Exit Ticket: Student verifies the "What-If" dynamic update.
Trend Hunter Worksheet Data Detective Log
Lesson 27: Sorting, Filtering & Trends
Name:
Date:
Investigation Tasks
1. Rank by Rating
Sort your Rating column from Highest to Lowest (Descending).
2. Target Buyers
Filter the "Would Buy?" column to show only the "Yes" responses.
3. Price Highlighting
Add a Conditional Format rule: If price is > $10, turn the cell Green .
4. Identify a Trend
Find one trait the "Yes" group shares and log it below.
Trend Discovery Log
What is one thing the "Yes" buyers have in common?
Example: "They are all in 7th Grade" or "They all listed 'Sports' as a hobby."
Detailed Observation:
Submit your discovery as a comment in Google Sheets
Data Detective Slides Unit 3: The Market Analyst
Data Detective
Lesson 27: Finding the Perfect Audience
Trend Hunter
Finding the Needle
Imagine a spreadsheet with 1,000 students answering your survey.
"Who is the 45th person who likes your product AND is in 7th Grade?"
Scrolling takes forever. Analyzers work smarter.
Sorting (Organizing)
Rank Your Data
Moves the whole row to put your data in order (A-Z or High-to-Low).
Find your Best Customers instantly.
Group people by Grade Level.
A → Z
Highest to Lowest
Filtering (Focus)
Filtering HIDES the data you don't want to see right now.
The Goal
"Only show me responses from 7th graders who said 'Yes, I'd buy this!'"
The Result
The "clutter" disappears. You see your Target Audience.
Traffic Light Analysis
Make the spreadsheet CHANGE COLOR automatically based on the answer.
Rating > 8 = Perfect Match
Rating < 3 = Not Interested
10 (Awesome!)
6 (Okay)
2 (Bad Fit)
9 (Perfect!)
MISSION: FIND THE TREND
1
SORT
Sort by Rating (Z-A).
2
FILTER
Show only people who said "Yes".
3
IDENTIFY
Look for a COMMON TRAIT (e.g. Hobby or Grade).
"Great marketers don't just see data; they see patterns ."
Data Story Slides Unit 3: The Market Analyst
The Story in the Chart
Lesson 28: Data Visualization & Presentation
Analyst Pro
The 2-Second Rule
In business, you don't have 30 seconds to explain your numbers. You have 2 seconds to make a point.
Numbers
Accurate, but slow to read.
Visuals
A shortcut for the brain.
The Pie Chart
Parts of a Whole
Use this when you want to show Percentages.
"What percentage of customers would buy my product?"
75%
The Bar Graph
Making Comparisons
Use this when you want to see Difference between groups.
"Which price point ($5 vs $15) did customers like more?"
The Editor Checklist
1. Highlight
Select the labels and numbers you want to visualize.
2. Insert
Click Insert > Chart . Google does the heavy lifting!
3. Brand
Use your Hex Codes from Unit 2 to match your Brand.
Mission: Visual Proof
Part 1: Pie
Create a Pie Chart showing "Yes vs. No" for purchase intent.
Part 2: Bar
Create a Bar Graph comparing different price points.
Don't forget professional titles & brand colors!
Visual Proof Worksheet Visual Proof Lab
Lesson 28: Transforming Data into Stories
Name:
Date:
The Pie Chart
Use for: Parts of a whole (%).
The Bar Graph
Use for: Comparisons.
Lab Mission: Chart Creation
Chart 1: Purchase Intent (Pie)
Done
Highlight "Would Buy" column data.
Insert > Chart > Select Pie Chart.
Change Title to: Customer Purchase Intent.
Chart 2: Pricing Preferences (Bar)
Done
Highlight "Price Preference" data.
Insert > Chart > Select Column/Bar Chart.
Change colors to match your Brand Style Guide (Hex Codes).
Exit Ticket: The Chart Logic
Why is a Pie Chart better than a Bar Graph for showing if people liked your product (Yes/No)? Think about the "Parts of a Whole" rule.
Exported screenshot to Portfolio Folder
Visual Analyst Certification
Visualization Teacher Guide Teacher Facilitation Guide
Data Storyteller
Lesson 28: Visualizing the Market
Unit 3: Market Analyst
Total Duration: 40 Minutes
Learning Objectives
Objective 1: Distinguish between Pie, Bar, and Line charts based on data types.
Objective 2: Generate branded charts in Google Sheets using customized hex codes.
Objective 3: Interpret visual data to support marketing claims (The "2-Second Rule").
Prep & Tools
Google Sheets (Previous lesson data)
Unit 2 Hex Codes (Brand Style Guides)
Sample Dataset: Ice Cream Favorites
The Hook
6 Mins
The 2-Second Rule
Project a wall of numbers (Ice Cream data). Wait 30 seconds. Quiz them (e.g., "What was the 4th most popular?"). Silence is expected. The Pivot: Flash a Pie Chart of the same data for only 2 seconds. Students will instantly identify the winner. Conclusion: Charts are brains' "Visual Shortcuts."
Instruction
9 Mins
Choosing the Right Lens
Pie Charts
Parts of a whole. Use for "Yes/No" or "Percentages."
Bar Graphs
Comparisons. Use to see which price group or age group was larger.
Formatting
Branding is key. Teach students how to input Custom Hex Codes in the Chart Editor to match their logo.
Lab Time
20 Mins
The "Visual Proof" Project
Students must create two distinct charts. Monitor for Title Accuracy. A chart named "Chart 1" is a failure; it must be descriptive, e.g., "Purchase Intent for [Brand Name]."
Evidence of Mastery
Chart 1: The Pie
Correct data range selected (binary data).
Data Labels (percentages) visible on slices.
Colors match the Brand Style Guide.
Chart 2: The Bar
X and Y axis are clearly labeled.
Title is professional and descriptive.
Clear comparison between 3+ price points.
Analyst Tip: Encourage students to "explode" a slice of the pie chart in the Chart Editor to highlight their most important data point (e.g., the 75% who said "Yes").
Data Story Slides Unit 3: The Market Analyst
The Story in the Chart
Lesson 28: Data Visualization & Presentation
Analyst Pro
The 30 - Second Spreadsheet
Try to find the 3rd most popular ice cream flavor...
Flavor Votes Rank Vanilla 42 1 Chocolate 38 2 Strawberry 15 4 Mint Chip 22 3 Rocky Road 12 5
Finding info in a list takes focus and time.
The 2 - Second Rule
In business, you don't have 30 seconds. You have 2 seconds to make your point.
Vanilla
Chocolate
Mint Chip
Strawberry
Rocky Road
A Chart is a Visual Shortcut.
The Pie Chart
Parts of a Whole
Use this when you want to show Percentages.
"What percentage of customers would buy my product?"
75%
The Bar Graph
Making Comparisons
Use this when you want to see Difference between groups.
"Which price point ($5 vs $15) did customers like more?"
The Editor Checklist
1. Highlight
Select the labels and numbers you want to visualize.
2. Insert
Click Insert > Chart . Google does the rest!
3. Brand
Use your Hex Codes from Unit 2 to match your Brand.
Mission: Visual Proof
Part 1: Pie
Create a Pie Chart showing purchase intent.
Part 2: Bar
Create a Bar Graph for pricing preference.
Pro Titles & Brand Colors Required
Visual Proof Worksheet Visual Proof Lab
Lesson 28: Transforming Data into Stories
Name:
Date:
The Pie Chart
Use for: Parts of a whole (%).
The Bar Graph
Use for: Comparisons.
Lab Mission: Chart Creation
Chart 1: Purchase Intent (Pie Chart)
Task Done
Highlight "Would Buy" column. Select Insert > Chart.
Title: Customer Purchase Intent
Customize colors to match your brand logo.
Chart 2: Price Comparison (Bar Graph)
Task Done
Highlight "Pricing" data counts. Select Insert > Chart.
Use Hex Codes for bars. Enable Data Labels.
Exit Ticket: The Chart Logic
Why is a Pie Chart better than a Bar Graph for showing if people liked your product (Yes/No)?
I exported my best chart to my Portfolio folder
Visual Analyst Certification • Unit 3 • Lesson 28
Forecasting Slides Unit 3: The Market Analyst
Forecasting & The Bottom Line
Lesson 29: Predicting Profit & Growth
Financial Analyst
The Fortune Teller
If you sell 10 products today...
How many will you sell in one year?
Businesses don't guess. They use Forecasting to see the future.
The Bottom Line
Revenue
Total Cash In
Every dollar that enters your cash register before you pay any bills.
Sales × Price
Profit
What You Keep
The money left over after you've paid for materials, marketing, and shipping.
Revenue − Expenses
The Multiplier (*)
In spreadsheets, the * is the "Magic Key" for Multiplication.
Standard Math
10 × 5 = 50
Spreadsheet Logic
=10 * 5
Revenue = Units Sold * Price
$
The Anchor ($)
The $ "locks" a cell.
When you drag a formula down, the cell with the $ stays in place.
=$B$1 * C2
Lock your data!
Mission: The Projection
The Setup
List Months (Jan–June)
Enter 20 Units for Jan
Growth Cell: 1.10 (in $D$1)
The Formula Logic
=B2 * $D$1
Forecast Growth
=B2 * $C$2
Revenue Calculation
Growth Projection Worksheet v2 Bottom Line Lab
Lesson 29: Forecasting Your Business Growth
Name:
Date:
Revenue
The "Money In." Total cash before expenses.
Profit
The "Keepable Cash." Money after bills.
Spreadsheet Power Commands
The Multiplier (*)
=Units * Price
Example: =B2 * C2
The Anchor ($)
=$C$2 * B2
Locks the cell address in place.
Task: The 6-Month Projection
Build your growth table in Sheets using these steps:
1. Setup
Month A: Jan–June.
2. Forecast
Formula: =B2 * $D$1
3. Revenue
Formula: =B2 * $C$2
Profit Check
Scenario: Your total revenue is $500 , but your total expenses are $600 . Are you successful? What is one specific business change you would make to fix this "Bottom Line"?
Growth Formula Active
Financial Mastery • Unit 3 • Lesson 29
Forecasting Teacher Guide Teacher Facilitation Guide
Financial Forecaster
Lesson 29: Profit & Growth Logic
Unit 3: Market Analyst
Total Duration: 40 Minutes
Learning Objectives
Objective 1: Distinguish between Revenue (Money In) and Profit (Bottom Line).
Objective 2: Apply Multiplication Formulas using the * operator.
Objective 3: Execute Absolute Cell Referencing ($) to lock critical values.
Setup & Tools
Google Sheets (Student Accounts)
Student survey price data (from Lesson 26/27)
Sample: 6-Month Projection Template
The Hook
6 Mins
The Fortune Teller
Challenge students: "If you sell 10 products today, can you guarantee your success?" Use the concept of Seasonality or Growth . Point: Businesses need a "Roadmap for the Future." This is Forecasting. It prevents them from running out of money.
Instruction
9 Mins
Technical Finance Logic
Multiplication (*)
Remind them the "x" from math class is an asterisk here. =Units * Price.
The Anchor ($)
Explain the "Drifting Cell" problem. If you drag =B2*C1 down, it becomes =B3*C2. If C2 is empty, the math breaks. The $ locks it.
Bottom Line
Define Revenue (Gross) vs Profit (Net). Middle schoolers often confuse total sales with money they get to keep.
Lab Time
20 Mins
Forecasting Mission
Students should use =B2 * 1.10 to show 10% monthly growth. Check-point: Walk around and ensure they are dragging the formula using the Fill Handle (from Lesson 26) to see the $ lock in action.
Evidence of Mastery
Formula Logic
Revenue column uses multiplication (*) correctly.
Absolute reference ($) is applied to the Unit Price cell.
Growth column shows compounding 10% increase.
Profit Analysis
Exit ticket correctly identifies a loss (Net Loss).
Student suggests lowering expenses or raising price.
Table formatting is clear with currency symbols ($).
Forecasting Teacher Guide Teacher Facilitation Guide
Financial Forecaster
Lesson 29: Profit & Growth Logic
Unit 3: Market Analyst
Total Duration: 40 Minutes
Learning Objectives
Objective 1: Distinguish between Revenue (Money In) and Profit (Bottom Line).
Objective 2: Apply Multiplication Formulas using the * operator.
Objective 3: Execute Absolute Cell Referencing ($) to lock critical values.
Setup & Tools
Google Sheets (Student Accounts)
Student survey price data (from Lesson 26/27)
Required: Unit Price and Growth Rate (1.10).
The Hook
6 Mins
The Fortune Teller
Challenge students: "If you sell 10 products today, can you guarantee your success?" Discuss growth . Point: Forecasting is a roadmap. It tells you if you need to lower costs or sell more to survive.
Instruction
9 Mins
Technical Finance Logic
The Anchor ($)
Demo the "Drifting Error": If you drag =B2*C1, the C1 shifts to C2. If C2 is empty, math breaks. Use $C$1 to "anchor" the unit price.
10% Growth
Teach the multiplier 1.10. It's the original (1.00) plus the growth (0.10). Formula: =B2 * $D$1.
Lab Time
20 Mins
Forecasting Mission
Students must use absolute referencing for both the Growth Rate and the Unit Price. Check-point: Ensure students are dragging the formula using the Fill Handle to verify the $ anchor is working correctly.
Evidence of Mastery
Technical Check
Revenue column uses multiplication (*) operator.
Absolute reference ($) is applied to Unit Price and Growth Rate.
Profit Logic
Exit ticket identifies a Net Loss scenario.
Strategy provided (e.g., Raise Price, Lower Marketing Cost).
Exit Ticket Prompt
"If your total revenue is $500 but your total expenses are $600, are you successful? What is one specific business adjustment you would make to the 'Bottom Line' to become profitable?"
Forecasting Slides Unit 3: The Market Analyst
Forecasting & The Bottom Line
Lesson 29: Predicting Profit & Growth
Financial Analyst
The Fortune Teller
If you sell 10 products today...
How many will you sell in one year?
Businesses don't guess. They use Forecasting to see the future.
The Bottom Line
Revenue
Total Cash In
Every dollar that enters your cash register before you pay any bills.
Sales×Price
Profit
What You Keep
The money left over after you've paid for materials, marketing, and shipping.
Revenue−Expenses
The Multiplier (*)
In spreadsheets, the * is the "Magic Key" for Multiplication.
Standard Math
10 × 5 = 50
Spreadsheet Logic
=10*5
Revenue = UnitsSold*Price
$
The Anchor ($)
The $ "locks" a cell.
When you drag a formula down, the cell with the $ stays in place.
=$B$1*C2
Lock your data!
Mission: The Projection
The Setup
List Months (Jan–June)
Enter 20 Units for Jan
Growth Cell: 1.10 (in $D$1)
Price Cell: Your Price (in $C$2)
The Formula Logic
=B2*$D$1
Forecast Growth
=B2*$C$2
Revenue Calculation
Growth Projection Worksheet Bottom Line Lab
Lesson 29: Forecasting Your Business Growth
Name:
Date:
Revenue
The "Money In." Total cash before expenses.
Profit
The "Keepable Cash." Money after bills.
Spreadsheet Power Commands
The Multiplier (*)
=Units*Price
Example: =B2*C2
The Anchor ($)
=$C$2*B2
Locks the cell address in place.
Task: The 6-Month Projection
Build your growth table in Sheets using these steps:
1. Setup
Month A: Jan–June.
2. Forecast
Formula: =B2*$D$1
3. Revenue
Formula: =B2*$C$2
Profit Check
Scenario: Your total revenue is $500 , but your total expenses are $600 . Task: Are you successful? What is one specific business change you would make to fix this?
Growth Formula Active
Financial Mastery • Unit 3 • Lesson 29
Unit 3 Assessment Slides v2 Unit 3 : Final Phase
The Data Deep Dive
Lessons 30 & 31 : Portfolio & Assessment
Unit 3 Capstone
The Data Story
Numbers alone are just math. When you combine them with a persona and a purpose, they become a Story.
The Who
User Persona
The Proof
Visual Charts
The Result
Market Report
Live Linking
Don't just copy an image. Link the data.
Copy your Chart from Sheets .
Paste into Docs / Slides .
Select Link to Spreadsheet.
Change the math → The report updates!
Mission : The Report
Structure
The Audience : Your Persona.The Proof : Your 2 Charts.The Findings : 3 - Sentence Summary.
SAVE AS : "Market_Analysis_Draft"
The Investor's Eyes
"If I am handing over $1,000,000, do I want to see a messy list of numbers or a professional story?"
Clean Data
Branded Style
Zero Errors
The Final Polish
Dead Cell Check
#REF! or #VALUE!
Broken formulas mean broken trust. Fix them before submitting.
PDF Export
File → Download → PDF
PDFs lock your design so it looks perfect on every screen.
Portfolio Launch
Complete the checklist. Verify your Brand Style. Ship your work.
Market Analysis Report Template Assessment Part 1
Market Analysis
Synthesizing Research into Results
Portfolio ID
01
Target Audience Profile
Insert Portrait
User Persona Overview
Copy your key findings from Lesson 22 here. Who are they? What do they value?
02
Market Validation Proof
Insert Pie Chart
(Purchase Intent)
Insert Bar Graph
(Pricing Preference)
03
Executive Summary
Key Insights (3 Sentences Minimum)
Submission Requirements
Save As: Market_Analysis_Draft
Linked Graphics
Charts are "Paste Linked" so they update automatically from your sheet.
Peer Feedback
Report has been reviewed by a partner for clarity and "2-Second Rule" effectiveness.
Unit 3 Assessment Teacher Guide Teacher Facilitation Guide
Unit 3 Assessment
Lessons 30 & 31: Portfolio Synthesis
80 Minutes Total
30
Part 1: The Market Analysis Report
Skill Push
Paste Link Integration (5 min)
Most 7th graders will try to screenshot charts. Force the Insert → Chart → From Sheets or Copy → Paste Linked method. Point: Professional reports are dynamic. If the data changes later, the report should reflect it instantly.
Facilitation
Executive Summary Scaffolding
Write this sentence starter on the board for students who are stuck:
"The [Chart Type] proves that [Majority Group] prefers [Product Trait]. This means our brand should [Marketing Action]."
31
Part 2: Finalization & Portfolio
The Polish
Technical Audit Points
Formatting Check
Verify students used View → Freeze → 1 Row . Also check that all currency columns are formatted as "$ Dollars."
Error Hunting
Scan for #REF! errors. Remind them: "Investors lose trust when they see broken code."
Portfolio Rubric (Quick Look)
Technical (40%)
Accurate formulas, cell referencing used, frozen headers, currency formatting.
Visual (30%)
Branded Hex codes applied to charts, professional titles, labels visible.
Analysis (30%)
Executive summary accurately interprets data and suggests brand action.
Transition to Unit 4: The Digital Launch
The data gathered here is the foundation for their launch strategy. In the next unit, they will take this validation to build websites and campaigns that target the specific audience they've just proven exists.
Unit 3 Assessment Teacher Guide v2 Teacher Facilitation Guide
Unit 3 Assessment
Lessons 30 & 31 : Portfolio Synthesis
80 Minutes Total
30
Part 1 : The Market Analysis Report
Skill Push
Paste Link Integration
Force the Insert → Chart → From Sheets method. Professional reports are dynamic. If students update their survey math, linked reports update instantly.
Facilitation
Executive Summary Scaffolding
Sentence structure model for students:
"The [ Chart Type ] proves that [ Majority Group ] prefers [ Feature ]. Our marketing should focus on [ Strategy ]."
31
Part 2 : Finalization & Portfolio
The Polish
Technical Audit Points
Formatting Check
Verify Frozen Rows and Currency Formatting ($). These are professional standards.
Error Hunting
Scan for #REF! or #VALUE! errors. Remind students: "Data errors kill investor trust."
Portfolio Rubric
Technical (40%)
Accurate formulas, cell referencing, frozen headers, currency formatting.
Visual (30%)
Branded Hex codes applied, professional titles, labels clearly visible.
Analysis (30%)
Summary accurately interprets data and suggests strategic action.
Transition to Unit 4 : Digital Launch
Validation is complete. In the next unit, students will take this research to build websites and social campaigns targeting the specific audience they've just proven exists.
Unit 3 Portfolio Checklist Final Submission
Unit 3: Portfolio Assembly
The Market Analyst Certification
Student Final Checklist
1. Product Survey Spreadsheet
Frozen Headers
Top row is frozen so it doesn't disappear when scrolling.
Clean Formula Check
No #REF! or #VALUE! errors at the bottom of the data.
Market Scores
Final calculations for =AVERAGE and =SUM are accurate.
2. Branded Visual Charts
Pie Chart: Intention to Buy
Bar Graph: Pricing Levels
Colors match Brand Hex Codes
Professional Data Labels
3. Final Analysis Report
The centerpiece of your folder. Investors read this first.
Export as PDF (File > Download > PDF)
Unit 3 Reflection
Designing a logo (Unit 2) vs. Analyzing data (Unit 3). Which was more challenging? Why?
Market Analyst Certification • Submission Deadline: End of Period
Unit 3 Portfolio Checklist v2 Final Submission
Unit 3 : Portfolio Assembly
The Market Analyst Certification
Student Checklist
1. Product Survey Spreadsheet
Frozen Headers
Top row is frozen so it stays visible.
Clean Formulas
No #REF! or #VALUE! errors found.
2. Branded Visual Charts
Pie Chart : Intent to Buy
Bar Graph : Pricing Levels
Hex Codes Match Unit 2
Visible Data Labels
3. Final Analysis Report
Your research centerpiece. PDF Export is required for final submission.
PDF ONLY
Unit 3 Reflection
Designing a logo (Unit 2) vs. Analyzing data (Unit 3). Which was more challenging? Why?
Market Analyst Certification • Submission Deadline : End of Period
Value Proposition Slides v2 Unit 4 : Digital Launch
The Heart of Marketing
Lesson 32 : The Value Proposition
Campaign Launch
Reach For Your Wallet
Option A
"It is a 24oz blue bottle made of steel with a screw-on cap."
Describes the Object
Option B
"Keep your drinks ice-cold for 24 hours while helping save the oceans from plastic."
Describes the Value
"Customers don't buy objects; they buy the improvement it brings to their life."
The Big Promise
A Value Proposition is a promise of value to be delivered.
The Core Question
"Why should I buy from YOU and not your competitor?"
The Secret Formula
Action
Verb
Who
Target
The Win
Outcome
Example : Uber
"Tap a button, get a ride."
Example : Slack
"Where work happens."
Visual Hierarchy
H1
Heading 1 (Hero)
Your Value Prop. The largest text on the page.
Sub
The Sub-Value
Explains the "HOW" in one simple sentence.
Fresh Coffee Delivered to your Desk.
Our robot fleet brings your favorite brew in under 5 minutes.
Hero Text Worksheet v2 Launch Activity 32
Hero Text Lab
Lesson 32 : Mastering the Value Proposition
Student :
Brand :
The Formula
Verb
Target
Outcome
e.g., Save + Parents + Time
Phase 1 : Drafting
Write 3 ways to promise value. Focus on the Outcome for your customer.
Version A (The Direct Approach) :
Version B (The Emotional Benefit) :
Version C (The Problem Solver) :
Phase 2 : The Hero Header
Final Selection (H1 Headline)
Sub-Headline (The "How")
The Grandma Test
Would someone who knows nothing about your tech understand your brand in 5 seconds? Explain your reasoning.
Unit 4 Campaign Strategy • Lesson 32
Value Proposition Teacher Guide v2 Teacher Facilitation Guide
The Message Maker
Lesson 32 : Crafting the Value Proposition
40 Minutes Total
Learning Objectives
Obj 1 : Apply the Action + Audience + Outcome formula.
Obj 2 : Differentiate product features from customer value.
Obj 3 : Use H1 hierarchy to design a hero header.
Materials & Prep
Student Brand Style Guides (Unit 2).
Completed Unit 3 Market Research folders.
Access to Canva/Slides for design.
The Hook
The 30-Second Commercial
Compare Option A (It's a blue steel bottle) vs Option B (Keep drinks ice-cold for 24 hours). Key Point : Middle schoolers often sell "what it is." Push them to sell "what it does."
Direct Info
The Value Formula
ACTION
AUDIENCE
OUTCOME
Ex : "Refresh [Action] tired gamers [Audience] with zero-sugar energy [Outcome]."
Lab Time
Hero Header Design
Students design their website's "Hero Text." Ensure the Outcome word is bolded. If a headline is over 8 words, force them to "cut the fluff."
Evidence of Success
Messaging Mastery
• Headline starts with an active action verb.
• Sub-headline explains the technical "how."
• Passed the "Grandma Test" for clarity.
Visual Hierarchy
• Headline (H1) is visually dominant (2x size).
• Font matches Unit 2 Brand Style Guide.
• Layout is professional and center-aligned.
Teacher's Note
The message is the entrance to the brand. If the copy fails, the campaign fails. Focus on clarity over creativity today.
