Reference no: EM132221683

Data Analysis and Decision Modelling Assignment -

Background Information - South Hill Mall is in Puyallup, a suburb of Seattle in Washington State. You will need to put in a new interior to the mall and choose which mix of shops should be built. So, it's basically a product mix problem, except instead of products, it will be shops.

The objective is to select the tenants for the mall that will provide the highest total present value for Rodney Development Company (RDC) given the following scenarios.

Janet Rodney, president of the RDC, was trying to decide what types of stores to include in her new shopping centre at Puyallup Mall. She had already contracted for a supermarket, a drugstore, and a few other stores that she considered essential. However, she had available 16,000 square feet of floor space yet to allocate. She drew up a list of the 15 types of stores she might consider (see the Table), including the floor space required by each. RDC did not think she would have any trouble finding occupants for any type of store.

The lease agreements RDC used in her developments included two types of payment. The store had to pay a certain annual rent, depending on the size and type of store. In addition, RDC was to receive a small percentage of the store's sales if the sales exceeded a specified minimum amount. The amount of annual rent from each store is shown in the second column of the table. To estimate the profitability of each type of store, RDC calculated the present value of all future rent and sales percentage payments. These are given in the third column. RDC wants to achieve the highest total present value over the set of stores she selects. However, she could not simply pick those stores with the highest present values, for there were several restrictions. The first, of course, was that she has available only 16,000 square feet.

In addition, a condition on the financing of the project required that the total annual rent should be at least as much as the annual fixed costs (taxes, management fees, debt service, and so forth). These annual costs were $130,000 for this part of the project. Finally, the total funds available for construction of this part of the project were $700,000 and each type of store required different construction costs, depending on the size and type of store (fourth column in the table).

Moreover, RDC had certain requirements in terms of the mix of stores she considered best. She wanted at least one store from each of the clothing, hardgoods, and miscellaneous groups, and at least two from the restaurant category. She wanted no more than two from the clothing group. Furthermore, she did not want the number of stores in the miscellaneous group to exceed the total number of stores in the clothing and hardgoods groups combined.

Required -

This assessment activity involves individual students developing a model to represent a business decision and implementing that model in EXCEL. A brief report recommending a solution to the problem must also be written by each student. Each student will submit an EXCEL spreadsheet, (which include multiple worksheets), to satisfy the requirements of the assignment.

The purpose of the assignment is to introduce students to the decision modelling life cycle. Students will be required to:

• Scope a decision situation;
• Identify the design criteria of outputs; calculations and processing; and, input variables and constants;
• Implement a model for the problem using EXCEL;
• Interpret the model output and make a recommendation on the basis of that output.

Expected Output -

1. The mathematical modelling of the business situation.

2. An EXCEL model implementation.

a. Discuss Implementation of the specification

b. Based on what the sensitivity report says, copy the original problem, and make some changes that you think might improve the solution. This could include:

• Changing a constraint if it's a bottleneck, such as a bigger budget for construction costs, a bigger floor space, lifting the maximum number of some sort of shop, or whatever you think might work.
• Add a completely new kind of shop, e.g., Internet café, Apple iStore, mobile phone shop (just make up the size / rent / value / cost numbers but make a realistic estimate).

3. A Report making a conclusion and recommendation to management on the basis of the model output. Prepare a brief report interpreting the model output/s and making a recommendation to management based on this interpretation. This report is to be included as an additional worksheet in the EXCEL model.

Attachment:- Assignment Files.rar