Design and build a comprehensive model to analyse problem

Assignment Help Other Subject
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

Reference no: EM132322624

Questions Cloud

Help justify enterprise system investments : When a company is considering enterprise systems investments, what are some factors that help justify enterprise system investments?
Achieve a set of goals for given process capability : In the context of the CMMI, a "collection of related activities performed together to achieve a set of goals for a given process capability" is called a:
Find the mean and variance of the probability density : Find the mean and varience of the probability density function f(y)=y/θ2 ey2/2θ2 y≥0
Create a personal SWOT analysis : MGMT6002 - Influencing and Making Decisions - Laureate International Universities - create a personal SWOT analysis and comment on their fit with organisation
Design and build a comprehensive model to analyse problem : Design and build a comprehensive, robust, accurate and necessarily flexible model to fully analyse the stated business problem.
Future savings for an organization : Identify the advantages, disadvantages, computer requirements, initial costs, and future savings for an organization considering an engagement in virtualization
Create diagram that illustrates the communication structure : CIS 505 - Communication Technologies - Create a diagram that illustrates the communication structure of your chosen technology in Visio or its open source.
System for rapidly developing software : One of the tenets of XP is that all code is written by two programmer sitting at the same terminal.
Evaluate the effectiveness of the delivery of a program : Post the two questions you created to evaluate the effectiveness of the delivery of a program for the homeless population in your community

Reviews

len2322624

6/14/2019 11:25:31 PM

All assumptions, inputs and output are clearly set out in the spreadsheet. Easy to understand for the reader with appropriate text describing the details and calculations. Report covers problem, assumptions and conclusions. Tells the story to the reader well and supports this with evidence in a sustained and logical fashion.

Write a Review

Other Subject Questions & Answers

  Cross-cultural opportunities and conflicts in canada

Short Paper on Cross-cultural Opportunities and Conflicts in Canada.

  Sociology theory questions

Sociology are very fundamental in nature. Role strain and role constraint speak about the duties and responsibilities of the roles of people in society or in a group. A short theory about Darwin and Moths is also answered.

  A book review on unfaithful angels

This review will help the reader understand the social work profession through different concepts giving the glimpse of why the social work profession might have drifted away from its original purpose of serving the poor.

  Disorder paper: schizophrenia

Schizophrenia does not really have just one single cause. It is a possibility that this disorder could be inherited but not all doctors are sure.

  Individual assignment: two models handout and rubric

Individual Assignment : Two Models Handout and Rubric,    This paper will allow you to understand and evaluate two vastly different organizational models and to effectively communicate their differences.

  Developing strategic intent for toyota

The following report includes the description about the organization, its strategies, industry analysis in which it operates and its position in the industry.

  Gasoline powered passenger vehicles

In this study, we examine how gasoline price volatility and income of the consumers impacts consumer's demand for gasoline.

  An aspect of poverty in canada

Economics thesis undergrad 4th year paper to write. it should be about 22 pages in length, literature review, economic analysis and then data or cost benefit analysis.

  Ngn customer satisfaction qos indicator for 3g services

The paper aims to highlight the global trends in countries and regions where 3G has already been introduced and propose an implementation plan to the telecom operators of developing countries.

  Prepare a power point presentation

Prepare the power point presentation for the case: Santa Fe Independent School District

  Information literacy is important in this environment

Information literacy is critically important in this contemporary environment

  Associative property of multiplication

Write a definition for associative property of multiplication.

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd