In this homework you will use Excel's Pivot Table feature to analyze your company's supply chain.
Business Problem: You need to understand your company's most favorable and unfavorable suppliers. Which suppliers give us the best Accounts Payable terms? To which companies do we have the most risk? Which companies supply our goods the fastest?
People Component: You are an information worker, perhaps an analyst at your company.
Organization Component: You work within the Supply Chain Auditing team which is part of the Accounting Department. Your job is to understand the components of your company's supply chain and to identify inefficiencies and opportunities to improve your company's bottom line. Your manager needs this report by 1:00 PM. It's now Noon and you were just getting ready to go to lunch.
Technology Component: The technology you will use is the Pivot Table feature of Excel. Pivot Tables are by far the de facto standard analysis tool used in business today.
Input: You have just received a data extract file from your IT Department. This data extract is in the form of a .CSV file and consists of information from your inventory and vendor management systems. This CSV file contains roughly 100 records. It is kept small for learning purposes, but keep in mind that this file could easily contain 100,000, 1 million records, or more.
Output: Your manager has just ask you to perform the following analysis. Your job is to answer the following three business questions with a report in the form of Pivot Tables within an Excel spreadsheet.
Each report will be a separate TAB in your spreadsheet. This can be accomplished by creating 3 separate pivot tables from the source data. The reports are to be professional, with proper headings, sorted correctly so as to understand the "picture" at a glance.
To what supplier do we have the most and least business exposure? Generate a report explaining which suppliers we have the most (and least) business exposure to in terms of total dollars of business.
Be sure to include the supplier name, the total number of orders from the supplier, the total number of items received from this supplier, the sum cost of all orders in our system in whole dollar currency format (no decimal places), as well as the average cost per order, also in whole dollar currency format. Sort the report in descending order by the cost of all orders. Format grand totals in the proper, corresponding format as well.
Which supplier provides us with the most favorable Accounts Payable terms? That is, which supplier allows us the most (and least) amount of time (in days) to pay them? To answer this question, you will generate another report, sorted in descending order of the average A/P terms that includes, of course, the Supplier name. A/P terms is to be in numeric format with no decimal places. Rename the "Grand Total" to "Average A/P".
Who is our fastest supplier? Speed matters! Which of our suppliers provides products the fastest (and slowest)? This requires a report, sorted in ascending order by the average DELIVERY DAYS.
However, there is an extra challenge with this report. IT did not supply the number of days it takes to receive an order! So, being a good information worker, you have to calculate it yourself. Include minimum days to deliver, as well as maximum days to deliver for each supplier. All columns to be in numeric order with no decimal places.
Submission Requirements
Spreadsheet should have 4 tabs. One for the input data and one tab for each of the three questions
3 pages. One for each question.
Each page is to include the question title