visual bacic

ME209

1

California State University, Northridge

Mechanical Engineering Department

Project #1

Credit: this project was provided by Dr. Robert Ryan.

Problem Statement:

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

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

2

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:

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:

ME209

3

 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?