Business Foundation

Business and Decision Making - A2 (Template Version)

Generate summary descriptive statistics for the delivery times provided by the courier provider. The descriptive statistics should include the following: Mean, Median, Standard Deviation, Maximum, Minimum. Give your comments on any findings you can learn from these indicators

Table of Contents
expand_more expand_less

Table of Contents

DETAILED INSTRUCTION

  1. Assessment Recap

Assessment type: Team Assignment, written report

Word Limit: 1400 words (+/- 10%)

Assignment Questions:

Question 1 (Suggested 600 words)

Using the data in Appendix A: 

a) Generate summary descriptive statistics for the delivery times provided by the courier provider. 

The descriptive statistics should include the following: Mean, Median, Standard Deviation, Maximum, Minimum.

Give your comments on any findings you can learn from these indicators. (3 marks) 

b) Based on your statistics, estimate the maximum interval in minutes that MealKit4U should use to specify the expected delivery time [scheduled delivery time ± interval]. 

Justify your estimate (2 marks)

Using the data in Appendix B: 

c) Perform a linear regression analysis to examine how sales relates to the Net Promotor Score (NPS) for the prior month (3 marks). 

d) Discuss your findings in part (c) with reference to the linear trendline and the correlation coefficient. (3 marks)

Using the data in Appendix C: 

e) Perform a trend analysis and vertical analysis on the income statement data. (3 marks) 

f) Critically evaluate the results obtained from your analysis in part (e) above and comment on at least two points. (3 marks) 

Using your team’s findings from all the previous steps: 

g) Present your team’s findings in an informative and engaging dashboard or infographic chart such as Piktochart. 

You should incorporate the data visualisation techniques discussed/presented in class. Make sure your visualisation is clear and can be easily understood by the Board. (4 marks)

 

Question 2 (Suggested 500 words)

  1. Following your team’s financial evaluation (NPV) of Proposal 1 (winery in China) and Proposal 2 (luxury resort), which project would you recommend? 

Show your calculations by replacing the following unfilled screenshot with your completed Excel spreadsheet screenshots - one for Proposal 1 and another one for Proposal 2 below. (1 + 3 = 4 marks).

  1. Replace the following screenshot with your completed selections in the Strategic Investment Assessment Model. (2 marks)

  2. Following your non-financial evaluation in the previous question, make a recommendation for the selection of Proposal 1 or Proposal 2. 

Provide reasons for why your team recommended Proposal 1 or Proposal 2. In your answer, you should comment on four (4) non-financial factors (the non-financial factors must be selected from the Excel Spreadsheet).

Make sure you comment on both projects in your answer. You should also conduct additional research and draw on real-life information to support your justification. 

Make sure you fully reference all the sources you have used in your answer. (1+4 =5 marks)

 

Question 3 (Suggested 300 words)

Reflecting on the course material covered from Week 1 to Week 8

  1. Key Term Definition

Mean

Description: The “average” of all the data points.

Calculation: Sum of all the data values, then divide by the number of data points in the sample

Excel formula: average()

Note: Each descriptive statistic contains an excel formula

Within the parentheses () the cells of the sample need to be highlighted. e.g., =average(A1:A10)

Median

Description: The middle point of the data set ordered by value

Calculation: •Order the data from low to high

  • There are “n” observations (values)

  • The position of the median is the data point in the middle (i.e., it’s the (n+1)/2 ranked value where “n” is the number of observations in the data)

  • If “n” is an odd number, the median is right in the middle

  • If “n” is an even number, the median is the average of the middle two data points

Excel formula: median()

Standard deviation

Description: A measure of how spread out the data is relative to the mean. The greater the deviation the more spread out the data is. The narrower it is, the more tightly packed it is around the mean

Calculation: Calculate the square root of the variance

Excel formula: stdev()

Range

Description: Another measure of the spread of the data, this time from the highest to the lowest point 

Calculation: Maximum value – minimum value 

Excel formula: max() – min()

Regression analysis output - Multiple R

It is the Correlation Coefficient that measures the strength of a linear relationship between two variables. The correlation coefficient can be any value between -1 and 1, and its absolute value indicates the relationship strength. 

The larger the absolute value, the stronger the relationship

1 means a strong positive relationship

