Business OBA 2321 – Group Exercise #2 Spring Semester 2023
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
Business OBA 2321 – Group Exercise #2
Spring Semester 2023
Due on Monday April 24th, 2023 at 11:59pm
Case Study Requirements:
1. Solve the problems below and submit the answers using the provided Excel worksheet
· File Name (1%) – Save your solution as “BusOBA 2321 – GE2 Group XXX” – inserting your group number for the “XXX”
· Cover Page (3%) – add all team members names and dot numbers as indicated
· List names in alphabetical order by last name
· No more than 4 members to a group; your assigned group is available on Carmen
· If a member does not contribute, do not include their name on the cover sheet
· Problem 1 (32%) – Modify the template to reflect your program/solution
· Problem 2 (32%) - Modify the template to reflect your program/solution
· Problem 3 (32%) - Modify the template to reflect your program/solution
· Hint: Refer to how you expanded MBE 3 into MBE 4
2. Submit the worksheet via Carmen.
· 1 submission per group
· Up to 25 percentage points will be deducted for “non-professional” reports – in other words neatness and formatting count
· If the assignment is incomplete or if the grader feels a poor effort was made, the entire assignment will receive a zero
· If the assignment is submitted late all members will receive a zero
3. Solver MUST be filled out for all problems in your final submission, or you will receive a ZERO for that problem!
If you used Google Docs, Excel Online, Teams, etc to share the excel workbook with group members or if you copied a problem from another workbook into your workbook, the solver program will not transfer with the copied/shared files. You are responsible for checking your final submission to ensure that solver is properly filled out for each problem. If solver is missing or does not run for a problem, you will receive a zero for the problem.
Problem 1: Mudsock Shopping Complex
Assignment Model - Binary
The town of Mudsock has seen tremendous growth in both population and prosperity following the COVID quarantine. People have flocked to the unassuming suburb to avoid the high cost of living and other issues in the “big city”, traditional residents have swarmed to public areas and gatherings, and the town has a reputation as a good mix of progressiveness and silliness. The Mudsock council recently agreed to renovate a large shopping complex to better meet the needs of the community, and hopefully increase local revenue. The town planner, Ty Phipps, hired FET1, a local development company, to help plan and construct the complex. FET1 has extensive experience in developing community master plans and the Mudsock council asked the firm to identify the right stores and mix of services for the complex.
Reynalda Morgan, president of FET1, is tasked with assisting Ty in deciding which stores to locate in the 32,000 available square feet of the complex. Reynalda and Ty are confident that there is sufficient market interest to quickly fill the shopping center with whatever allocation plan they select. Reynalda has developed a list of 20 stores for consideration. The table below lists the stores in alphabetical order and provides required floor space, annual sales, and renovation costs for each store.
The leasing agreement, developed by FET1, requires each store to pay Mudsock the annual rent for the allotted space. Mudsock council mandates that the total rent payments each year must be sufficient to cover the annual fixed cost of maintenance of the complex. The cost, which includes security, janitorial services, maintenance, and utilities, is estimated to be $500,000 annually. The Mudsock council also specifies that the total renovation costs cannot exceed 60% of the total annual rent to be collected. FET1 proposes that Mudsock also receive a fixed percentage of total annual sales generated by the stores.
FET1 has found that a proper mix of stores will make the shopping complex attractive to a broad range of customers. Juniper Xu, the marketing coordinator for FET1, recommends that the complex include at least two units each for apparel, food, and specialty stores, and at least one unit each of housewares and service stores. Juniper also recommends no more than three stores of any one type and that the total number of restaurants and service stores should not exceed the total number of units of the other three types of stores.
Mudsock has tasked your group to identify the proper mixture of the stores that will maximize the total annual sales of the stores.
Store |
Store Type |
Area Needed (sq ft) |
Annual Rent |
Annual Sales |
Renovation Costs |
Arcade |
Service |
6000 |
$ 70,000 |
$ 1,250,000 |
$ 40,000 |
Bath & Body Products |
Specialty |
2000 |
$ 35,000 |
$ 650,000 |
$ 40,000 |
Children's Clothing |
Apparel |
4800 |
$ 70,000 |
$ 1,750,000 |
$ 35,000 |
Chinese Fast Food |
Food |
2400 |
$ 40,000 |
$ 800,000 |
$ 27,000 |
Electronics Store |
Specialty |
6400 |
$ 80,000 |
$ 1,300,000 |
$ 75,000 |
Hair Salon |
Service |
3000 |
$ 50,000 |
$ 500,000 |
$ 35,000 |
Hardware Store |
Housewares |
7200 |
$ 95,000 |
$ 1,200,000 |
$ 55,000 |
Hobby Store |
Specialty |
3600 |
$ 50,000 |
$ 550,000 |
$ 29,000 |
Ice Cream Store |
Food |
1600 |
$ 30,000 |
$ 500,000 |
$ 20,000 |
Kitchen Store |
Housewares |
6400 |
$ 80,000 |
$ 900,000 |
$ 42,000 |
Lingerie Store |
Apparel |
3600 |
$ 55,000 |
$ 1,250,000 |
$ 30,000 |
Luggage Store |
Housewares |
4000 |
$ 70,000 |
$ 650,000 |
$ 34,000 |
Men's Clothing |
Apparel |
7000 |
$ 100,000 |
$ 2,000,000 |
$ 40,000 |
Pizza Retaurant |
Food |
4800 |
$ 62,000 |
$ 1,200,000 |
$ 31,000 |
Shoe Store |
Apparel |
4800 |
$ 60,000 |
$ 1,400,000 |
$ 40,000 |
Sports Bar |
Food |
4000 |
$ 80,000 |
$ 1,300,000 |
$ 50,000 |
Tex-Mex Fast Food |
Food |
2400 |
$ 35,000 |
$ 1,100,000 |
$ 35,000 |
Toy Store |
Specialty |
4800 |
$ 70,000 |
$ 1,000,000 |
$ 50,000 |
Travel Agency |
Service |
2000 |
$ 30,000 |
$ 400,000 |
$ 15,000 |
Women's Clothing |
Apparel |
7000 |
$ 120,000 |
$ 2,250,000 |
$ 65,000 |
Problem 2: New TA Office
Goal Programming
The Great Fisher Hall Flood of 2022 damaged the bottom 2 floors of the building and made all of the TA offices on the ground floor of Fisher Hall unusable. The FCOB Executive Committee asked your Team to submit competing proposals for the furnishing the new BusOBA 2321/3230 TA offices. Your team takes measurements of the space and interview several of the TAs who will utilize the office to determine the requirements. Your team then decides to use the skills learned in BusOBA 2321 to create an integer linear program to satisfy the TA requirements while minimizing the cost to furnish the new TA office. If it's not possible to satisfy all the requirements your team will use goal programming to try and minimize the weighted percent deviation.
Below is a list of all the objects available to furnish the new TA room and a selected set of metrics about each object that will be used to assist your team’s decision making.
Object |
Cost |
Functionality Rating |
Comfort/ |
Floor Space (sq ft) |
Wall Space |
Placed on a table? |
Basic Chair |
$100.00 |
1 |
0 |
2 |
|
|
Table |
$150.00 |
1 |
0 |
3 |
|
|
Swivel Chair |
$300.00 |
2 |
1 |
2 |
|
|
Adjustable Table |
$300.00 |
2 |
1 |
3 |
|
|
White Board |
$200.00 |
2 |
1 |
2 |
3 |
|
Mini Fridge |
$150.00 |
0 |
4 |
2 |
|
|
Dog Bed |
$75.00 |
0 |
3 |
2 |
|
|
Poster |
$50.00 |
0 |
1 |
0 |
1 |
|
TV |
$300.00 |
1 |
3 |
1 |
2 |
|
Computer |
$400.00 |
1 |
1 |
0 |
|
Yes |
Printer |
$100.00 |
1 |
0 |
0 |
|
Yes |
Mr. Coffee |
$60.00 |
0 |
1 |
0 |
|
Yes |
Keurig |
$140.00 |
0 |
2 |
0 |
|
Yes |
2023-04-22