Tag Archives: Data

Learn How to Analyze Big Data for Free

Intro Databases and Statistical Learning“What Should I Study?”

Right before I started my first full-time job, I had a good talk with the chairman of my university’s supply chain department. I asked him, “If I were going to study more after I graduate, what do you suggest I focus on?” Without a second of hesitation, he responded, “databases and statistics.”

Really? Databases and statistics?

I had taken the required introductory statistics class. Then, I promptly forgot everything that Excel couldn’t automatically do for me.

I had also done a few queries in Microsoft Access, but anything more on databases was taught over at the computer engineering school – not the business school.

As I entered the workforce, I read up on many other topics related to my field: books on negotiations, communication, and network optimization. I received my APICS certification. Logistics and purchasing trade magazines covered my desk.

I eventually taught myself SQL through Sams Teach Yourself SQL in 10 Minutes so that I could better query our company’s databases, but that seemed like more than enough database knowledge for what I needed to do.

However, after a few years, I kept running into the term “big data.” Although there are many definitions for big data, I like to think of it as ‘more data than an Excel spreadsheet can handle’. What’s cool about big data, and the reason it’s gathered so much attention, is that you can find trends and patterns that were impossible to uncover before companies started collecting so much information. Faster computers also allow you to analyze that information without waiting weeks for your calculations to process.

All of this is extremely important to supply chains and company operations. Millions of dollars are just waiting to be saved if you can uncover better trends and patterns, especially in forecasts.

The surprise for me, however was that to be a big data ninja (or analyst, if you want to use the boring job title), you need fairly decent skills in two areas. Would you like to guess what those two skills are?

Databases and statistics.

Learn Databases and Statistics through Free Online Courses

Well here’s the best part of this article – you can start mastering both of those topics for free.

Stanford University is offering free online classes on both of those topics right now. The process is easy and straightforward:

  • Professors lecture, explaining concepts and examples through online videos
  • You read the free course materials and/or books
  • You work through examples with free open-source software
  • You take online quizzes and talk with other students.
  • You learn some awesome big data skills and even get a certificate of completion from Stanford when you finish

To enroll, you just register at the class websites: Introduction to Databases and Statistical Learning.

I’m taking the statistical learning class right now and I’m really enjoying it. A friend of mine finishing his MBA program let me know about the statistical learning class. His professor suggested that he may want to take it as he heads off to work for UPS.

The classes started about a month ago, but there’s no problem starting late and jumping in now. Plus – there’s no risk at all – so if you start and realize it’s not the thing for you, then oh well, no worries.

Why I’m Studying Statistical Learning

Databases make sense if you’re interested in getting into big data. However, statistics seems a little more intimidating to me. Here’s the reason I’m taking the statistical learning class.
Before the class, I knew how to use the trendline function in Excel to find the relationship between two variables. I could easily figure out if there’s a correlation between sales and the money spent on TV advertising.

However, now I’m learning how to find the correlation between multiple variables, such as sales and combined advertising in TV, radio, and newspaper. I’m in the middle of chapter 3, where I learned what method to use to figure out whether a variable actually relates, or if other variables are responsible for the change. For example, shark attacks and ice cream sales both go up in the summer, but ice cream isn’t causing shark attacks. Similarly, in the advertising data I’m working with, newspaper advertising appears at first glance to have an effect on sales. However, when we look at all the variables together, newspaper advertising doesn’t affect sales at all – only TV and radio advertising do.

Now that’s an awesome observation if you work in marketing, but how will that help our supply chain? I plan to approach our forecasts much differently after understanding these statistical analysis techniques. If I could find relationships on dimensions such as date, location, promotion, price, and other variables, then I could get much better forecasts and hold less inventory. Even if I could improve our forecasts just a little, that’d more than make my time in a free class worth it.

The class teaches you how to use an open-source program called R, which many companies are beginning to use such as Google, P&G, and Ford. If they’re using it, and it’s a free program, then maybe my growing company should use it too.

If anyone is brave enough to sign up with me, let me know, and we can work together on any tough problems we encounter.

