In the fast-paced world of business, managing data efficiently can make all the difference. When John (not the client’s real name) needed help with his Excel spreadsheet (see figure 1 below, not his real data) to reassign account managers and balance revenue potential, he turned to TechMentors for expert guidance. Here’s how we transformed his frustration into satisfaction in just 45 minutes, and how you can apply the same techniques to your own Excel projects.
The Challenge
John, our client, had a complex spreadsheet listing over 200 companies, their annual revenue for the past three years, their projected future revenue, and a distribution of that revenue over five product lines. He needed to distribute the account manager assignments evenly across the five product lines. He was overwhelmed and unsure how to make these assignments while tracking the impact on client count and revenue potential.
The Solution
Step 1: Scheduling an Appointment
John visited our online calendar and scheduled a Zoom meeting at his convenience. During the meeting, he explained his challenge in detail.
Step 2: Adding a Metrics Sheet
We started by adding a new sheet named “metrics” to John’s Excel workbook. This sheet would help visualize the impact of account manager assignments in real-time.
Step 3: Counting Assigned Accounts
In column A of the metrics sheet, we listed John’s three account managers. In column B, we inserted a COUNTIF function to count the number of accounts assigned to each manager:
= COUNTIF(Data!C:C, A2)
Step 4: Summing Revenue
Next, we used a SUMIF function in column C to calculate the total weighted revenue for each manager’s accounts:
= SUMIF(Data!C:C, A2, Data!G:G)
Step 5: Average Revenue by Product Line
To see the distribution of revenue across the five product lines, we used an AVERAGEIF function in columns D through H:
= AVERAGEIF(Data!$C:$C, $A2, Data!H:h)
Step 6: Creating a Dynamic View
To allow John to see the metrics update as he made assignments, we created a second window (View > New Window) and arranged the windows side by side (View > Arrange All > Horizontal). This setup displayed the client list in the first window and the metrics in the second window.
Step 7: Testing and Verifying
Finally, we made a few test assignments to verify the metrics were calculating data as desired. John was able to see the immediate impact of his assignments on client count and revenue potential.
The Result
John was very impressed and pleased with the results. He could now make informed decisions about account manager assignments and instantly see the impact on his business metrics. This successful session demonstrates the power of Excel and the value of having a TechMentor by your side.