quantitative business analysis excel 2016 only

profileNickphoto
homework.xlsx
Home>Business & Finance homework help>quantitative business analysis excel 2016 only

Q1 Product Mix

Q1 JC Dollar is a clothing store and needs to sell 200 of its shirts and 100 pairs of trousers left over from last season. One idea is to put together two offers, Package A and Package B. A is a package of one shirt and a pair of pants for $30. B consists of three shirts and a pair of pants, which will sell for $50. Management wants to sell more than 20 packages of A and more than 10 of B. How many of each do they have to sell to maximize revenue generated from the promotion?

Q2 Product Mix

Q2 USF is preparing a trip for 400 students. Krashnburn rents buses has 10 buses with a capacity of 50 seats each and 8 buses of 40 seats each, but only has 9 drivers available. The large bus rents for $800 and the small bus, $600. How many of each type bus should be rented for the trip for the least possible cost?

Q3 Media Mix

Q3 The Lewis N. Clark Company sells both international outdoor adventures and low-end local camping sites. It wants to place TV ads to be seen by the following audiences:
At least 65 million High Income Men (HIM)
At least 72 million High Income Women (HIM)
At least 70 million Low Income people (LIP)
The advertising costs and potential audiences in millions of viewers for 1 minute ads on various types of shows are listed below. It has an $800,000 budget. The company wants at least two ads on sportshows, two on new shows, and two on soap operas. Also, it wants no more than 10 ad on any one show. The number of ads of each type must be an integer.
Show Type HIM HIW LIP Cost
Sports shows 7 8 4 $120,000
Game Show 3 6 5 $40,000
News 6 3 5 $50,000
Sitcom 4 7 5 $40,000
Drama 6 6 8 $60,000
Soap Operas 3 5 4 $40,000

Q4 Staff Scheduling

Q4 The manager of a "6/10 Market" opens his store at 6AM, closes at 10PM. The number of workers he wants on duty each two-hour slot is shown below. By union rules, he must hire all full time workers and each worker must work the standard 5-8 plan. Use Solver to solve this staff scheduling problem using the integer constraint. How many workers will be needed?
Note - The demand pattern for this operation is nasty in that it will require far more workers than the theoretical minimum of 9 shown below.
INPUTS:
DEMAND MATRIX A: Enter the number of workers needed each 2 hour span.
Shift Shift Sun Mon Tue Wed Thu Fri Sat
12AM-2AM 1 0 0 0 0 0 0 0
2AM-4AM 2 0 0 0 0 0 0 0
4AM-6AM 3 0 0 0 0 0 0 0
6AM-8AM 4 2 2 2 2 2 2 2
8AM-10AM 5 2 2 2 2 2 2 2
10AM-12PM 6 2 2 2 2 2 2 2
12PM-2PM 7 5 3 3 3 3 3 5
2PM-4PM 8 5 3 3 3 3 3 5
4PM-6PM 9 6 4 3 3 3 4 5
6PM-8PM 10 6 4 4 4 4 4 6
8PM-10PM 11 6 3 3 3 3 4 6
10PM-12AM 12 0 0 0 0 0 0 0
Total Workerhours: 360
Unadjusted Workers Required: 9

Q5 Transportation Model

Q5 The Bubba BBQ Company manufactures its backyard BBQ grills in three plants and ships them to distributors in the four cities shown below. The cost to transport a grill from each plant to each destination is also shown and so is the anticipated demand this quarter from each region and the capacity that each plant will have in terms of how many units it can supply this quarter. Determine the optimal shipping plan to minimize shipping costs.
Transportation Cost Matrix: Cost per Unit Shipped From Each Plant to Each Region
New York Chicago Atlanta Los Angeles Supply
Factory A $262 $436 $532 $240 450
Factory B $500 $232 $526 $556 600
Factory C $356 $264 $244 $360 500
Demand 400 300 200 400

Q6 Extra Credit