Supply Chain Cowboy ApprovedThat’s my thought for the week. The internet and improvements in technology have given us the challenge and opportunity of big data. Similarly, the internet and Stanford has given us a free way to learn how to surmount that challenge. Pretty cool, and definitely Supply Chain Cowboy approved.

If big data is something that interests you, here’s a recent, related article: Getting Started with Big Data in a Small Business

Build an Awesome Vendor Scorecard Program in 4 Easy Steps

Vendor scorecards measure and track supplier performance on various dimensions that are important to your organization. At first, I was reluctant to start a scorecard program because I thought our company was too small and too busy. However, after eventually beginning our program, I saw powerful results that freed up time and helped the company grow.

Vendor Scorecard Example

Vendor Scorecard Template with ExamplesVendor scorecards strengthen supply chain relationships and help focus your suppliers on what matters most to you. Scorecards set goals for your vendors to reach for so they can become your vendor of choice. You can clearly see where each vendor ranks against each other, which helps you decide which supplier to work with on complex projects. This article outlines the four steps I took in building our company’s vendor scorecard program. I have attached a Excel Vendor Scorecard Template that I put together as a starting place for your own scorecard.

1. Decide What Matters

The first step in creating a vendor scorecard program is to define what your ideal vendor would look like. For me, it would be someone that communicated clearly 100% of the time, shipped quality products for free, and had a lead time of 15 minutes. Although those requests are a bit ridiculous in my industry, it does highlight what matters to me in my vendors: communication, quality, pricing, and lead-time. Together with my team, we took my brainstorm farther and came up with four categories that matter most to us with our vendors:

  • Pricing/costs, including payment terms
  • Production and Supply chain, including communication and lead-time
  • Quality
  • Product Development

Essentially, if our vendors could continually improve on these four points each year, our organization would benefit immensely.

2. Measure the Metrics

Having defined the broad categories, we now have to build the nitty-gritty of the scorecard. You need to build specific, measurable metrics for each category. Specifically, what exactly will you measure, and more importantly, how? For example, a pricing metric could be a comparison of costs between all capable vendors. A quality metric might be the percentage of orders with quality defects.

Good scorecard metrics should clearly define what is good, acceptable, and bad performance in each dimension. Your metrics should be a score for how your vendors are doing in aspects that matter most to you. They should be easy to understand, and if possible, easy to calculate. Unfortunately, building the perfect metrics often takes some deep thought to get them right.

Nailing the Details is Key

Many metrics were much more complicated to fully define than I thought they would be. For example, lead time is an excellent metric that I use. Tracking the time from when you place an order to when it gets delivered is a great way to compare vendors and encourage reductions in lead time. However, measuring this can be tricky when you get into the details. Should you track the time until delivery at to your location or delivery at port? If you ask a vendor to delay a shipment, will their lead-time artificially inflate?

For most quantitative metrics, your accounting system should have the records you need. However, based on the specific things you want to measure  you also might need to start tracking new events or information. For both of the above lead-time questions, I had to change our receipt processes to account for how we wanted to measure that metric. Despite the added work, tracking more data allowed us to trust our metrics and better compare our vendors apples to apples.

A Note on Subjective Scores

When hard data is unavailable or impossible, use a subjective grade. For example, “This Vendor is Flexible in Requests to Alter Production” is a difficult metric to track in our ERP system. Instead, at the end of each quarter, our supply chain team fills out a survey for each vendor that rates them on several dimensions such as flexibility. Rating vendors on a scale is the best way to get a good score from a soft metric. Even better is when the survey has an example for a top, middle, and bottom score for the metric so that scoring is more consistent across teammates. Recording everything in a free Google Form that you send out to your team is even better.

Google Doc Questionnaire 2

Weight What Matters

Once you have the metrics you want to measure (I have 4-6 in each category), it’s time to weight them. Start by rating the overall categories. The pricing category may be 25% of the total score, quality 40%. When your categories equal 100%, weight the individual components of each category. For example, if the quality category is weighted at 20% and has three metrics, then those three metrics could be 5%, 12%, and 3%, which adds up to 20%. The Vendor Scorecard Template shows my weighting.

Example Weighting