-1 means a strong negative relationship

0 means no relationship at all

Regression analysis output - R square

R Square. It is the Coefficient of Determination, which is used as an indicator of the goodness of fit. It shows how many points fall on the regression line. The R2 value is calculated from the total sum of squares, more precisely, it is the sum of the squared deviations of the original data from the mean.

 

 

Regression analysis output - Adjusted R square

It is the R square adjusted for the number of independent variables in the model. You will want to use this value instead of R square for multiple regression analysis.

 

 

Regression analysis output - Standard Error

It is another goodness-of-fit measure that shows the precision of your regression analysis - the smaller the number, the more certain you can be about your regression equation. While R2 represents the percentage of the dependent variable variance that is explained by the model, Standard Error is an absolute measure that shows the average distance that the data points fall from the regression line.

 

Regression analysis output - Observations

It is simply the number of observations in your model.

 

Regression analysis output - ANOVA

df is the number of the degrees of freedom associated with the sources of variance.

SS is the sum of squares. The smaller the Residual SS compared with the Total SS, the better your model fits the data.

MS is the mean square.

F is the F statistic, or F-test for the null hypothesis. It is used to test the overall significance of the model.

Significance F is the P-value of F.

 

Regression analysis output - coefficients

 

The most useful component in this section is Coefficients. It enables you to build a linear regression equation in Excel:

y = bx + a

For our data set, where y is the number of umbrellas sold and x is an average monthly rainfall, our linear regression formula goes as follows:

Y = Rainfall Coefficient * x + Intercept

NPV - net present value

NPV = PV of future cash flows – Initial Investment

To better understand the idea, let's dig a little deeper into the math.

For a single cash flow, present value (PV) is calculated with this formula:
PV formula

Where:

  1. r – discount or interest rate

  2. i – the cash flow period

 

  1. Data - Reference

  2. Graham Institution (2023). In Vino Sustineri: Why should we care about the environmental impact of wine. Graham Institution website, accessed 25 August 2023. In Vino Sustineri: Why should we care about the environmental impact of wine? - Climate & Environment at Imperial.

  3. Lioudis N (2022) The Importance of Diversification, Investopedia website, accessed 27 August 2023. https://www.investopedia.com/investing/importance-diversification/

  4. Pradhan S (3 August 2018). What are luxury hotels doing to save the environment?. SCMP Magazine website. Accessed 25 August 2023. What are luxury hotels doing to save the environment?

 

  1. Detailed Outline

Using the data in Appendix A:

  1. Generate summary descriptive statistics for the delivery times provided by the courier provider. The descriptive statistics should include the following: Mean, Median, Standard Deviation, Maximum, Minimum. Give your comments on any findings you can learn from these indicators. (3 marks)

THEORY (Refer to the theory table above for the definitions)

Mean

Calculation: Sum of all the data values, then divide by the number of data points in the sample

Excel formula: average()

Note: Each descriptive statistic contains an excel formula

Within the parentheses () the cells of the sample need to be highlighted. e.g., =average(A1:A10)

 

Median

Calculation: •Order the data from low to high

  • There are “n” observations (values)

  • The position of the median is the data point in the middle (i.e., it’s the (n+1)/2 ranked value where “n” is the number of observations in the data)

  • If “n” is an odd number, the median is right in the middle

  • If “n” is an even number, the median is the average of the middle two data points

Excel formula: median()

 

Standard deviation

Calculation: Calculate the square root of the variance

Excel formula: stdev()

 

Range

Calculation: Maximum value – minimum value 

Excel formula: max() – min()

EXAMPLE:

Mean 

-5,059406

AVERAGE(D3:D103)

Median

-2

MEDIAN(D3:D103)

Standard Deviation

46,26442

STDEV(D3:D103)

Maximum

118

MAX(D3:D103)

Minimum

-137

MIN(D3:D103)

-> Evaluate the effectiveness of delivery based on the data just shown

Example: According to the data presented in the table, the Mean and Median negative statistics indicate that the delivery process is consistently efficient as the deviation from the scheduled delivery time is minimal and even sometimes ahead of schedule. This could be advantageous for customers to have their orders delivered early in most cases.

Charge your account to get a detailed instruction for the assignment