4: Decision Making and Improving Supply Chain Management (Excel Exercise) Due: March 25, 2012 Total Score: 30 Points Part A: Springville School District Student and Teacher Ratio Problem Attention: Please name the spreadsheet file using your REAL NAME for easy evaluation. Your workbook should contain 3 worksheets. The Springville School District has seven elementary schools. The following table shows the current enrollment in each elementary school and the number of teachers currently assigned to each school. The district has established a goal of ensuring that there is at least one teacher for every 20 students in each elementary school. (You can divide the number of students by the number of teachers to determine this ratio for each school.) Is it possible to achieve this goal without increasing the number of teachers in the district? 1. Build a spreadsheet to perform the ratio analysis described above. Your original data set in spreadsheet should look like this: (4 points) 2. One suggested method for achieving the district's goal is to pay transportation expenses for parents who are willing to transfer their children from overcrowded schools to schools that are less crowded. Use goal-seeking analysis on your spreadsheet to determine the number of students who would have to move so that all of the schools are below the 20 to 1 ratio. 3. A second alternative is to transfer teachers from less-crowded schools to schools that are overcrowded. Use goal-seeking analysis on your spreadsheet to determine the number of teachers who would have to transfer to each overcrowded school so that all schools are below the 20 to 1 ratio. 4. Please perform the goal seeking activities as noted in the above figure. Your table data should reflect the changes. Each time, please also use Alt + Fn + PrintScreen key combination (Alt + PrintScreen on certain keyboard) to copy the Goal Seeking Status message box with the result, and paste it to the right of the data table (5 points). 5. Please state clearly in your spreadsheet how many students to move from one school to keep the desired ratio and how many teachers to move from one school to keep the desired ratio (1 point). Part B: Supply Chain Management Problem Solving You run a company that manufactures aircraft components. You have many competitors who are trying to offer lower prices and better service to customers, and you are trying to determine if you can benefit from better supply chain management. This data file is available to you via Excel File for Hands-On 4 link. The fields in the spreadsheet file include vendor name, vendor identification number, purchaser's order number, item identification number and item description (for each item ordered from the vendor), cost per item, number of units of the item ordered, total cost of each order, vendor's accounts payable terms, promised shipping date, promised transit time, and actual arrival date for each order. Prepare a recommendation of how you can use the data in this spreadsheet database to improve your supply chain management. You may wish to consider ways to identify preferred suppliers or other ways of improving the movement and production of your products. Some criteria you might consider include the supplier's track record for on-time deliveries, suppliers offering the best accounts payable terms, and suppliers offering lower pricing when the same item can be provided by multiple suppliers. Use your spreadsheet software to prepare reports and, if appropriate, graphs to support your recommendations. This exercise requires some student knowledge of spreadsheet database functions. At a minimum, students should know how to sort the database by various criteria such as item description, item cost, vendor number, vendor, name, or A/P terms. Students may need to be told that A/P Terms is expressed as the number of days that the customer has to pay the vendor for a purchase. In other words, 30 designates net 30 days. The vendor that allows customers the longest amount of time to pay for an order would, of course, offer the most favorable payment terms. Students will need to add additional columns for calculating the actual delivery time for each order and the number of days the delivery is late. The Actual Delivery Time can be calculated by subtracting the Promised Ship Date from the Arrival Date. The number of days late can be calculated by subtracting the Promised Transit Time from the Actual Delivery Time. If the number of days late is negative, it indicates that the order arrived early. These numbers are useful when trying to determine who the vendor is with the best on-time delivery track record. Students can use the DAVERAGE function to determine the average delivery time for each vendor. Students can also use one of the database functions to determine the vendor with the best accounts payable terms. To determine the vendor with the lowest prices for the same item when it is supplied by multiple vendors, students can filter the database using the item description. This filtered list can then be sorted by item cost and vendor number. So specifically, I am expecting to see two added columns to the data table under the headings \"Actual Delivery Time\" and \"Days Late\" (2 points each). I also expect to see three reports be added to the bottom of the existing data table: Report 1 - Most On-time Suppliers with a list for vender number and a list for average days late; Report 2 - Best Accounts Payable Terms listing A/P terms for each vender; Report 3 - Comparison of Supplier Costs containing all the column headings in the existing data table (5 points each report, 1 remaining point for the professional look of your work). Springville School District Teacher Student Ratios School Number of Number of Student / Teacher Ratio Cleveland 741 43 17.23 Parkside 884 38 23.26 Powers 643 37 17.38 Lakeview 906 39 23.23 Sunnyslop 704 40 17.60 Prairieview 715 41 17.44 Mountainvi 793 40 19.83 TOTAL 5386 278 19.37 NOTE TO PERFORM GOAL SEEKING: Select Data and then Goal seek Select the target cell D4 value 20 and the parameter to change B4 in part B value of B4 becomes 760 Do the same for cell D6 using Cells B6 value of B6 becomes 780. For part C do the same changing the Number of Teachers value C4 becomes 44.2 in effect 45, C6 becomes 45.3 in effect 46 Orders and Suppliers Vendor Name Spacetime Technologies Steelpin Inc. Steelpin Inc. Steelpin Inc. Steelpin Inc. Steelpin Inc. Alum Sheeting Alum Sheeting Alum Sheeting Alum Sheeting Spacetime Technologies Spacetime Technologies Spacetime Technologies Spacetime Technologies Spacetime Technologies Spacetime Technologies Durrable Products Durrable Products Durrable Products Durrable Products Durrable Products Spacetime Technologies Spacetime Technologies Spacetime Technologies Durrable Products Durrable Products Durrable Products Manley Valve Manley Valve Manley Valve Manley Valve Spacetime Technologies Spacetime Technologies Manley Valve Manley Valve Manley Valve Manley Valve Pylon Accessories Alum Sheeting Alum Sheeting Steelpin Inc. Alum Sheeting Alum Sheeting Pylon Accessories Durrable Products Durrable Products Vendor No. Order No. Item No. 2 A0111 6489 6 A0115 5319 6 A0123 4312 6 A0204 5319 6 A0205 5677 6 A0207 4312 5 A0223 4224 5 A0433 5417 5 A0443 1243 5 A0446 5417 2 A0533 9752 2 A0555 6489 2 A0622 9752 2 A0666 5125 2 A0777 6489 2 A1222 4111 3 A1234 9399 3 A1235 9399 3 A1344 5454 3 A1345 9399 3 A1346 9399 2 A1444 4111 2 A1445 4111 2 A1449 4111 3 A1456 5454 3 A1457 4569 3 A1567 1369 7 A2345 6431 7 A2356 7258 7 A2367 9977 7 A2378 6431 2 A3467 4111 2 A5689 4111 7 A9821 6431 7 A9842 7258 7 A9865 9967 7 A9876 9955 8 A9999 6433 5 B0447 5634 5 B0247 1243 6 B0445 4312 5 B0479 5634 5 B0567 1243 8 B1111 9764 3 B1234 7258 3 B1345 7258 Item Description Item Cost Quantity O-Ring $ 3.00 900 Shielded Cable/ft. $ 1.10 17,500 Bolt-nut package $ 3.75 4,250 Shielded Cable/ft. $ 1.10 16,500 Side Panel $ 195.00 120 Bolt-nut package $ 3.75 4,200 Bolt-nut package $ 3.95 4,500 Control Panel $ 255.00 500 Airframe fasteners $ 4.25 10,000 Control Panel $ 255.00 405 Gasket $ 4.05 1,500 O-Ring $ 3.00 1,100 Gasket $ 4.05 1,550 Shielded Cable/ft. $ 1.15 15,000 O-Ring $ 3.00 1,050 Bolt-nut package $ 3.55 4,200 Gasket $ 3.65 1,250 Gasket $ 3.65 1,450 Control Panel $ 220.00 550 Gasket $ 3.65 1,470 Gasket $ 3.65 1,985 Bolt-nut package $ 3.55 4,250 Bolt-nut package $ 3.55 4,200 Bolt-nut package $ 3.55 4,600 Control Panel $ 220.00 500 Bolt-nut package $ 3.50 3,900 Airframe fasteners $ 4.20 15,000 O-Ring $ 2.85 1,250 Pressure Gauge $ 100.50 95 Panel Decal $ 1.00 525 O-Ring $ 2.85 1,350 Bolt-nut package $ 3.55 4,800 Bolt-nut package $ 3.55 4,585 O-Ring $ 2.85 1,300 Pressure Gauge $ 100.50 100 Hatch Decal $ 0.85 550 Door Decal $ 0.55 150 O-Ring $ 2.95 1,500 Side Panel $ 185.00 150 Airframe fasteners $ 4.25 9,000 Bolt-nut package $ 3.75 4,150 Side Panel $ 185.00 140 Airframe fasteners $ 4.25 10,500 Gasket $ 3.75 1,980 Pressure Gauge $ 90.00 100 Pressure Gauge $ 90.00 120 Durrable Products Durrable Products Durrable Products Fast-Tie Aerospace Fast-Tie Aerospace Fast-Tie Aerospace Fast-Tie Aerospace Fast-Tie Aerospace Fast-Tie Aerospace Fast-Tie Aerospace Fast-Tie Aerospace Fast-Tie Aerospace Fast-Tie Aerospace Fast-Tie Aerospace Fast-Tie Aerospace Steelpin Inc. Steelpin Inc. Steelpin Inc. Steelpin Inc. Steelpin Inc. Steelpin Inc. Fast-Tie Aerospace Fast-Tie Aerospace Fast-Tie Aerospace Steelpin Inc. Steelpin Inc. Steelpin Inc. Manley Valve Hulkey Fasteners Hulkey Fasteners Pylon Accessories Manley Valve Hulkey Fasteners Hulkey Fasteners Hulkey Fasteners Manley Valve Hulkey Fasteners Hulkey Fasteners Hulkey Fasteners Hulkey Fasteners Hulkey Fasteners Hulkey Fasteners Hulkey Fasteners Pylon Accessories Hulkey Fasteners Hulkey Fasteners Hulkey Fasteners Pylon Accessories 3 3 3 4 4 4 4 4 4 4 4 4 4 4 4 6 6 6 6 6 6 4 4 4 6 6 6 7 1 1 8 7 1 1 1 7 1 1 1 1 1 1 1 8 1 1 1 8 B1468 B1589 B1666 B2333 B2345 B2356 B2367 B2378 B2498 B2499 B2511 B2519 B2528 B2537 B2566 B3041 B3042 B3022 B3111 B3222 B3333 C0423 C0234 C0433 C0456 C0467 C0589 C1111 C1212 C1313 C2211 C2222 C2323 C2929 C3232 C3333 C3434 C4545 C5656 C6765 C7875 C8854 C8989 D1111 D1212 D2121 D3232 D3333 1369 5275 1369 6321 6321 6321 6321 6321 5689 7268 7268 5462 5689 5462 5462 5234 5234 5677 5234 8008 8008 5689 5166 5462 5677 8008 5319 9955 1122 3166 9764 7258 1122 3166 1122 8148 1122 1122 1122 5066 3166 3166 9966 9764 5066 1122 1122 9764 Airframe fasteners Shielded Cable/ft. Airframe fasteners O-Ring O-Ring O-Ring O-Ring O-Ring Side Panel Pressure Gauge Pressure Gauge Shielded Cable/ft. Side Panel Shielded Cable/ft. Shielded Cable/ft. Electrical Connecto Electrical Connecto Side Panel Electrical Connecto Machined Valve Machined Valve Side Panel Electrical Connecto Shielded Cable/ft. Side Panel Machined Valve Shielded Cable/ft. Door Decal Airframe fasteners Electrical Connecto Gasket Pressure Gauge Airframe fasteners Electrical Connecto Airframe fasteners Machined Valve Airframe fasteners Airframe fasteners Airframe fasteners Shielded Cable/ft. Electrical Connecto Electrical Connecto Hatch Decal Gasket Shielded Cable/ft. Airframe fasteners Airframe fasteners Gasket $ 4.20 $ 1.00 $ 4.20 $ 2.45 $ 2.45 $ 2.45 $ 2.45 $ 2.45 $ 175.00 $ 95.00 $ 95.00 $ 1.05 $ 175.00 $ 1.05 $ 1.05 $ 1.65 $ 1.65 $ 195.00 $ 1.65 $ 645.00 $ 645.00 $ 175.00 $ 1.25 $ 1.05 $ 195.00 $ 645.00 $ 1.10 $ 0.55 $ 4.25 $ 1.25 $ 3.75 $ 100.50 $ 4.25 $ 1.25 $ 4.25 $ 655.50 $ 4.25 $ 4.25 $ 4.25 $ 0.95 $ 1.25 $ 1.25 $ 0.75 $ 3.75 $ 0.95 $ 4.25 $ 4.25 $ 3.75 14,000 25,000 10,000 1,300 1,200 2,500 1,250 1,500 150 110 105 22,500 175 21,500 23,000 4,500 4,750 110 4,850 150 100 155 5,650 22,500 130 120 18,100 125 19,500 5,600 1,850 90 15,500 5,500 18,000 125 12,500 15,000 14,500 25,000 5,650 5,425 500 1,800 17,500 17,500 17,000 1,750 Cost per order A/P Terms Ship Date Transit Time Arrival Date $ 2,700.00 25 10/10/04 5 10/18/04 $ 19,250.00 30 08/20/04 11 08/31/04 $ 15,937.50 30 08/25/04 5 09/01/04 $ 18,150.00 30 09/15/04 15 10/05/04 $ 23,400.00 30 11/02/04 8 11/13/04 $ 15,750.00 30 09/01/04 6 09/10/04 $ 17,775.00 30 10/15/04 5 10/20/04 $ 127,500.00 30 10/20/04 5 10/27/04 $ 42,500.00 30 08/08/04 5 08/14/04 $ 103,275.00 30 09/01/04 6 09/10/04 $ 6,075.00 25 09/20/04 5 09/25/04 $ 3,300.00 25 10/05/04 5 10/10/04 $ 6,277.50 25 09/25/04 5 10/05/04 $ 17,250.00 25 10/01/04 10 10/15/04 $ 3,150.00 25 10/29/04 5 11/10/04 $ 14,910.00 25 09/15/04 20 10/15/04 $ 4,562.50 45 10/01/04 5 10/06/04 $ 5,292.50 45 10/03/04 5 10/08/04 $ 121,000.00 45 10/09/04 6 10/14/04 $ 5,365.50 45 10/07/04 5 10/12/04 $ 7,245.25 45 10/05/04 5 10/11/04 $ 15,087.50 25 09/20/04 15 10/10/04 $ 14,910.00 25 09/25/04 25 10/25/04 $ 16,330.00 25 10/05/04 15 10/19/04 $ 110,000.00 45 10/15/04 5 10/20/04 $ 13,650.00 45 10/05/04 5 10/10/04 $ 63,000.00 45 09/25/04 5 09/30/04 $ 3,562.50 30 10/05/04 5 10/10/04 $ 9,547.50 30 10/20/04 8 10/29/04 $ 525.00 30 11/01/04 5 11/07/04 $ 3,847.50 30 10/01/04 5 10/07/04 $ 17,040.00 25 09/05/04 10 09/20/04 $ 16,276.75 25 09/10/04 15 09/30/04 $ 3,705.00 30 09/25/04 5 10/01/04 $ 10,050.00 30 10/15/04 8 10/24/04 $ 467.50 30 11/05/04 6 11/11/04 $ 82.50 30 11/01/04 5 11/06/04 $ 4,425.00 15 10/01/04 5 10/10/04 $ 27,750.00 30 10/25/04 5 11/03/04 $ 38,250.00 30 09/05/04 5 09/12/04 $ 15,562.50 30 09/03/04 5 09/11/04 $ 25,900.00 30 10/29/04 5 11/04/04 $ 44,625.00 30 10/10/04 6 10/17/04 $ 7,425.00 15 09/20/04 6 09/29/04 $ 9,000.00 45 08/25/04 4 08/28/04 $ 10,800.00 45 09/05/04 4 09/09/04 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 58,800.00 25,000.00 42,000.00 3,185.00 2,940.00 6,125.00 3,062.50 3,675.00 26,250.00 10,450.00 9,975.00 23,625.00 30,625.00 22,575.00 24,150.00 7,425.00 7,837.50 21,450.00 8,002.50 96,750.00 64,500.00 27,125.00 7,062.50 23,625.00 25,350.00 77,400.00 19,910.00 68.75 82,875.00 7,000.00 6,937.50 9,045.00 65,875.00 6,875.00 76,500.00 81,937.50 53,125.00 63,750.00 61,625.00 23,750.00 7,062.50 6,781.25 375.00 6,750.00 16,625.00 74,375.00 72,250.00 6,562.50 45 45 45 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 15 30 30 30 30 30 30 30 30 30 30 30 30 15 30 30 30 15 09/27/04 10/25/04 09/29/04 08/25/04 09/12/04 09/25/04 10/12/04 10/25/04 11/01/04 11/01/04 11/05/04 08/20/04 11/05/04 08/15/04 08/10/04 08/28/04 09/05/04 11/05/04 09/02/04 10/15/04 10/10/04 10/25/04 10/01/04 08/25/04 10/28/04 10/28/04 08/25/04 11/05/04 08/05/04 08/25/04 09/25/04 10/10/04 09/04/04 09/01/04 10/01/04 10/10/04 09/05/04 09/08/04 09/28/04 09/05/04 09/05/04 09/10/04 08/25/04 09/28/04 09/15/04 10/25/04 10/11/04 09/20/04 6 4 5 10 9 9 8 8 7 10 10 5 7 5 5 5 5 9 6 7 8 8 5 6 8 7 10 5 7 5 6 7 8 5 7 8 7 7 7 8 5 5 6 5 8 8 8 5 10/03/04 10/30/04 10/04/04 09/04/04 09/23/04 10/04/04 10/21/04 11/02/04 11/09/04 11/12/04 11/16/04 08/26/04 11/15/04 08/22/04 08/15/04 09/05/04 09/13/04 11/17/04 09/11/04 10/26/04 10/21/04 11/03/04 10/06/04 09/02/04 11/07/04 11/04/04 09/05/04 11/10/04 08/13/04 08/29/04 10/05/04 10/17/04 09/12/04 09/06/04 10/08/04 10/17/04 09/11/04 09/15/04 10/03/04 09/12/04 09/10/04 09/15/04 08/31/04 10/05/04 09/22/04 11/03/04 10/19/04 09/25/04 Assignment 3: Decision Making and Supply Chain Management (Excel Exercise) (Help File) Part A: Springville School District Student and Teacher Ratio Problem 1. To solve this problem in Excel 2007, I will first make a copy of the original data table and paste it to an open space on a spreadsheet. 2. Activate Data to see Data Tools; 3. In What-If Analysis, pick Goal Seek; 4. Select the target ratio cell for Parkside School (D4), the cell address information automatically gets into the Goal Seek box; 5. Type 20 into To value box; 6. Place the cursor into the By changing cell box and select cell B4. Now you have all the needed values for using Goal Seek; 7. To leave some evidence to the instructor, I use the key combination Alt+Fn+PrintScreen to copy the active Goal Seek window box; 8. Click the OK button to see the changes to the Parkside in the original data table; 9. Paste the copy of the Goal Seek window box into an open space; 10. Let's follow the same procedures to make changes to the cells for Lakeview. 11. Now we will adopt the approach of changing the number of teachers to keep the desired ratio. 12. First make a copy of the original data table and paste it to an open space; 13. Follow the same procedures of using Goal Seek, but select the cell for Number of Teachers instead; 14. Use the key combination Alt+Fn+PrintScreen to copy the active Goal Seek window box; 15. Execute to see the effect and paste the image to an open space; 16. Repeat those steps to Lakeview cells. 17. Find an open space to indicate how many students to move from Parkside or Lakeview and how many teachers to move from each school to keep the desired ratio. 18. Change your work sheet name to Ratios. Part B: Supply Chain Management Tasks You will create three table reports in this part for SCM purposes. Report 1: Most On-time Suppliers 1. To find out most on-time suppliers, I use the average days late as the index. The vendor who gets the smallest value on this index should be considered the most on-time supplier. 2. Create the report title Report 1: Most On-time Suppliers; 3. Under this title, there are two table columns, Vendor No. and Average Days Late; 4. Obviously the first column name comes from the field name in the original data source table. From the source table we notice that there are eight vendor numbers. We therefore key in 1 underneath the column heading. 5. Select the column heading and the first cell under it, and use the autofill function to fill this column with the eight vendor numbers. You may notice that the column heading is repeated several times. Since it does not interfere with our work, simply leave them as they are. 6. In the first cell underneath the column heading Average Days Late, we will use a database function DAVERAGE() to generate the value. This function helps to identify the exact value in a source table based on given criteria. 7. Please activate the menu Formulas and click the Insert Function icon to see the Function Arguments dialog box; 8. In the database box, you need to select the entire source table area including the table headings and use $ to lock up the data range ($A$4:$N$98 in my case); 9. In the Field box, either type 14 or Day Late. According to the system default, column N is recognized as the 14th data column. You may also use the column header in the source table to refer back to any value in the column. However, the spelling must be exactly the same as in the header. 10. For Criteria, select the first two cells in the first data column in Report 1 table. This way Excel will calculate the average of the days late values for Vendor No. 1 in the source table and display this average value next to 1 in Report 1 table. 11. The correct value should be -0.3. Once you get this value, you are ready to use autofill function to fill up the rest of the cells in column with needed values. 12. Save the changes and get ready to create Report 2. Report 2: Best Accounts Payable Terms 1. Copy the column header A/P Terms from the source table and paste it into a cell under the table title and make sure it is on the same line with the column headings in Report 1; 2. Select the cell underneath the table heading; 3. While Formulas menu is still active, click the icon Lookup & Reference to see a function list; 4. Pick LOOKUP from the list. This function will look up an A/P terms value for a specific vendor in the source data table. 5. Since LOOKUP() function requires that values in the lookup vector be placed in ascending order, please activate Data menu to sort data. 6. Select the entire data area in the source table; 7. Click Sort icon button to see the dialog box; 8. Make sure you sort by Vendor No., sort on Values, and sort in order of Smallest to Largest; 9. Delete unneeded levels and click OK. 10. Now, you are ready to use LOOKUP formula. 11. Activate Formula menu and pick LOOKUP from the list; 12. Click OK to accept the default; 13. In the dialog box, get your blinking cursor in the Lookup_value box; 14. Select the cell that contains the value 1 in the Vendor No. column of Report 1; 15. Get your blinking cursor in the Lookup_vector box; 16. Select the entire data range of Vendor No. column in the source table; 17. Get your blinking cursor in the Result_vector box; 18. Select the entire data range of A/P Terms column in the source table; 19. Use $ to lock up both data ranges; 20. Click OK to see the result. 30 should be displayed for Vendor No. 1. 21. Use autofill function to fill up the rest of the column with values; 22. Delete all the #N/A codes and you will see all the neatly listed A/P terms values. 23. As an option, you can use a pivot table to get the same results. Report 3: Comparison of Supplier Costs 1. 2. 3. 4. 5. 6. Create the table title; Copy and paste the source table including all the column headers below; Activate Data menu; Leave your cursor anywhere in the table; Click Filter button to see all the handles; To make sure that this table will show clearly a vendor who provides an item at the lowest price, we will use Check All for filter first; 7. Then, click on Sort icon button to sort this data table by Items Cost and Vendor No. 8. You will have to add a sorting level first; 9. Sort this table by Item Cost using Smallest to Largest order; 10. Then sort by Vendor No. using the same order; 11. Click OK to see the result. 12. If you click on the down arrow on Item Description, you will see the default Check All is on. 13. If you take off the check mark in front of Check All and click to add a check mark in front of any item description, you will see only the records with the selected description. 14. Please place a check mark in front of Bolt-nut Package; 15. Click OK to see the filtered table. You can see the vendor who provides Bolt-nut Package at the lowest Item Cost immediately. *********************************