Pull Out the Gradebook

Maybe it’s from the report cards I received every semester in public school, but the A through F scale carries a lot of significance to me. That’s why I like to use that scale for each of my metrics. Some can only receive an A or F, or A, C, or F, but they all have the same percentage score. Based on their grade, vendors receive a percentage of that metrics weight as follows:

  • A – 100% A metric with 10% of the total scorecard weight would be 10% with an A
  • B – 75% (7.5% with the same metric)
  • C – 50% (5%)
  • D – 25% (2.5%)
  • F – 0%

Color-coding the scale adds the final touch of understanding so that it translates well and conveys the message clearly.

Example Weighting

Build the Document

Finally, once you’ve figured out your categories, metrics, and weighting, put it all together in a spreadsheet scorecard. You can use my template as a starting point to build your own.

3. Roll Out the Program

Once your scorecard is complete, implementation is your next bull to lasso. You’ll need to devise a plan to clearly communicate what, why, and how you are measuring your vendors. Depending on your suppliers, your experience could be much different, but here’s what I did.Why a Vendor Scroecard?

First, I put together a presentation with one or more slides explaining the following. It was detailed and thorough so that our vendors could clearly understand each score. Specifically, the document had the following:

  • A detailed explanation of each category and metric
    • For complex calculations, I included an example slide
    • Explanation of weights were also included
  • Reasons why we were beginning the vendor scorecard program
  • The implementation schedule (trial and full launch)
  • Our commitment to our vendors

Armed with a document that clearly defined the program, our CEO emailed the presentation and the scorecard spreadsheet to the leadership of our key suppliers. He asked them to review it and then meet with us in a video conference discussing the program. During the meetings with our six key suppliers, the CEO expressed support of the program and our supply chain team explained the details. Most vendors appreciate being measured on more than just price, and so all of our vendors were excited about the program as a chance to prove their holistic value to our company.

We designated the first month as a trial period where we would track performance, iron out issues, and report scores but not take action based on their results. After meeting at the end of the first month to discuss the trial run, we began the program in earnest.

4. Review and Reward

What will make your vendor scorecard program truly succeed is your diligence after implementation. I strive to send out scorecards on-time at the end of every quarter. My team schedules meetings via Skype or in person to review the scorecard each quarter and discuss ways to improve. The communication is two-way – we want all our vendors to reach perfect scores. That is why we council openly about what each of us can change to improve the metrics.

Another big decision to make is what you’ll do because of the scores. Will vendors with consistently high scores obtain a preferred status? Will quality checks or audits happen less frequently? Will you distance yourself from vendors who are very cheap, but fail in every other category? Will you reward contracts based on scores?

If you find yourself rewarding higher scores with more business, then your weighting is probably correct. However, if more and more business is still going to vendors with lower scores, then consider revising your scorecard to better reflect your company’s true priorities.

A great and relatively inexpensive way to encourage scorecard improvement is a vendor of the year program. This could involve a personal meeting, dinner with the CEO, and a plaque for the winning company. When I watch the “Walmart Vendor of the Year” award go to one of my competitors, I find new motivation to improve. Your suppliers may feel the same.

Bonus Step – Survey Your Vendors for Improvement Tips

If your vendor scorecard program is chugging along, then consider asking your vendors to score you. Sending a quarterly feedback survey to your vendors to discuss at the same time as their scorecard can bring insights into how you can be a better customer. Some questions could be:

  • What good practices do your other customers do that you wish we did?
  • What can we do to help you reduce lead-time?
  • What was an example of a project that went well? What about that experience can we recreate for all future projects?

If you make it clear they won’t be penalized for honesty, then you may be lucky enough to get great feedback on how to truly improve. Becoming a better customer can help your vendors better service you. In addition, you may pick up some best practices from their other customers or resolve root causes of your own deep problems. Address these issues in the scorecard review meetings and make commitments to improve when possible. We received a lot best practice tips from our vendors when we said, “we’re really bad at forecasting, so we’ve brought on staff with forecasting experience and invested in the software we needed.” They detailed how their other customers forecast and recommended we try the same.

Final Thoughts

