Why integration multiplies value in Google Sheets - Business Case Study

Scenario Mode

👤 Your Role: You are a business analyst at a retail company.

📋 Request: Your manager wants to understand how combining two marketing channels increases overall sales more than using them separately.

📊 Data: You have monthly sales data from two marketing channels: Email and Social Media. Each channel's sales and combined sales are recorded.

🎯 Deliverable: Create a spreadsheet that shows individual sales, combined sales, and calculates the multiplication effect of integration.

Sample Data

Month	Email Sales	Social Media Sales	Combined Sales
January	1000	800	2200
February	1100	900	2500
March	1200	950	2700
April	1300	1000	3000
May	1250	1050	3100
June	1400	1100	3400

1

Step 1: Enter the sample data into your Google Sheets with columns: Month, Email Sales, Social Media Sales, Combined Sales.

Expected Result

Data is organized in columns A to D, rows 2 to 7.

2

Step 2: Calculate the sum of Email Sales for all months.

=SUM(B2:B7)

Expected Result

7250

3

Step 3: Calculate the sum of Social Media Sales for all months.

=SUM(C2:C7)

Expected Result

5800

4

Step 4: Calculate the sum of Combined Sales for all months.

=SUM(D2:D7)

Expected Result

16900

5

Step 5: Calculate the expected combined sales if there was no integration effect by adding Email and Social Media sums.

=SUM(B2:B7)+SUM(C2:C7)

Expected Result

13050

6

Step 6: Calculate the integration multiplier by dividing actual combined sales by expected combined sales.

=SUM(D2:D7)/(SUM(B2:B7)+SUM(C2:C7))

Expected Result

1.295

7

Step 7: Format the integration multiplier cell to show 3 decimal places.

Use Format > Number > Number with 3 decimals

Expected Result

1.295

8

Step 8: Add a note explaining: 'Integration multiplier > 1 means combined marketing channels generate more sales together than separately.'

Expected Result

Clear explanation visible next to the multiplier.

Final Result

Month     Email Sales  Social Media Sales  Combined Sales
---------------------------------------------------------
Jan       1000         800                 2200
Feb       1100         900                 2500
Mar       1200         950                 2700
Apr       1300         1000                3000
May       1250         1050                3100
Jun       1400         1100                3400

Totals:   7250         5800                16900

Integration Multiplier = 1.295

This shows combined sales are 29.5% higher than sum of individual channels.

✓The combined sales (16900) are higher than the sum of individual sales (13050).

✓The integration multiplier is 1.295, meaning integration multiplies value by about 29.5%.

✓Using both marketing channels together creates more sales than using them separately.

Bonus Challenge

Create a chart showing monthly sales for Email, Social Media, and Combined to visualize the integration effect.

Show Hint

Use Insert > Chart, select Line chart, and include all three sales columns.

Practice

1. What does the SUMPRODUCT function do in Google Sheets?

easy

A. It multiplies corresponding elements in arrays and sums the results.

B. It adds all numbers in a single range without multiplication.

C. It finds the average of numbers in a range.

D. It counts the number of cells with numbers.

Solution

1. Step 1: Understand the function purpose

   SUMPRODUCT multiplies elements from two or more arrays element-wise.

2. Step 2: Sum the multiplied results

   After multiplying, it adds all those products to give a total sum.

3. Final Answer:

   It multiplies corresponding elements in arrays and sums the results. -> Option A

4. Quick Check:

   SUMPRODUCT = multiply then sum [OK]

Hint: Remember: multiply pairs, then add all products [OK]

Common Mistakes:

• Thinking SUMPRODUCT only sums without multiplying
• Confusing SUMPRODUCT with SUM or AVERAGE
• Assuming it counts cells instead of calculating products

2. Which of these is the correct syntax to multiply two ranges A1:A3 and B1:B3 and sum the results in Google Sheets?

easy

A. =A1:A3*B1:B3

