Question: I would like you to use the data file provided, which contains information on 109 2019 sets that had positive value potentials from the themes
I would like you to use the data file provided, which contains information on 109 2019 sets that had positive value potentials from the themes I discuss in the (B) version of the case. Your task is to build an optimization model that determines which sets to invest in and how many of each to buy to maximize the total value potential while meeting the other conditions described in the case. I would like you to build and solve the model using the $4,000 budget figure given in the case. Then I would like you to adjust your model to analyze the range of budget amounts specified in the case and recommend a budget amount for Greg to use based on your analysis.
Below is the case and a screenshot of the excel page. Please show me how to do this, will thumbs up. Thank you!
Case:
While the future value of a retired LEGO set is partially dependent upon the desirability of the set itself as well as the theme (such as Harry Potter, Minecraft, Star Wars, etc.), another major determinant of the desirability of a set as an investment is the value potential, which is defined as the difference between the price that LEGO should be charging for the set based on its characteristics and the actual retail price. Sets with large value potential represent outsized investment opportunities in much the same way that a value mutual fund invests in securities that are potentially undervalued based on their fundamentals.
In his prior analysis Greg has developed a model that determines the price that LEGO should be charging for sets and has used it to determine the value potential for all of the sets that were released in 2019. Some sets ending up having positive value potentials, while others had negative value potentials. He eliminated all of the sets with negative value potential from further investment consideration. He also researched the resale prices for retired LEGO sets on the secondary market and narrowed his potential investment options to sets in the following themes: City, Creator, DC Super Heroes, Friends, Harry Potter, Hidden Side, Ideas, Marvel Super Heroes, Minecraft, Ninjago, Overwatch, Speed Champions, and Star Wars. This left him with the 109 sets listed in the data file provided.
Gregs task now is to determine which specific sets he should purchase as an investment. He has initially allocated $4,000 to his investment budget, although he is open to adjusting that amount up or down within the range from $2,000 to $6,000 based on the relationship between value potential and total investment.
Like any well-constructed investment portfolio, Greg also wants to ensure that he is properly diversified to limit his exposure to adverse market conditions affecting one particular set or theme in the future. As a result, he had decided not to purchase more than 10 copies of any one set, and he seeks to avoid any one theme representing more than 20% of the total number of LEGO sets that he has purchased.
Based on his analysis of sets selling on the secondary market, however, Greg thinks that sets from three themesHidden Side, Ideas, and Star Warshave the potential to increase in value significantly upon retirement; and he wants to be in position to take advantage of such an opportunity in the future. Therefore, he requires that at least 5% of the total sets purchased are from each of those three themes.
Perhaps most importantly, Greg wants to avoid the wrath of his wife, who is not interested in turning their entire house into a warehouse for his new investments to sit while they appreciate. He has negotiated the use of one metal shelving unit with dimensions 60 inches x 36 inches x 24 inches that is set up in the basement to store these LEGO sets. He has assured his wife that the sets he purchases for investment will fit entirely on those shelves.
Excel:
Step by Step Solution
There are 3 Steps involved in it
Get step-by-step solutions from verified subject matter experts
Study Help