Navigating the Road Less Traveled

In this blog post, I'm going to illustrate how I would tackle the challenge presented by the "What Happens in Excel, stays in Excel!" problem statement. If you've been following my writing, you are likely familiar with this problem statement and the intricacies of working with Excel.

Let's start with the same problem statement and then dive into the solution using SharePoint.

Setting the Scene

Picture yourself several months into your role as a data manager, overseeing worldwide marketing coordination. Your responsibilities encompass approximately 60 sales representatives spread across the globe, divided into the familiar regions of the Americas, EMEA, and APAC. You've diligently marked your calendar "Out of Office" for an upcoming Thursday and Friday, anticipating a two-day conference.

However, on a Tuesday morning, your manager springs a surprise request on you: to create a presentation on sales forecasts for the upcoming year. The data must be categorized by the company's five traditional product categories, and you need to collect estimates from all 60 sales representatives. The catch? Your manager expects the presentation on their desk by Monday at noon, immediately upon your return from the conference. Furthermore, they've requested a Level 0 analysis.

Given your familiarity with the headaches of crafting an Excel template, you opt for a systematic approach using SharePoint.

Leveraging SharePoint for a Systematic Solution

You kick off by creating a new SharePoint list and adding the following fields: Region (choice: APAC, EMEA & Americas), Analyst (person or group, utilizing the official analyst group maintained by HR), and Product Category (choice with the 5 product categories). Additionally, you create columns for the current year actuals (Q1 Y, Q2 Y, Q3 Y, and Q4), and columns for the Q forecasts (Q1 Y+1, Q2 Y+1, Q3 Y+1, and Q4 Y+1). To keep track of progress, you create a Yes/No calculated field that updates to "No" once all forecast Q figures are filled in, aptly named "Pending."

Now, it's time to populate the SharePoint list with existing data. You replicate the Excel layout with current data, analyst names, and product categories. Returning to SharePoint, you edit the list in grid view, select a blank record, and paste the Excel records. SharePoint swiftly processes and ingests the data without errors.

Creating Foolproof Views

To ensure a foolproof system, you create several views:

1. A nested view by region to list all analysts in the same region, color-coded in green when the Pending field is "No."

2. A duplicate of the previous view with a filter applied for when the Pending flag is "Yes," serving as the "wall of shame" for uncooperative analysts.

3. A 'Personal' view that filters data for each analyst based on the SharePoint variable @[Me], preventing interference with others' data.

4. 
   

Bringing it All Together

You consolidate everything by creating a new SharePoint page with three collapsible sections: Instructions, Pending Board, and Personal Workspace, embedding the views you've created. The result exudes professionalism and organization.

Lastly, you assign proper permissions to the page and list, based on the HR Group. In a neatly crafted email, you provide basic instructions and direct everyone to the page. On your main site page, a banner links to this page as a backup option.

The Day After

You arrive an hour earlier, well-prepared to impress your audience. First, you check the Pending Board and notice that only a few analysts haven't filled in their data.

You discover urgent emails, some indicating discrepancies in the HR list, resulting in a few individuals left out. They've sent their figures in Excel, a minor challenge since there are only three of them. These records replace the missing analysts, as they are no longer with the company. After some Excel wizardry, you integrate the missing data and add the new analysts to your SharePoint list.

Now, the fun part begins. You request SharePoint to export the list's contents to a pivot table with a persistent connection. This sets the stage for the creation of an impressive dashboard complete with slicers, dynamic graphs, trend lines, and drill-down options in no time.

Rather than using PowerPoint, you opt for a different route. You turn to SharePoint's PowerApps feature and select Power BI, enabling data flow to a Power BI dashboard with default visualizations. You fine-tune the report, save it, and copy the embedding URL. Returning to SharePoint, you create a new SharePoint page and embed the Power BI visualization while providing a link to the Excel file containing the dashboard.

With everything in place, you send the newly created page's link to the meeting's audience, titling it as the information to be reviewed. It's an efficient, organized, and visually engaging solution to a complex problem.

The morning after, you're ready to impress, knowing that you've successfully navigated the less traveled road to proper data management by 'Getting Rid of Excel!!"