Question

Hudson Group is a one of the largest and most recognizable travel retailers in North America. we own and manage over 1,000 duty-paid and duty-free stores in 89 locations, including airports, commuter terminals, hotels and some of the most visited landmarks and tourist destinations in the world.
In 2019 we initiated the Hudson Next Project, one of the key pillars being the completion of design and implementation of four new brands within the current Business Operating Model.
These brands include: Speciality stores, Newsstands, Book stores & Brook stone stores
The new Specitity stores will be based out of the LAX airport. They will cost approximately $19 million to contruct and will require approximately 50 employees to operate. The Newstands, located in Newark, New Jersey, will be based out of the airport - less than 15 miles outside of New York City, will cost $6.5 million to construct and 20 employees to operate. The bookstores, located in Houston, will require $8 million to construct and 15 employees to operate. Located in the suburbs of Pittsburgh, Pennsylvania, the new Brookstones stores will cost $12 million to construct and 50 employees to operate across all stores.
Hudson Group will pledge 75.5 million in new construction and hire no more than 260 employees. Annually, Specialty stores are a 9.5 million operation, Newstands are a $2.4 million operation, bookstores are a 1.2 million operation and the new Brookstones stores net 3.3 million in volume and growing.
If Hudson Group wasnts to maximize it’s annual revenue, how many of each for brands should they build?

PLEASE PROVIDE IN EXCEL FORMAT

Answer 1

The problem is modeled mathematically below in the excel snippet 1. With costs, the number of employees and revenue picked from the question above. The constraints are also derived from the question as to the maximum amounts that the Hudson group wants to invest in a year. The formulae are shared below in excel snippet 2.

Once the set up is done use the excel solver to add the constraints in image 3. Add 2 numeric constraints and 1 integer constraint shown. Then hit solve. The solution will be calculated.

Answer: 3 Speciality stores, 1 news-stand, 1 brookstone store

Excel snippet 1

			E	F	G	H	I	J	K	L	M
			Cost (in mil)	Employees	Revenue(in mil)	Ideal number
	Specialty	$19	50	$9.50	3		Constraints
	Newsstands	$6.50	20	$2.40	1			75.5	<=	$75.50
	Bookstores	$8	15	$1.20	0			220	<=	260
	Brookstone stores	$12	50	$3.30	1
								Max
									34.2

Excel snippet 2

			E	F	G	H	I	J	K	L	M
			Cost (in mil)	Employees	Revenue(in mil)	Ideal number
15	Specialty		19	50	9.5	3		Constraints
16	Newsstands		6.5	20	2.4	1			=SUMPRODUCT(E15:E18,H15:H18)	<=	75.5
17	Bookstores		8	15	1.2	0			=SUMPRODUCT(F15:F18,H15:H18)	<=	260
18	Brookstone stores		12	50	3.3	1
								Max
									=SUMPRODUCT(G15:G18,H15:H18)

Image 3