Excel and thinkcell Analysis Automation Expert
Budget: €8 – €30 EUR
**Job Title:**
Excel & Think-Cell Specialist for Zone KPI Analysis Automation
---
## **Project Overview**
We need an expert to build a single, master **“Zone KPI Analysis”** worksheet inside our existing Excel file that:
1. **Pulls & aggregates** every KPI used in our Free Platform Performance Dialogue slides (10 zones).
2. **Pre-calculates** all four quarters (Q1–Q4) of Booked & Committed Orders and Revenue.
3. **Computes** FY22–FY25 Act/Plan values, deltas vs MTA, funnel “rest,” backlog, and new-vs-core market splits.
4. **Feeds** those results into Think-Cell charts via simple copy-&-paste.
Once set up, updating any quarter or refreshing source data should automatically refresh **all** zone rows—then anyone can copy their row to the PPT slides without digging through 7 different sheets.
---
## **Files You’ll Work With**
- **Excel:** `Free.Funnel – CRM Data Cloud.xlsx`
- Sheets:
- **MBR PV06** (quarterly & MBR vs MTA KPIs)
- **Summary FY25** (FY25 Total Funnel & Act/Scheduled)
- **InVivo FreD OLIs** (New/Core market flags & market-type)
- _… plus existing “New Market Analysis” for design inspiration_
- **PowerPoint:** `2024.11 Free. Platform PD Collection MBR25 round.pptx`
- Slides 6, 8, 10, 16, 24, 36, 46, 56, 63 (one per zone)
---
## **Key Deliverables**
1. **“Zone KPI Analysis”** tab in Excel, one row per zone (ASN, CEECA, CHN, CWE, IND, JPN, LAM, MEA, NAM, SEU) with columns for:
- **FY22–FY24 Act Orders & Revenue** (GETPIVOTDATA from `MBR PV06`)
- **FY25 Plan Orders & Revenue** (MBR25 25.4)
- **Δ FY24 vs MTA & Δ FY25 vs MTA** (MBR vs MTA cols AN/AO)
- **Q1–Q4 Booked & Committed** (PV06 cols AB/AC + AD/AE + AF/AG + AH/AI)
- **FY25 Total Funnel & “Rest”** (Summary FY25 col C minus sum of Qn)
- **FY25 Act/Scheduled** (Summary FY25 col D) & **Backlog** (`MBR PV06` col AK)
- **# New vs # Core Markets** and **% splits** (COUNTIFS on `InVivo FreD OLIs` Flag Y/N)
- **# New-Market by Type**: Hospitals, Imaging Centers, Ortho & Pain, Dental, Veterinary
2. **PivotTables** (or equivalent) set up on three source ranges, with `GETPIVOTDATA` formulas reading directly from those pivots.
3. **Mapping Guide** tab listing each output column → exact formula → source sheet/column.
4. **1-page “How to Refresh & Copy”** instructions:
- Refresh pivots
- Copy the appropriate cells for each slide
- Paste into Think-Cell data sheet
5. **Example proof-of-concept**: Populate the row for **Zone ASN** and show it pasted into Slide 6’s Think-Cell data sheet.
---
## **What We Expect From You**
- **Deep Excel expertise:** PivotTables, `GETPIVOTDATA`, `INDEX/MATCH`, `COUNTIFS`.
- **Attention to detail:** Every KPI on each slide must map to exactly one cell in Excel.
- **Think-Cell familiarity:** Understand how to copy multi-series data into chart sheets.
- **Clear documentation:** Mapping Guide & refresh instructions must be bullet-proof.
- **Proactive QA:** Verify that values in Excel match those on the PPT (accounting for slight Q1-to-Q3 shifts).
---
## **Timeline & Communication**
- **Kick-off**: ASAP
- **Draft deliverable**: within 5 business days
- **Revision & QA**: 2 days after draft
- **Final hand-off**: within 10 days of start
Please include in your proposal:
1. **Brief summary** of your Excel + Think-Cell experience.
2. **A screenshot** or small snippet of a similar pivot-powered KPI dashboard you’ve built.
3. **Confirmation** that you’ve reviewed the project scope and can hit the timeline.
We’re looking for a razor-sharp freelancer who can turn our manual, error-prone update process into a **5-minute refresh & paste** for all zones, every quarter. Let’s make our next PPT update effortless!
Excel & Think-Cell Specialist for Zone KPI Analysis Automation
---
## **Project Overview**
We need an expert to build a single, master **“Zone KPI Analysis”** worksheet inside our existing Excel file that:
1. **Pulls & aggregates** every KPI used in our Free Platform Performance Dialogue slides (10 zones).
2. **Pre-calculates** all four quarters (Q1–Q4) of Booked & Committed Orders and Revenue.
3. **Computes** FY22–FY25 Act/Plan values, deltas vs MTA, funnel “rest,” backlog, and new-vs-core market splits.
4. **Feeds** those results into Think-Cell charts via simple copy-&-paste.
Once set up, updating any quarter or refreshing source data should automatically refresh **all** zone rows—then anyone can copy their row to the PPT slides without digging through 7 different sheets.
---
## **Files You’ll Work With**
- **Excel:** `Free.Funnel – CRM Data Cloud.xlsx`
- Sheets:
- **MBR PV06** (quarterly & MBR vs MTA KPIs)
- **Summary FY25** (FY25 Total Funnel & Act/Scheduled)
- **InVivo FreD OLIs** (New/Core market flags & market-type)
- _… plus existing “New Market Analysis” for design inspiration_
- **PowerPoint:** `2024.11 Free. Platform PD Collection MBR25 round.pptx`
- Slides 6, 8, 10, 16, 24, 36, 46, 56, 63 (one per zone)
---
## **Key Deliverables**
1. **“Zone KPI Analysis”** tab in Excel, one row per zone (ASN, CEECA, CHN, CWE, IND, JPN, LAM, MEA, NAM, SEU) with columns for:
- **FY22–FY24 Act Orders & Revenue** (GETPIVOTDATA from `MBR PV06`)
- **FY25 Plan Orders & Revenue** (MBR25 25.4)
- **Δ FY24 vs MTA & Δ FY25 vs MTA** (MBR vs MTA cols AN/AO)
- **Q1–Q4 Booked & Committed** (PV06 cols AB/AC + AD/AE + AF/AG + AH/AI)
- **FY25 Total Funnel & “Rest”** (Summary FY25 col C minus sum of Qn)
- **FY25 Act/Scheduled** (Summary FY25 col D) & **Backlog** (`MBR PV06` col AK)
- **# New vs # Core Markets** and **% splits** (COUNTIFS on `InVivo FreD OLIs` Flag Y/N)
- **# New-Market by Type**: Hospitals, Imaging Centers, Ortho & Pain, Dental, Veterinary
2. **PivotTables** (or equivalent) set up on three source ranges, with `GETPIVOTDATA` formulas reading directly from those pivots.
3. **Mapping Guide** tab listing each output column → exact formula → source sheet/column.
4. **1-page “How to Refresh & Copy”** instructions:
- Refresh pivots
- Copy the appropriate cells for each slide
- Paste into Think-Cell data sheet
5. **Example proof-of-concept**: Populate the row for **Zone ASN** and show it pasted into Slide 6’s Think-Cell data sheet.
---
## **What We Expect From You**
- **Deep Excel expertise:** PivotTables, `GETPIVOTDATA`, `INDEX/MATCH`, `COUNTIFS`.
- **Attention to detail:** Every KPI on each slide must map to exactly one cell in Excel.
- **Think-Cell familiarity:** Understand how to copy multi-series data into chart sheets.
- **Clear documentation:** Mapping Guide & refresh instructions must be bullet-proof.
- **Proactive QA:** Verify that values in Excel match those on the PPT (accounting for slight Q1-to-Q3 shifts).
---
## **Timeline & Communication**
- **Kick-off**: ASAP
- **Draft deliverable**: within 5 business days
- **Revision & QA**: 2 days after draft
- **Final hand-off**: within 10 days of start
Please include in your proposal:
1. **Brief summary** of your Excel + Think-Cell experience.
2. **A screenshot** or small snippet of a similar pivot-powered KPI dashboard you’ve built.
3. **Confirmation** that you’ve reviewed the project scope and can hit the timeline.
We’re looking for a razor-sharp freelancer who can turn our manual, error-prone update process into a **5-minute refresh & paste** for all zones, every quarter. Let’s make our next PPT update effortless!