B. =SUMPRODUCT(A1:A3, B1:B3)

C. =PRODUCT(SUM(A1:A3), SUM(B1:B3))

D. =SUM(A1:A3)+SUM(B1:B3)

Solution

1. Step 1: Check formula for multiplying and summing pairs

   =SUMPRODUCT(A1:A3, B1:B3) multiplies each pair and sums the results correctly.

2. Step 2: Verify other options

   =A1:A3*B1:B3 multiplies element-wise but returns an array spill instead of a single sum; =PRODUCT(SUM(A1:A3), SUM(B1:B3)) multiplies sums, not pairs; =SUM(A1:A3)+SUM(B1:B3) just adds sums.

3. Final Answer:

   =SUMPRODUCT(A1:A3, B1:B3) -> Option B

4. Quick Check:

   Correct syntax for pairwise multiply and sum is SUMPRODUCT [OK]

Hint: Use SUMPRODUCT for pairwise multiply and sum [OK]

Common Mistakes:

• Using * directly between ranges without SUM (array spill)
• Multiplying sums instead of element-wise pairs
• Adding sums instead of multiplying pairs

3. Given columns:
A1:A3 = {2, 3, 4}
B1:B3 = {5, 6, 7}
What is the result of =SUMPRODUCT(A1:A3, B1:B3)?

medium

A. 56

B. 72

C. 54

D. 90

Solution

1. Step 1: Multiply each pair of elements

   2*5=10, 3*6=18, 4*7=28

2. Step 2: Sum all products

   10 + 18 + 28 = 56

3. Final Answer:

   56 -> Option A

4. Quick Check:

   Sum of products = 56 [OK]

Hint: Multiply pairs, then add all results [OK]

Common Mistakes:

• Adding elements instead of multiplying
• Multiplying sums instead of element-wise
• Miscalculating individual products

4. You wrote =SUMPRODUCT(A1:A3, B1:B2) but get an error. What is the problem?

medium

A. You need to use SUM instead of SUMPRODUCT.

B. SUMPRODUCT cannot multiply ranges with numbers.

C. Ranges have different lengths, causing mismatch error.

D. Formula is missing parentheses.

Solution

1. Step 1: Check range sizes

   A1:A3 has 3 cells, B1:B2 has 2 cells; lengths differ.

2. Step 2: Understand SUMPRODUCT requirement

   SUMPRODUCT requires ranges to be same size to multiply pairs element-wise.

3. Final Answer:

   Ranges have different lengths, causing mismatch error. -> Option C

4. Quick Check:

   SUMPRODUCT needs equal-length ranges [OK]

Hint: Ensure ranges have same number of cells [OK]

Common Mistakes:

• Ignoring range size mismatch
• Thinking SUMPRODUCT works with different sized ranges
• Assuming syntax error instead of range mismatch

5. You have hourly rates in A1:A4 = {10, 15, 20, 25} and hours worked in B1:B4 = {2, 3, 1, 4}. Which formula calculates total earnings correctly?

hard

A. =SUM(A1:A4)*SUM(B1:B4)

B. =PRODUCT(A1:A4, B1:B4)

C. =SUM(A1:A4+B1:B4)

D. =SUMPRODUCT(A1:A4, B1:B4)

Solution

1. Step 1: Understand what total earnings mean

   Total earnings = sum of (rate * hours) for each entry.

2. Step 2: Choose formula that multiplies pairs and sums

   =SUMPRODUCT(A1:A4, B1:B4) multiplies each rate by hours and sums all.

3. Final Answer:

   =SUMPRODUCT(A1:A4, B1:B4) -> Option D

4. Quick Check:

   Use SUMPRODUCT for total of multiplied pairs [OK]

Hint: Multiply pairs then sum with SUMPRODUCT [OK]

Common Mistakes:

• Multiplying sums instead of pairs
• Adding rates and hours directly
• Using PRODUCT which multiplies all cells together