This project focuses on using introductory shipping cost analysis using the following Excel tools:

• What If analysis using Scenario Manager and Data Tables • VLOOKUP • Statistical Functions 1. Individual or Team Approach – You can work on this project individually or in teams of two (2) people. If you are working with another person on this project – please note both names of your team with your submission to Desire2Learn.

2. Necessary Files a. Download and use the following file from Desire2Learn i. MBA668AS17 Project 1.xls b. Rename your file with your name or identifying your team, following the format from the class syllabus.

3. Submitting your Project – Submit your project files to Desire2Learn drop box Project 1 – DUE THURSDAY FEB 9TH 11:59PM.

Instructions

In the accompanying Excel file, there are already tabs and target cells for calculations set up where possible. In some cases, e.g., creating Scenario Manager output, you will need to create a new worksheet. Follow the instructions below to generate your answers in the target cells or in new worksheets as needed.

1. Base Function Tab (10 pts) – This Excel worksheet provides the base equation that will be used in answering the Scenario Manager and Data Tables questions below. In this worksheet create the equations for (highlighted yellow boxes). There is an explanation of each equation to the right of the target cell. a. Trip Cost (Total) b. Cost Per Mile

Scenario Manager (20 pts) – With this tab, perform the following: a. Copy the equations and formatting from the Base Function worksheet to this worksheet (Scenario Manager). b. Make sure the colors, borders, and equations for Trip Cost and Cost per Mile match exactly what you generated in the Base Function worksheet.

c. Using Scenario Manager, create the following scenarios using the corresponding data below:

Scenario
Number of Miles
Product A Qty
Product B Qty
Product C Qty Best Case 1500 400 500 600 Most Likely 1000 300 400 500 Worst Case 500 200 300 400 d. Generate a Summary Report in a separate worksheet selecting the following: i. Scenario Summary ii. Result Cell is equal to the cell corresponding to Trip Cost (Total). iii. Label the resulting worksheet Scenario Summary 1

e. Go back to the Scenario Manager worksheet. Change the Shipping Unit Costs to the following:

f. Generate a Summary Report in a separate worksheet selecting the following: i. Scenario Summary ii. Result Cell is equal to the cell corresponding to Trip Cost (Total). iii. Label the resulting worksheet Scenario Summary 2

3. Data Table (20 pts) – With this tab, perform the following: a. Using the equations and formatting from the Base Function that already exist on this worksheet, create a One Variable Data Table in the area highlighted in Yellow under the label ONE VARIABLE DATA TABLE. i. The single variable in the first column will be number of miles from 100 through 2,000 miles. ii. The output will be the Trip Cost in the second column. Use the What If/Data Table function to generate the table.

b. Create a Two Variable Data Table in the area highlighted in Yellow under the label TWO VARIABLE DATA TABLE. i. The first variable in the first column will be number of miles from 100 through 2,000 miles. ii. The second variable that will be in the second through fourth columns will be the quantity of Product A. For this, use quantities of 500, 800, and 1000.
Canisius College Page 3 of 3 MBA668A – Spring 2017 – Project 1 iii. The output will be the Trip Cost in the second column. Use the What If/Data Table function to generate the table.

4. VLOOKUP Tables ( 20 pts) a. For each of the columns highlighted in Yellow, using the VLOOKUP function to obtain the corresponding data from 4A VLOOKUP TABLES, and fill in the columns on the 4 VLOOKUP worksheet. A sample is provided for the first row in the “Car Make” column. This includes using VLOOKUP to fill in the following columns: i. Make, Model, Package, MPG, Monthly Fuel Cost ii. Dealer Name, Dealer City, Dealer State b. If the dealer is in New York state, Rebate Program = “YES”, otherwise Rebate Program = “NO”

5. Statistical Functions (30 pts) – In this analysis, you are provided data that has been logged for 360 trips between a shipping point in Buffalo, New York and one in Newark, New Jersey. Using this data determine the following: a. There are only two locations, but in the Ship From column you will see they are labelled differently. Revise the entries in the Ship From column so that there are only “Buffalo” and “Newark” entries. Feel free to create any additional worksheets to perform this consolidation. b. Using the statistical functions covered in class, calculate the Maximum, Minimum, Average, and Median for the Mileage and Weight. Show your calculations and data in the yellow highlighted boxes. You can use the straight application of the statistical functions to the cells, as well as Pivot Tables/changing Field Settings to generate your answers. c. With these results, can you make any general observations regarding the mileage or weight shipped from Buffalo to Newark, and from Newark to Buffalo? Summarize your findings in 2-3 sentences.
Uploaded additional materials:

Category: Essays