As I talked about in my article on supply chain gamification, games have a way of bringing out our passion and motivation. A vendor scorecard brings the power of game mentality to supplier relations. “Just keep everything green and keep out reds” becomes the goal of your vendors. “Work with the highest scoring vendors” becomes your vendor selection shortcut. Measuring progress brings improvement that both your vendor and you will enjoy.

From the success I’ve seen from the program, I wish I had started it years ago. This quickly brought to mind the mantra of a friend of mine in process improvement. “There’s two good times to plant a tree: twenty years ago and now.”

If you haven’t started a program yet, begin today. If you have one already, take a look at how you can improve. Either way, share your experience in a comment below.

Update – Learn More about Vendor Scorecards in our Podcast

In our podcast interview with Mark Kosiba (former VP of Operations at Skullcandy), Mark talks about vendor scorecards and their effect on his company. The above model was based on his help, so it definitely applies to anyone wanting to implement a vendor scorecard program similar to the above.

Check out the podcast to learn more: How Skullcandy Rocked S&OP (and Vendor Scorecards)

Stop Throwing Away What You Learn

Throwing Away What you Learn

My wife and I have a running joke about asking certain types of questions. She will ask me something like “Do you know when Easter is this year?” In an ironic tone, I’ll reply, “if only there was a global electronic network of information that you could access from your phone that would answer that question.” She’ll roll her eyes at me, lightly punch me in the arm, and then pull out her phone and ask Google her question. Wikipedia usually has the answer, and our conversation moves on. Rest assured though that my wife takes great pleasure answering the same way when I ask similar questions that the Internet easily answers.

Even though that question is sarcastic for general questions, it holds real meaning for questions in a small business. “Where do I find the form to request time off?” “Where is our procedure for creating an invoice through EDI?” “What are Walmart’s shipping requirements?” To all these, I reply, “If only there was a company-wide, easily accessible, and searchable resource that would answer that question.” From that idea came our company’s wiki.

Background on Wikis

A wiki is collection of information that can be edited by most or all users of the program. Wikipedia is the best-known example, an encyclopedia that anyone can edit or add information to. Creating a similar, living encyclopedia of information for our business has been incredibly helpful. Being able to add procedures, forms, and other information to a location where any employee can quickly access them allows us to share knowledge and train others better.

A wiki is a powerful tool in sharing knowledge across the company. Rather than needing to explain every procedure, employees trying something new can first search the wiki to see if a documented process can show them what to do. This is especially useful if someone is out of the office for vacation. The wiki empowers others to cover for others whose processes are documented on the wiki – and employees can enjoy their vacation time without phone calls asking for help from work.

How a Wiki can Help Business

The reason a wiki is different from just a bunch of text documents somewhere on a hard drive is accessibility and editability. A wiki indexes information so that you can search and easily find what you need. Finding the “Time Off Request” form is so much easier to find through a search bar than digging through folders in Windows Explorer or asking HR for the form’s location.

Company Wiki Search

Second, a wiki’s true power comes in the ability to edit and update information. When Walmart changes a shipping requirement, I can go to the Walmart page, press the edit button, type the new requirement, and then hit save. The pages are so easy to edit that despite their busy schedules, my team is able to find time to document common procedures. Wikis allow for the ease of sharing information, so that knowledge of processes is not locked away in the minds of individual employees. Without such sharing, we are essentially throwing away all acquired knowledge each time someone is away from the office (temporarily or permanently).

Wikis are also great to help accomplish work that is not performed regularly. For example, every six months or so, our ERP system needs to be reinstalled on a certain machine because of a problem on that machine. The first time, it took several days for me to figure out how to do the install because of the machine’s unique setup. After figuring it out, I jotted some quick notes on the procedure and posted them on a wiki page under the IT section. Six months later, after I had long forgotten what I had done to fix the issue, the machine started erroring again – the signal to reinstall. This time, I jumped on the wiki and searched for the document. My notes popped up as the first result and the machine was as good as new 15 minutes later.

Wiki Options for Small Business to Consider

Company intranets and wikis are nothing new– but many businesses have yet to implement one. Fortunately, adding a company wiki is easy and affordable (there are many great, free options). Here are a few options to consider.