30% Extra Credit Question: You must answer all of the prior questions to be eligible for extra on this question
Use the following historical returns to answer the following questions. Use Solver's GRG Nonlinear Method
Q6a "How should I allocate the money in my portfolio to maximize average return over 40 years?" What could be your worst return in any single year based on the historical record?
Q6b "How should I allocate the money in my portfolio to maximize average return over 40 years but lose no more than 20% in any one year? How much did you have to give up in terms of the ending value?
Q6c "How should I allocate the money in my portfolio to maximize minimum return over 40 years?" (Hint - Start with optimal solution to Q11A.) What could be your worst return in any single year based on the historical record?
Q6d "How should I allocate the money in my portfolio to maximize minimum return over 40 years but lose no more than 20% in any one year? (Hint - Start with optimal solution to Q11b.) How much did you have to give up in terms of the ending value?
US Stocks International Bonds Cash
Large Cap Mid Cap Small Cap Micro Cap Developed Emerging LT Gov't Intmed Term
Year S&P 500 Index CRSP Deciles 3-5 Index CRSP Deciles 6-7-8 Index CRSP Deciles 9-10 Index MSCI-EAFE Emerging Markets Long-Term Gov'tt Bonds Five-Year US Treasury Notes One-Month US Treasury Bills
1927 37.5% 33.9% 29.1% 25.8% 12.59% 28.7% 8.9% 4.5% 3.1%
1928 43.6% 40.1% 31.8% 46.7% 9.11% 1.1% 0.1% 0.9% 3.6%
1929 -8.4% -26.4% -38.9% -50.6% -10.98% -9.1% 3.4% 6.0% 4.7%
1930 -24.9% -36.3% -39.3% -45.5% -22.94% -9.0% 4.7% 6.7% 2.4%
1931 -43.3% -46.5% -50.3% -49.4% -38.15% -16.6% -5.3% -2.3% 1.1%
1932 -8.2% -6.5% -3.6% 10.4% 4.03% -3.9% 16.8% 8.8% 1.0%
1933 54.0% 102.7% 115.5% 203.3% 74.58% 80.2% -0.1% 1.8% 0.3%
1934 -1.4% 10.8% 21.6% 25.6% 7.23% 22.7% 10.0% 9.0% 0.2%
1935 47.7% 41.7% 58.5% 65.4% 4.72% 10.1% 5.0% 7.0% 0.2%
1936 33.9% 35.5% 53.2% 79.6% 10.47% 16.7% 7.5% 3.1% 0.2%
1937 -35.0% -42.1% -48.4% -53.4% -9.39% -4.9% 0.2% 1.6% 0.3%
1938 31.1% 38.3% 42.9% 23.3% -9.66% -4.8% 5.5% 6.2% -0.0%
1939 -0.4% -1.5% 4.5% -1.8% -13.01% -15.2% 5.9% 4.5% 0.0%
1940 -9.8% -5.7% -5.1% -12.3% 7.41% -0.7% 6.1% 3.0% 0.0%
1941 -11.6% -8.6% -9.8% -14.9% 27.60% 18.2% 0.9% 0.5% 0.1%
1942 20.3% 21.4% 25.7% 50.5% -1.70% 18.0% 3.2% 1.9% 0.3%
1943 25.9% 38.0% 58.0% 102.4% 11.88% 22.9% 2.1% 2.8% 0.3%
1944 19.7% 29.9% 41.9% 63.2% -12.11% 16.7% 2.8% 1.8% 0.3%
1945 36.4% 56.4% 63.7% 83.7% 1.30% 18.5% 10.7% 2.2% 0.3%
1946 -8.1% -9.0% -10.8% -13.4% -25.79% -12.3% -0.1% 1.0% 0.4%
1947 5.7% 1.4% -3.1% -2.7% -5.90% -1.9% -2.6% 0.9% 0.5%
1948 5.5% -0.0% -4.1% -6.6% -8.44% -14.1% 3.4% 1.8% 0.8%
1949 18.8% 22.4% 21.3% 21.5% -8.18% -3.9% 6.4% 2.3% 1.1%
1950 31.7% 30.3% 36.6% 45.9% 5.37% 7.8% 0.1% 0.7% 1.2%
1951 24.0% 18.3% 15.5% 9.8% 10.31% 9.5% -3.9% 0.4% 1.5%
1952 18.4% 11.9% 9.7% 6.5% -0.91% 4.7% 1.2% 1.6% 1.7%
1953 -1.0% -0.8% -2.9% -6.0% 11.94% 6.4% 3.6% 3.2% 1.8%
1954 52.6% 56.0% 57.5% 65.2% 33.79% 1.5% 7.2% 2.7% 0.9%
1955 31.5% 18.5% 20.8% 22.1% 6.22% 12.1% -1.3% -0.7% 1.6%
1956 6.6% 8.0% 6.5% 3.5% -4.03% 12.1% -5.6% -0.4% 2.5%
1957 -10.8% -12.4% -17.8% -15.1% -0.62% 1.6% 7.5% 7.8% 3.1%
1958 43.4% 56.1% 61.9% 70.9% 23.20% 2.0% -6.1% -1.3% 1.5%
1959 12.0% 15.4% 17.3% 18.7% 47.20% 16.5% -2.3% -0.4% 3.0%
1960 0.5% 2.4% -3.4% -5.0% 11.87% 16.3% 13.8% 11.8% 2.7%
1961 26.9% 29.0% 29.5% 30.8% 6.80% -12.6% 1.0% 1.8% 2.1%
1962 -8.7% -13.1% -16.8% -16.5% -10.21% 20.4% 6.9% 5.6% 2.7%
1963 22.8% 15.9% 18.7% 11.9% 5.91% 9.6% 1.2% 1.6% 3.1%
1964 16.5% 18.1% 16.5% 18.3% -2.51% -4.3% 3.5% 4.0% 3.5%
1965 12.5% 26.1% 35.0% 38.0% -6.37% 10.6% 0.7% 1.0% 3.9%
1966 -10.0% -5.9% -7.1% -8.3% -11.84% -1.6% 3.7% 4.7% 4.8%
1967 24.0% 39.9% 63.9% 103.4% 23.84% 11.2% -9.2% 1.0% 4.2%
1968 11.1% 21.1% 31.8% 50.2% 22.68% 26.5% -0.3% 4.5% 5.2%
1969 -8.5% -14.7% -22.2% -32.4% 2.26% 19.3% -5.1% -0.7% 6.6%
1970 4.0% -2.0% -9.9% -16.8% -14.37% 14.6% 12.1% 16.9% 6.5%
1971 14.3% 21.2% 20.3% 17.7% 31.77% 36.7% 13.2% 8.7% 4.4%
1972 19.0% 9.1% 5.6% -1.4% 39.09% 29.6% 5.7% 5.2% 3.8%
1973 -14.7% -25.9% -34.3% -40.8% -11.39% 13.9% -1.1% 4.6% 6.9%
1974 -26.5% -25.1% -25.9% -26.8% -19.56% -12.9% 4.4% 5.7% 8.0%
1975 37.2% 57.1% 60.9% 71.5% 31.02% 12.7% 9.2% 7.8% 5.8%
1976 23.8% 39.8% 50.7% 53.4% 2.33% 16.1% 16.8% 12.9% 5.1%
1977 -7.2% 3.9% 17.1% 21.8% 16.14% 24.2% -0.7% 1.4% 5.1%
1978 6.6% 10.7% 16.6% 22.4% 31.43% 19.4% -1.2% 3.5% 7.2%
1979 18.4% 33.0% 46.3% 43.7% 9.42% 35.9% -1.2% 4.1% 10.4%
1980 32.4% 31.4% 33.1% 34.6% 23.47% 34.4% -3.9% 3.9% 11.2%
1981 -4.9% 4.1% 3.1% 8.2% -3.86% -5.1% 1.9% 9.5% 14.7%
1982 21.4% 24.4% 29.4% 27.2% -1.30% -24.3% 40.4% 29.1% 10.5%
1983 22.5% 26.4% 28.8% 34.1% 23.84% 21.6% 0.7% 7.4% 8.8%
1984 6.3% -1.0% -2.2% -14.0% 2.95% 13.2% 15.5% 14.0% 9.8%
1985 32.2% 31.1% 32.8% 28.3% 50.79% 24.8% 31.0% 20.3% 7.7%
1986 18.5% 16.4% 8.8% 3.2% 65.31% 11.7% 24.5% 15.1% 6.2%
1987 5.2% 1.3% -6.9% -13.8% 24.24% 22.3% -2.7% 2.9% 5.5%
1988 16.8% 21.7% 24.8% 21.9% 27.46% 40.4% 9.7% 6.1% 6.3%
1989 31.5% 24.8% 19.2% 8.2% 11.14% 65.0% 18.1% 13.3% 8.4%
1990 -3.1% -10.5% -17.8% -27.4% -23.08% -10.6% 6.2% 9.7% 7.8%
1991 30.5% 41.9% 48.6% 50.1% 12.04% 59.9% 19.3% 15.5% 5.6%
1992 7.6% 16.1% 17.4% 28.1% -12.27% 11.4% 8.1% 7.2% 3.5%
1993 10.1% 16.3% 18.3% 20.1% 32.21% 74.8% 18.2% 11.2% 2.9%
1994 1.3% -2.6% -1.5% -3.1% 7.35% -7.3% -7.8% -5.1% 3.9%
1995 37.6% 34.1% 29.4% 33.2% 11.41% -5.2% 31.7% 16.8% 5.6%
1996 23.0% 16.8% 18.1% 19.3% 6.87% 6.0% -0.9% 2.1% 5.2%
1997 33.4% 23.3% 28.0% 24.0% 2.27% -11.6% 15.9% 8.4% 5.3%
1998 28.6% 5.8% 0.5% -8.2% 18.76% -25.3% 13.1% 10.2% 4.9%
1999 21.0% 30.7% 32.9% 31.5% 27.93% 66.5% -9.0% -1.8% 4.7%
2000 -9.1% -7.7% -11.0% -13.3% -13.37% -30.8% 21.5% 12.6% 5.9%
2001 -11.9% -2.8% 13.2% 33.7% -21.40% -2.6% 3.7% 7.6% 3.8%
2002 -22.1% -18.6% -21.6% -13.9% -15.80% -6.2% 17.8% 12.9% 1.6%
2003 28.7% 41.5% 51.6% 78.2% 39.42% 55.8% 1.4% 2.4% 1.0%
2004 10.9% 18.2% 21.1% 16.6% 20.38% 25.6% 8.5% 2.3% 1.2%
2005 4.9% 11.1% 6.8% 3.7% 14.47% 34.0% 7.8% 1.4% 3.0%
2006 15.8% 13.9% 16.2% 18.0% 25.71% 32.1% 1.2% 3.1% 4.8%
2007 5.5% 4.9% 0.0% -7.9% 12.44% 39.4% 9.9% 10.1% 4.7%
2008 -37.0% -38.2% -37.6% -41.5% -43.56% -53.3% 25.9% 13.1% 1.6%
2009 26.5% 41.8% 44.1% 61.1% 33.7% 78.5% -14.9% -2.4% 0.1%
2010 15.1% 27.4% 30.5% 29.1% 8.9% 18.9% 10.1% 7.1% 0.1%
2011 2.1% -0.9% -4.0% -10.2% -12.2% -18.4% 28.2% 9.5% 0.0%
2012 16.0% 16.4% 18.1% 17.3% 16.4% 18.2% 3.3% 2.1% 0.1%
2013 32.4% 39.3% 43.0% 49.2% 21.0% -2.6% -11.4% -1.1% 0.0%
2014 13.7% 8.2% 4.4% 2.7% -4.3% -2.2% 23.9% 3.1% 0.0%
2015 1.4% -3.8% -7.1% -11.4% -3.0% -14.9% -0.1% 1.7% 0.0%