Reference no: EM132322624 , Length: word count : 1000

Assessment Task : Data Analytics Using Excel

Case Study 1: Sam’s Sausages Pty Ltd

Sam’s Sausages Pty Ltd is manufacturer of boutique smallgoods. One of the most important customer groups for Sam’s Sausages comprises Sydney’s high-end restaurants and hotels.

Sam Mincer, the owner of Sam’s Sausages, wants to better understand the smallgoods buying pattern of the high-end restaurants and hotels. In short, he wants to be able to accurately forecast the number of high-end restaurant and hotel customers that will place large orders with his firm each month. This will enable Sam to better plan his production of smallgoods and therefore reduce his waste and costs.

Sam believes that when someone tastes his delicious smallgoods they will want even more, so he has suggested that orders should exhibit a level of autocorrelation. Sam has also collated data on the number of high-end restaurant and hotel customers that have placed large orders per month over the past 4 years. Required data set can be found in the link provided named: Sam’s sausages.

YOU ARE REQUIRED TO:

1. Design and build a comprehensive, robust, accurate and necessarily flexible model (i.e. an MS Excel spreadsheet) to fully analyse the stated business problem.

2. Write a supporting report (500 words) showing:

• how your model works (i.e. its inputs, assumptions, methods, calculations, etc) and why you designed it that way;

• the core outputs and results from both your model and, importantly, any useful sensitivity/scenario analysis; and

• the conclusions drawn from the analysis, and the recommendations made to solve the stated business problem.

3. Submit both your Excel spreadsheet and your report by the due date and time.

Case Study 2: Combo Corporation 

Combo Corporation is an investment advisory firm that is considering putting together a portfolio of Australian stocks for its clients. The firm has obtained the weekly price return data (i.e. the percentage increase or decrease in stock price week on week) for 6 potential stocks over the past 49 weeks. Required data set can be found in the link provided named: Combo Corporation.

The firm has engaged you to develop a binary programming model to determine which four stocks (out of the possible six stocks) it should hold in an equally-weighted portfolio, if the aim is to maximise the portfolio expected weekly return, while retaining the variance of the portfolio weekly returns at no more than 0.0002.

In order to do this the firm has reminded you of some finance theory which states that an equallyweighted portfolio of ‘n’ stocks has the following two relationships:

• Portfolio Expected Weekly Return = (1/n)*(E[r1] + E[r2] + … + E[rn])

So, for a portfolio of four stocks the expected weekly return will equal 0.25 multiplied by the sum of the average weekly returns for each of the four stocks in the portfolio.

• Portfolio Variance of Weekly Returns = (1/n2)*(Sum of all terms in an un-weighted covariance matrix)

Asset 1	Asset 2	Asset 3	Asset 4
Asset 1	Variance of Asset 1's returns	Covariance between Asset 1 and Asset 2 returns	Covariance between Asset 1 and Asset 3 returns	Covariance between Asset 1 and Asset 4 returns
Asset 2	Covariance between Asset 2   and Asset 1returns	Variance of Asset 2's returns	Covariance between Asset 2 and Asset 3 returns	Covariance between Asset 2 and Asset 4 returns
Asset 3	Covariance between Asset 3 and Asset 1 returns	Covariance between Asset 3 and Asset 2 returns	Variance of Asset  3's returns	Covariance between Asset 3 and Asset 4 returns
Asset 4	Covariance between Asset 4 and Asset 1 returns	Covariance between Asset 4 and Asset 2 returns	Covariance between Asset 4 and Asset 3 returns	Variance of Asset 4's returns

Where an un-weighted covariance matrix for the weekly returns of four stocks is formed as follows: Moreover, in Excel:

• The variance of a number of cells is found via the formula =VAR(number1, number2, …) where you put in the brackets that range of cells for which you want to calculate the variance

• The covariance between two sets of numbers (which must be of the same length) is found via the formula = COVAR(array 1, array2) where array 1 is the first range of cells and array 2 is the second range of cells between which you want to find the covariance.

YOU ARE REQUIRED TO:

1. Design and build a comprehensive, robust, accurate and necessarily flexible model (i.e. an MS Excel spreadsheet) to fully analyse the stated business problem.

2. Write a supporting report (500 words) showing:

• how your model works (i.e. its inputs, assumptions, methods, calculations, etc) and why you designed it that way;

• the core outputs and results from both your model and, importantly, any useful sensitivity/scenario analysis; and

• the conclusions drawn from the analysis, and the recommendations made to solve the stated business problem.

1000 words

Attachment:- Sams Sausages Pty Ltd.rar