Confluence by Atlassian – $10+

Confluence LogoThis is the system my company uses. It’s not free, but for 10 users, it’s only $10. I like it because it’s very easy to use, has extensive documentation and tutorials on how to use it, and you can edit the theme to make it look more attractive. The last point isn’t important to me, but it helped get executive sign off to work on it (looks and appealing design are important at my company).

Confluence

MediaWiki – Free

Media WikiIf you use Wikipedia frequently, then you’ll feel right at home with MediaWiki. MediaWiki is what Wikipedia is based on. It’s free and very popular, which means there’s a strong community and many tutorials to help you easily install and run it.

MediaWiki

MediaWiki Sysadmin Hub (Installation Instructions)

Tiki Wiki – Free

Tiki Wiki LogoIf MediaWiki doesn’t have quite enough features that you’re looking for, then check out Tiki Wiki. For me, I didn’t want to be overwhelmed and miss my goal of company documentation, but you may want to take your wiki to the next level. Tiki Wiki boasts a very long list of features including the following:

  • Themes, newsletters, banners, and blogs
  • Shopping carts, payment, membership, and accounting tools
  • Friends, surveys, polls, chats, and other social networking
  • Issue tracking and other IT Help Desk tools
  • Spreadsheet, slideshow, drawing, and other office applications
  • Quizzes, webinar integration, and other e-learning tools
  • Many other additional features

Tiki Wiki

Tiki Wiki Installation Guide

WikkaWiki – Free

wikka_logoIf you’d rather go the other direction and want something more lightweight and simple, then check out WikkaWiki. It is designed to be much easier and straight-forward.

WikkaWiki

WikkaWiki Installation Guide

Dokuwiki – Free

Doku Wiki LogoFinally, Dokuwiki is a great option for company documentation because that’s what it’s built for. It requires no back-end database and can efficiently fulfill the documentation needs of a small company.

DokuWiki

DokuWiki Installation Guide

If you have an IT person that wants to do in-depth comparisons and look at even more options, then check out WikiMatrix. There, you can compare dozens of Wiki options and find one to fit your expertise and needs.

A Couple Factors to Consider

With all company tools, it’s important to consider the side-effects and consequences of using it. Here are some things you’ll want to consider before rolling the wiki out to the whole company.

Internal or external – do you want your wiki accessible from any internet connection or only while on your company’s local network? If you want it accessible from anywhere, then you’ll need to put some security login procedures in place to keep the world out of your company’s procedures.

Users and permissions – should everyone in the company access everything? If not, then you’ll need to edit user permissions and groups.

Moderation – will anyone be responsible to moderate and monitor the wiki’s usage? If you have a larger organization, then you may need someone to keep everything somewhat neat and organized.

File storage – we often upload Word and Excel documents to our wiki so everyone can access the latest version of a file. However, since our wiki stores the file within its programming, the program can become quite large as many files are added. Will you upload files to your wiki or just add links to the file’s location on your network?

Use more than text – as explained in my article [article about visual explanations], visuals are often much more helpful than just text. Be sure to include screenshots, pictures, or even video tutorials to make your procedures easier to learn.

Although your wiki may be sparse at first, be diligent in your implementation and building a company resource. We installed ours just 18 months ago, and my teammates and I now rely on it multiple times each day. It’s allowed us to do more and maintain corporate learning as some employees have moved on. Best of all, it empowers everyone in the organization to learn and effectively do more each day.

What does your company use to share its knowledge? Could a Wiki help you stop throwing knowledge away?

Who Owns the Forecast?

As our small company has grown, my team has repeatedly asked the question, “Who owns the forecast?” When we were smaller, with just a handful of products and employees, that question didn’t matter much. We all agreed together on what we hoped to sell, and then we bought inventory in anticipation of that growth. Since we only had a few items, the decisions weren’t complicated. Purchasing too much wasn’t a problem either. We’d have overstocks, but it was only a few thousand dollars, which we could easily liquidate.

