engineering excel proj1

Project 1: Distribution Center Location Credit: this project was provided by Dr. Robert Ryan.

Overview

You are required to create an Excel workbook, supplemented with well-structured VBA

procedures, which will allow the user to choose an optimum location for a distribution center. The

distribution center must service six customers in a 30 x 30-mile area. The goal is to minimize the

delivery costs per week by finding the optimum location for the distribution center. The locations

of the customers (denoted by xi and yi) and the volume delivered to each customer per week (Vi)

are given in Table I. The customer locations are shown graphically in Figure 1.

Table I: Customer Data

Customer x location (miles) y location (miles) Volume (tons/week)

1 1 28 3

2 7 18 7

3 8 16 4

4 17 2 5

5 22 10 2

6 27 8 6

Figure 1: Customer Locations

Assuming that the distribution center is located at (X,Y), then the distance between the center

and the ith customer located at (xi,yi) is:

i i i d X x Y y   

The corresponding cost per week for each customer (Ci) is a function of the distance and the

weekly volume:

0

5

10

15

20

25

30

0 10 20 30

Y (

M il

e s

N o

rt h

o f

O ri

g in

)

X (Miles East of Origin)

CUSTOMER LOCATIONS

ME209 Dr. Youssef

CSUN-Mechanical Engineering Department © 2014 Dr. George H Youssef

2

1

2 i i i

C d V

The total weekly cost is:

6

1

i

i

Cost C 

 

The recommended approach is a “brute force” technique; in other words design the program to test

every possible location (within a reasonable precision, which is chosen by the user) and calculate

the corresponding cost. By tracking the minimum cost as the program cycles through all of the

locations, the optimum can be found.

Specifically, your program should be able to do the following three tasks.

 Identify the optimum X and Y location of the distribution center to the nearest half mile, and the corresponding weekly cost. (The precision of the location should be selectable by

the user, but we will use a half mile as our nominal precision).

 To check the sensitivity of the cost to the location, the program should also be able to output the cost at the four adjacent locations to the optimum.

 Generate a 3-D plot showing the overall relationship between cost and location. This will require that the results are output to the worksheet in the “proper” format for creating a

“surface chart”.

Assignment Requirements

You will be required to submit a report which contains the following items:

 A hard copy of the workbook showing the input and output of your program. The output must include the information asked for in the previous section.

 A hard copy of the VBA code used to generate your results.  A written description using the following format:  Introduction: Give a statement of the problem, list the key input and output variables, and

describe your solution approach. Include a list of VBA procedures that you used with a

short description of each.

 User’s Manual: Describe how to use your workbook to locate the optimum distribution center location. This description should be sufficient to enable a freshman engineering

student to open and execute your workbook, and interpret the results.

 Sample Results: Describe the results obtained. Briefly discuss the sensitivity of the cost to the center location. Are the results consistent with your intuition?