QUESTION

# Assignment 3: Decision Making and Supply Chain Management (Excel Exercise) (Help File) Part A: Springville School District Student and Teacher Ratio...

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

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. Create the table title; 2. Copy and paste the source table including all the column headers below; 3. Activate Data menu; 4. Leave your cursor anywhere in the table; 5. Click Filter button to see all the handles; 6. 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.