You are the project officer for an online company called SalesGalore! that sells a wide variety of consumer goods worldwide.
As the project officer of SalesGalore! you provide managers with key decision making analyses and recommendations involved with running SalesGalore!.
You have recently been requested to review a number of SalesGalore!'s strategies.
The creative assistant is interested to know how key demographic variables differ between countries as this may affect their new advertising strategy of utilising online advertising via social networking sites more regularly.
The logistics manager has also requested you to provide a detailed analysis how many of each type of product to stock so as to take full advantage of the possible new advertising strategy The CIO is requesting analysis to predict future online user behaviour, to help SalesGalore! to plan for future infrastructure investment.
Issue #1
Analysing advertising strategies by understanding key demographic variables
Using proprietary software you investigated online social network usage in Australia, the USA and Japan, and identified Socializer.co as the top social network site in Australia and the US. You also noticed the growing popularity of Socializer.co in Japan, and think it is worth analysing this market as well. Taking a randomly selected sample of 2,000 Socializer users from each country produced the data in the file AdPlanner.xls, available on the course website.
Required: Explain where and why you would market the following product:
The tTouchable, the newest netbook from Tangerine Computers and the latest in must have electronics is popular with both genders and is favoured heavily by people earning at least AUD 100,000.
To be able to correctly comment on the above you will need to prepare:
i. histograms in Excel for the demographic variables Gender and Income, for each country.
ii. descriptive summaries for the demographic variables Gender and Income for each country. Also compute Quartile 1, Quartile 3 and the Interquartile Range for each descriptive summary.
iii. a commentary for each country, describing the shape (symmetry, modality, outliers) of the Gender and Income distributions. Explain how
iv. an opinion on which summary measures (central tendency, dispersion) would be appropriate for the Gender and Income distributions for each country. Give reason(s) for your choice and be sure to interpret the values of the summary measures you have chosen. Be careful when interpreting Income!
v. a discussion about what SalesGalore! should imply for each country. For example, the type of person likely to view the web sites. Provide reasons as to why you suggest the specific groups.
EXCEL Instructions:
For the histogram for each country,
- you will need to create Bin columns for the histogram. E.g. BinG, could contain the Bin numbers from 1 to 2 in steps of 1 (i.e. 1, 2) for Gender, and
- a second Bin column could be called BinI, with the appropriate range of numbers for Income with a step size of 1. E.g. for Australia, BinI will contain the numbers from 1 through to 6.
- You will need to ensure the formatting of the histogram is compliant with all presentation standards
Refer to Topic 6 in the Excel Booklet for instructions on how to obtain and format a histogram. Refer to Topic 7 in the Excel Booklet for instructions on how to obtain Descriptive Statistics, the two quartiles and the interquartile range
For full marks make sure the title of each histogram ends with your personal ID (e.g. Histogram of Income bloggsj001).
Instructions for identifying outliers: Whether an observation is an outlier is a matter of judgment. One rule commonly used for identifying outliers is the 1.5×IQR rule. An observation is suspected to be an outlier if it is more than 1.5×IQR below the first quartile Q1 or above the third quartile Q3.
Note: In order to achieve full marks for this question it is essential that you fully explain what you are doing, why you are doing it and the steps involved in providing a final solution. Ensure your answer is not just a set of calculations as 25% of the marks for this question are set aside for your explanation.
Issue #2
Analysing inventory requirements
An important part of your duties as project officer is to recommend how much of each product to order for SalesGalore!'s monthly stock inventory.
You first priority is to focus on your three most recently acquired products, with the aim to maximise SalesGalore!'s profit. The first product is the set Cosmeticlogy that generates $130 in profit and is overwhelmingly popular with female shoppers up to the age of 34. Due to a current manufacturing shortage crisis however, you are only able to order up to 30 sets per month.
Tangerine's new netbook, the tTouchable, is another high seller which costs SalesGalore! only $110 to purchase yet retails at the bargain price of $385.
The third product is the box set Fragrance by Frankline, the latest fragrance from the English soccer superstar B. Frankline. The fragrance costs SalesGalore! $8.50 to purchase (per box set) and retails on SalesGalore!'s website for $133.50 (per box set).
After reviewing the profit/loss sheets from the previous financial quarter, SalesGalore! has decided to spend no more than $5800.00 per month to acquire these products. Of this amount, a maximum budget of $4,300.00 has been allocated to purchasing the products, and the remaining maximum monthly budget is to be spent on shipping the products from the suppliers to SalesGalore!'s storage warehouse.
The shipping cost of the B. Frankline fragrance is $11.25 per box set, while the tTouchable shipping cost is $18.50 per computer, which includes a shipping insurance plan of $12.00. It was also determined that a maximum of 45 tTouchables should be purchased from the supplier per month, to avoid an unacceptable increase in shipping costs.
Finally, as SalesGalore! order regularly from Cosmeticlogy Manufacturing, they have been offered a 15% reduction in cost price of the Cosmeticlogy sets, and now pay the manufacturers $11.50 per set, with shipping costs set at $8.50 per set.
Required:
(i) Detail all decision variables together with their associated objective function required to solve this problem.
(ii) The appropriate mathematical formulae to explain all of the constraints applicable to this problem.
(iii) A full Linear Programming solution to the problem. This solution needs to include the model used, the parameters employed and any reports generated.
(iv) Explain the optimal number of Cosmeticlogy sets, tTouchables and B. Frankline box sets to be ordered, sold and the optimal profit in the coming month.
(v) A discussion that responds to the following:
a) the actual total amounts spent on shipping and purchasing the products from the manufacturers.
b) any concerns from parts (i) and (ii) you may have identified when performing the calculations, and its impact on the solution to the linear programming problem.
c) the potential implications on the profit if you double the number of Cosmeticlogy sets ordered, once the manufacturing shortage crisis is over. Also include the maximum possible number of Cosmeticlogy sets you can order, without changing the optimality of the solution originally obtained.
(vi) Using the results and associated reports from the above set of calculations, detail the potential impact on your answers to (a) to (c) above of a rumour that Tangerine's rival Apple is soon to launch an iNetbook and you are concerned the profit per tTouchable may decrease to $200.
a. Which of the EXCEL reports helps you answer this question?
b. Would the solution obtained in part above still be optimal?
c. Justify your answer fully.
d. Would the solution of SalesGalore!'s profit change, if at all?
Provide your full EXCEL Solver solution in solving this linear programming problem together with an Answer Report and a Sensitivity Report in a separate Excel file.
EXCEL Instructions: All EXCEL output should bear your student ID. To ensure this, you will need to save your EXCEL file as ‘599999 SalesGalore!.xls'. In addition, your constraint names should begin with your initials, e.g. BC_Max_CosmeticlogySets.
Please refer to Topic 5 in the Excel Booklet or the Linear Programming Supplement to the textbook for detailed instructions on how to set up your spreadsheet and use Solver.
Note: In order to achieve full marks for this question it is essential that you fully explain what you are doing, why you are doing it and the steps involved in providing a final solution. Ensure your answer is not just a set of calculations as 25% of the marks for this question are set aside for your explanation.
Issue #3
Managerial Decision III: Analysing Socializer Shopping Traffic
To help you understand the long-term potential of offering products for sale on Socializer in Australia, The US and Japan, you decide to obtain online users views on their likely purchasing habits of visitors to a well know internet site during March 2010, broken down by each country. You wish to predict the probability of people using the Socializer site to purchase items from SalesGalore!s.
Six hundred users in each country were surveyed to determine whether a person who uses an online social networking facility (labeled social attitudes) would also purchase a product (labeled shopper type) through that facility. For each country answer the questions that follow. In each case show the both the full probability statement and all supporting calculations.
Australia
|
Sample Space
|
Shopper type
|
|
|
|
|
Shop
|
Not Shop
|
Totals
|
|
Social attitudes
|
Social
|
320
|
80
|
400
|
|
|
Not Social
|
160
|
40
|
200
|
|
|
Totals
|
480
|
120
|
600
|
USA
|
Sample Space
|
Shopper type
|
|
|
|
|
Shop
|
Not Shop
|
Totals
|
|
Social attitudes
|
Social
|
375
|
0
|
375
|
|
|
Not Social
|
125
|
100
|
225
|
|
|
Totals
|
500
|
100
|
600
|
Japan
|
Sample Space
|
Shopper type
|
|
|
|
|
Shop
|
Not Shop
|
Totals
|
|
Social attitudes
|
Social
|
300
|
150
|
450
|
|
|
Not Social
|
100
|
50
|
150
|
|
|
Totals
|
400
|
200
|
600
|
Required:
(a) What is the probability that a person uses a social networking site?
(b) What is the probability that a person will shop online?
(c) What is the probability that a person will not use a social networking site and they shop online?
(d) Suppose a person who uses a social networking site. What is then the probability that they will shop online?
(e) Suppose that a person shops online. What is then the probability that this person will not use a social networking site?
(f) Are ‘social attitudes' and ‘shopper type' independent or dependent? How do you know? Show calculations that support your answer.
Note: In order to achieve full marks for this question it is essential that you fully explain what you are doing, why you are doing it and the steps involved in providing a final solution. Ensure your answer is not just a set of calculations as 25% of the marks for this question are set aside for your explanation.
Attachment:- AdPlanner 2018.rar