Today we face a much different situation – one in which we’re fighting hard to figure out the forecasting process. Compared to our beginnings, our customer base, order quantities, and SKU count have all increased exponentially. We’ve long passed the point at which our one purchaser could keep track of everything in her head. More precarious, however, is that overbuying has gone from a few thousand dollars of overstock to millions.
Hot Potato Forecast
We also began experiencing company misalignment as each departments moved forward with its own projection. Our salespeople set revenue goals, our controller had a year-over-year budget, and Operations had its excel sheets and run rates. All three pseudo-forecasts were vastly different. What resulted were overstocks, stock-outs, and unrealistic budgeting. We needed someone to take ownership of the forecast – but who?

Should Sales Own the Forecast?

Being in operations, my natural instinct was that Sales should own the forecast. After all, they interacted most with the customer, so they were obviously the best at translating those insights into numbers. However, the job of our small sales team was to build relationships with customers that brought in revenue. This required almost all of their time and effort; understandably, they wanted to focus on selling instead of forecasting. Sales agreed to give as much input as possible, but they needed someone else to own the forecast.

Should Operations Own the Forecast?

Our company looked to Supply Chain and Operations to own the forecast, but we hadn’t been doing a great job so far. We had workbooks upon workbooks of Excel forecasts, but it was only as good as past data could predict. Our limited MRP tool wasn’t robust enough to meet our products’ erratic patterns, so we had to manually calculate and guess every order. Because excess inventory was piling up, we tried to scale back our purchasing to preserve cash. However, this often forced us to use expensive airfreight to keep items in stock. We quickly realized that our current processes and tools wouldn’t suffice. While we could play a leading role, we couldn’t own the forecast. We could lend our analytical skills and champion the process, but without help from other departments, the forecast would continue to be unacceptably poor.

Should Finance Own the Forecast?

Even though our controller (our head accountant) wasn’t involved with purchasing or selling our product, she had to present budgets and revenue forecasts to our CEO and Board of Directors. Most of this forecasting was similar to the following:

Controller: “How do you feel about next year?”

Executive: “I see big things coming; I think we’ll grow by 25%”

Controller: “Ok, how about we budget conservatively at 15%”

Now to be fair, quite a bit more went into this analysis, but we never approached the level of detail that would make the forecast useful to Operations.

Recognizing this misalignment, our company met together to address the problem. Sales, Operations, and Finance all agreed that we needed one forecast the entire company could rely on. It needed to be as detailed as stating that, “we will sell Walmart $1.64 Million of Item X next year, and here is the week by week breakdown.” In fact, we needed that same level of detail for every item going to each significant customer. However, without structured collaboration, this level of specificity would be impossible to achieve.

The S&OP Process Owns the Forecast

Recognizing the need for collaborative ownership of the forecast, we began consulting other companies our size and researching best practices. Specifically, we learned that we must create a robust Sales and Operations (S&OP) planning process. S&OP is something that most companies are still learning, but the ones who figure it out seem to thrive. S&OP calls for structured and disciplined sharing of information to build a forecast that unites the company. Thus, everyone involved in the S&OP process becomes the owner of the forecast.

My great fear, however, was the business proverb that that reads, “When an issue becomes everyone’s responsibility, the issue becomes nobody’s responsibility.” In order to avoid the “nobody’s responsibility” syndrome that often plagues forecasting, we all sat down together to define the role each department would play in our S&OP process. Sales would provide customer insight and commitments, Operations would provide historical analytics, and Finance would ensure cash and budgets were in line with the company’s strategy. Most importantly, the CEO would make S&OP a top priority, hold each department accountable to the process, and offer support as needed.
The S&OP Process Owns the Forecast
Many articles and publications suggest S&OP process timelines, but below is what our monthly S&OP process currently looks like. We do our best to improve and tweak the process each month.

Week 1: Review and Fine-tune

At the beginning of the month, Operations reviews the accuracy of the previous month’s forecast against actual sales. Using every bit of data available, we analyze and fine-tune a rolling 12 month-forecast. For example, in May, we forecast the next month, June, through May of following year. We re-forecast every month because accuracy improves as we move closer to each month. The team does its best to serve up the recommended forecast “on a silver platter” to Sales. The goal is for the forecast deliverable to be so easy to understand and tweak that Sales can’t help but give their input.

