Explore BrainMass

eClickTik.com Server Problem - Spreadsheet Modeling

The Koan Solutions Server Study

Study of eClickTik.com Server Problem
Prepared by Koan Solutions Consultants
Per your request, we have studied the situation regarding the potential server locations. This report details the data we have gathered and recommends four server locations in addition to the current location in New York City.
We have collected and analyzed data on potential server locations in the United States, as well as historical information from public and private resources regarding server capacities, information channel capacity, rates of congestion, and customer switching ratios. Based on these analyses, we have selected the following sites as possible server locations in addition to eClickTik.com's current server location in New York City: Atlanta, Georgia; Des Moines, Iowa; Irving, Texas; and Seattle, Washington.
Table 1 shows the loss rates?in lost customers per million hits?determined from our data analysis. Also included are estimates of the annual cost of maintaining the servers for each location and server capacities.
Table 1. eClickTik.com Loss Rates: Lost Customers Per Million Hits
Server Location
Region Seattle Irving Atlanta Des Moines New York
South 2.603 3.681 2.9748 4.090 2.603
West 0.744 1.116 1.1751 1.145 1.473
East 1.178 1.153 8.677 1.029 0.892
Midwest 1.050 1.299 1.2206 0.787 0.919
Server Cost
(Annually) $1,000,000 $1,650,000 $1,200,000 $1,250,000 $1,300,000
Connected) 36,000 60,000 40,000 42,000 50,000
Table 2 provides customer demand information in two forms: customers connected (the average instantaneous volume of customers on the eClickTik.com website) and customer hits per year (total hits on the website per year). Both sets of figures are based on current and forecasted levels of customer demand in the U.S. regions served.
Table 2. Customer Demand
Region Customers Connected Hits Per Year
South 36,848 110,543,550
West 25,652 76,955,520
East 40,643 121,928,110
Midwest 25,192 75,575,710

User Abandonment Report

User Abandonment Report
Prepared by the Marketing Division of eClickTik.com
The results of this report were based on
a review of historical records of the profits generated by our customers
the future earnings potential of our funds
the future growth of our company
the potential lifetime of our customers
our policy of keeping customers for life
We have determined that losing a customer potentially costs our company, in lost profit, the following amounts:
Region Dollars Lost per Customer
South $5,000
West $7,000
East $4,000
Midwest $3,300
Koan Solutions, the consulting firm assisting eClickTik.com with the server configuration project, has developed the following method of calculating the dollar loss rate (DLR) per customer connected. The formula is as follows:

where L is the loss rate, or lost customers per hit; H is customer hits per year; DL is dollars lost per lost customer; and C is number of customers connected.
Refer to the Koan Solutions Server Study for data on customer loss rates. The dollars lost per customer (DL) are shown in the table above.
Because the loss rate (L) is based on the region and the server location, the results for the dollar loss rate (DLR) will also be designated by region and by server location as shown in the table below.
User Abandonment Costs ($ per Customer)
Region Seattle Irving Atlanta Des Moines New York City
South 39.045 55.221 44.623 61.356 39.045
West 15.618 23.427 24.676 24.052 30.924
East 14.131 13.833 10.412 12.346 10.709
Midwest 10.394 12.863 12.084 7.796 9.095

Server Location Map

Server Configuraton Background

To: managingdirector@eclicktik.com <Managing Director>
From: yang@eclicktik.com <Rich Yang>
Subject: Server Configuration Background
Gilda asked me to send you some information that might help you solve the server configuration problem.
As you saw in the Koan Solutions Server Study, our server is currently located in New York City, and the sites proposed for potential new servers are Atlanta, Georgia; Des Moines, Iowa; Irving, Texas; and Seattle, Washington.
We have to consider the annual costs and capacity of each potential server and find an optimal configuration to accommodate the expected rates of customer traffic for each of our U.S. regions. We certainly don't want to lose customers and revenues, so the servers should be configured properly to avoid this outcome.
We want to achieve the fastest possible speeds for our customer transactions, but keep in mind that our goal is to minimize costs. Two separate key factors determine the Internet transaction speed: the capacity of the server that hosts a website a customer is looking for and the capacity of the "pipe" through which a request to reach the network travels. If the server capacity is utilized properly, as we are trying to do, the server?or servers?should not at all affect transmission times for users.
The pipe capacity, however, can cause congestion on the Internet. Depending on the width of pipe available from a local service provider, heavy traffic on these pipes can cause network delays. Physical connectivity capabilities in different regions can also affect transmission times. For instance, because Chicago's infrastructure and phone lines are much older than those in western Texas, Chicago's connection speeds are probably slower than those in western Texas. Fortunately for us, server-hosting companies have sophisticated ways of dealing with this problem.
For example, when many customers try to connect to our site, packets of data containing these requests will burrow paths through various gateway computers until they reach an Internet exchange point or backbone, such as UUNet, PSiNet, or Qwest Communications. These Internet exchange points are most heavily clustered in the Washington D.C., New York City, and San Francisco Bay areas. These data packets are sent through our dedicated data centers, which use routing and switching software to send thousands of packets each second to our network address on our server in the fewest possible "hops" through other gateway points and at the fastest possible speed. For this reason, the closer our servers are to these Internet exchange points, the faster the connection will be made.
I hope this "view of the Internet from 50,000 feet" gives you a little guidance in making your decision. Let me know if I can offer any other help.


Solution Preview

Please see the attached file.

Background and context of the situation:

The eclicktik.com is currently facing capacity problem for its server. Currently, the server is located in New York City. It want to expand the capacity to handle the traffic and have identified Atlanta, Georgia; Des Moines, Iowa; Irving, Texas; and Seattle, Washington as the possible locations for the server. The data regarding the cost of installing the server at these locations and maintenance cost is also collected. The problem here is that the loss rate for different region and server location combinations are different. Thus, we need to take into consideration this loss while calculating the total cost to the company. Second, we need to decide on the user routing matrix. How the traffic should be routed. Thus, the problem at hand is to first decide where the servers should be installed and second how the traffic should be routed.


Based on the analysis and results of the decision model used to solve this problem, the following decisions are recommended. These decisions would result in meeting all the requirements of the eclicktik.com while minimizing the costs.

The new servers should be located in Atlanta, Georgia and Des Moines, Iowa. Thus, there will be two new servers and one existing server at New York City.

The routing matrix will be as follows:
User Routing Matrix (percentages)
Seattle Irving Atlanta Des Moines NYC Users Routed
Regions South 0% 0% 0% 0% 100% 100%
West 0% 0% 34% 66% 0% 100%
North East 0% 0% 77% 0% 23% 100%
Midwest 0% 0% 0% 100% 0% 100%

The total cost to the eclicktick with this decision (including the ...

Solution Summary

A comprehensive problem to understand decision modeling using spreadsheet. The problem is well suited to be solved using the linear programming model. Using a prescriptive linear programming spreadsheet model, Excel Add-in Solver was used to solve the problem. Going through the spreadsheet will help the students to learn how to solve such problems in future. The write up provides step-by step logic for taking the particular route for modeling and solving the capacity problem eclicktik.com is facing. The write up provides background and context of the situation, recommendations based on the outcome of the decision analysis, overview of the methodology, and technical section where the mathematical equations for objective function and constraints are given. The solution include write up of 1063 words and model in spreadsheet.