Week 1 Deliverable: A complete 12-month rolling forecast based on history and known data points for Sales to evaluate, review, and tweak. Currently, this takes the form of a formatted spreadsheet generated by our forecasting software.

Week 2: Sales Input

During the second week of the month, Sales reviews the forecast. Specifically, they review and add customer commitments of new items going in and old items coming out of each channel. Sales incorporates upcoming events, customer feedback, and promotions to help modify what the historical data predicts. If possible, week two also includes a deep dive into our customer’s systems to review their sales and inventory levels.

Week 2 Deliverables: A completely reviewed and approved forecast from Sales, which incorporates as much customer knowledge as possible. Sales also shares feedback on how to improve the Week 1 deliverable.

Week 3: Supply Planning and Vendor Communication

Based on the Week 2 Deliverable, Operations then builds a supply plan to support the demand forecast. Operations meets with Sales about potential stock outs and capacity issues. Sales, Operations, and our CEO approve any significant issues, such as large stock outs or airfreight expense. After signing off on the significant issues, the CEO also signs off on the entire forecast. Operations locks and archives the forecast, which means everyone can now rely on the forecast to remain unchanged (except in extreme circumstances) until next month. With a locked forecast, Operations can now move forward with building a purchase order plan. From that plan, Operations sends purchase orders and forecasts to key vendors. They also schedule conference calls with those vendors to discuss and plan for the new forecast.

Week 3 Deliverables: An approved and locked demand plan, approved purchase orders, and vendor forecasts for key vendors.

Week 4: Meet, Plan, Reflect, and Improve

During week 4, we round out the process with vendor meetings and continued data analysis. We also hold a reflection meeting to review that month’s S&OP process. We ask, “What went well, what went poorly, and what can we do better?” We then make assignments that incorporate the feedback into changes for the following month. During this week, we also format the forecast and purchasing plan so that Finance can incorporate them into cash planning and budgeting.

Week 4 Deliverables: Assignments to improve next month’s S&OP process and a forecast formatted to Finance’s needs.

Results of the S&OP Process Owning the Forecast

We still have a long way to go before our S&OP process and forecasts are world-class. However, we’ve already seen fantastic results from our collaboration. Our controller absolutely loves the level of detail in the forecast, which helps her finance the rest of the company in realistic ways. Our executive team can now see their corporate strategy rolled out in a week-by-week plan. Sales is able to focus on selling, but they’re now armed with detailed analytics of each customer’s performance. This helps them sell even more. Not least of all, Operations has been able to better support customer demand while also reducing inventory. Being on the same page with the rest of the company ensures that we order what we need, and avoid ordering what we don’t need. Additionally, Operations now has a forecast for our vendors. We are now working on reducing vendor lead times with the forecast as a tool. Shorter lead times will let us hold less safety stock inventory, which will help free up even more cash.

Perhaps the best result from our S&OP process is the structured collaboration between departments. The discipline and teamwork required to build a single, detailed forecast has helped us unite and become a stronger company. We all are tied into the decision making process so there is less blame when a problem occurs. Roles are more clearly defined so we are able to work on what is expected rather than guessing what we should be doing. Best of all, we’re reducing errors and inventory, which helps us continue our growth and success.

What have you do at your company to build your S&OP process? Please share your thoughts and advice in how we can improve.

Getting Started with Big Data in a Small Business

Data Analyst Sheriff Badge
Better data leads to better decisions, especially in supply chain and operations. Unfortunately, most small businesses don’t have enough analysts (or any at all) who can comb through databases to provide data-backed recommendations. In fact, that number crunching responsibility often falls to the operations team, who may not have much experience analyzing big data. Perhaps you are in a similar position. You recognize the need for more data in your decisions, but accessing that information seems a bit daunting. If so, then here are three easy levels of data-crunching proficiency that will help you earn your data analyst sheriff badge.

Level 1 – Pivot Tables

If you need to analyze a large, pre-made table of data, then you should to start with a pivot table. Microsoft Excel’s pivot table tool is the data analyst’s faithful and reliable six-shooter. It can handle almost any problem a set of data brings your way. Pivot tables allow you to transform a set of data into an easy-to-rearrange and organized summary grid. The grid of information it produces helps you quickly find patterns, trends, and outliers. In fact, when you hear the words “slice and dice,” you should instantly think pivot tables.

Pivot Tables in Excel

Most people I’ve met who have yet to use pivot tables just don’t know how to start. The easiest way to learn is to just jump in and play around with one in Excel. You can find lots of good online tutorials and videos that will get you up to speed with pivot tables in less than 15 minutes. For example, here’s a seven-minute YouTube video that does a great job at walking through the basics: Microsoft Excel Pivot Table Tutorial for Beginners – Excel 2003, 2007, 2010

Once you feel comfortable manipulating data someone else has provided, then you can take the next step and access the data yourself.

Level 2 – Database Queries

For many small businesses, the challenge with data often lies in how to access your own database. Depending on your accounting  or ERP system, the exact technique will vary. However, you can usually access most small business systems easily through Excel’s Microsoft Query tool. For your exact system, run a web search for “how to access QuickBooks database in excel”, replacing “QuickBooks” with your own system’s name. Most systems will have an ODBC driver, which basically means it has the ability to be queried by outside programs such as Microsoft Excel.

How to Access your Database if it has an ODBC Driver

If your system uses an ODBC driver, then the query process is straightforward. In Excel, go to Data > From Other Sources > From Microsoft Query

Bringing Data Into Excel 1

A list will appear of available databases. Choose the one you want to query and press OK. Below I chose the connection for our Sage 100 database (formerly known as Sage Mas 90 or 200). If you don’t recognize the name of your database, you may need to search the web a little more.

Bringing Data Into Excel 2

Next, you’ll want to select the fields and tables you want in your report. You’ll need to pick fields from tables that are linked together in order to pull the items correctly. It may require some trial and error, as well as some research into how your database is structured.

Bringing Data Into Excel 3

After selecting the fields, the query wizard gives you a few options to filter and sort your data. However, you can access more advanced query options by manipulating your query in the Microsoft Query program. On the last page of the Query Wizard, select that option and press Finish.

Bringing Data Into Excel 4

Inside Microsoft Query, you can add additional filter criteria, table links, and column labels.

Bringing Data Into Excel 5

Once you’ve fine-tuned your query to just what you want, click the “Return Data” button (the one with an arrow pointing to a door by the “Save” button). This will bring the data you’ve selected back into Excel.

Once I learned how to access our company’s database, I began running dozens of queries each day. This helped tremendously, and I learned a great deal from the data. However, I eventually reached the limits of what Microsoft Query could offer. To go further, I had to learn a bit of Structured Query Language, or SQL (pronounced “sequel”), to get the answers I needed.

Level 3 – Advanced Queries with SQL

For some strange reason, I was more eager to learn French in high school than computer code, despite the fact that I query databases much more frequently than I ask which café has the best baguette. Luckily, SQL is very easy to learn (there’s definitely no conjugation or verb tenses).

Although Microsoft Query and Microsoft Access have great query-building interfaces, knowing a bit of SQL can empower you to do much more. SQL functions allow you to manipulate or summarize data as you pull it from the database. SQL also will allow you to make advanced links that might give you data hard to get at any other way. Accordingly, even a little SQL will quickly empower you to more efficiently and effectively get the answers you need. SQL is also the language you’ll use to connect your database with most outside SaaS modules and programs. Knowing SQL can help make implementing such add-ons much easier and cheaper.

The best part of SQL is that you can learn most of what you need to query big data in an afternoon. I recommend Sams Teach Yourself SQL in 10 Minutes. Each lesson actually takes about 10-15 minutes, and after about twenty lessons, you’ll know everything you need to build advance database queries. The book also starts at the very basics, which is great for operations people like me who don’t have a computer science degree.

No matter what the next step is, each of these levels will help you to make data more accessible for your company. With more data, you’ll quickly be making better, fact-based decisions to meet your goals and improve your supply chain.