Faculty of Business and Law

MOD007192

Introduction to Financial Analysis and Management

Element 011 Assignment

Student ID: 1919955

Circle the appropriate data set A B C D

Academic Year: TRI 2 - 2020/2021

1 of 14
Important Notes:
 Student IDs that end with number 0, 1, 8 and 9 use data set A
 Student IDs that end with number 2, 3 and 4 use data set B
 Student IDs that end with number 5, 6 and 7 use data set C

If you use the wrong dataset, your awarded mark will be reduced by 15%.

You must screen capture images of the answers that you create in your excel file and
paste them into the appropriate sections in your assignment.

All answers should be given to 2 decimal places.

2 of 14
Contents

Question 1 – Frequency Distribution.................................................................................4

Question 2 – Regression Analysis.....................................................................................6
Question 3 – Time Series and Forecasting (Additive Model)..........................................7
FORMULAE SHEETS...........................................................................................................8
Descriptive Statistics..........................................................................................................8
Ungrouped data..................................................................................................................8
Regression Analysis...........................................................................................................9
Time Series Analysis and forecasting..............................................................................10

3 of 14
 Question 1 – Frequency Distribution

Weighting – 40% of total marks

ALPHA (a producer of advanced measuring, monitoring, controlling and testing

instruments) is struggling to survive. ALPHA’s staff voted for a pay cut to save jobs. John
Doe, the finance manager, has been assigned the task to investigate all employee
salaries. The salaries of all employees have been recorded into the ALPHA worksheet.

a) Describe the difference between a sample and a population and explain why is
important to understand the difference of these terms? Which of these is used in this
task? (1 marks)
- Sample is used to take a little set from a population to make a research it’s
important to remember sample can represent a whole population.
- Populations its used to make the research, requires data from every member
of the population.
It’s important to understand because the sample it’s a statistical testing study
made by population.

b) Construct a frequency table of the salaries, using a maximum of 10 groups, and enter
your values in the table below. (5 marks; 1 per class boundary and 3 for frequency)

Lower Class boundary Upper class boundary Frequency (f)

5000 14999 4

15000 24999 10
25000 34999 22
35000 44999 58
45000 54999 82
55000 64999 82
65000 74999 58
75000 84999 22
85000 94999 10
95000 104999 4

c) Calculate the mean, median and mode of the data set. Which measure of central
location best describes the data in this task and why? (3 marks)
Not finish.

4 of 14
 The best

d) Use the frequency distribution to draw a rough sketch/ graph and describe the shape of
the distribution in appropriate terms - these might include "approximately normal,"
"symmetric," "skewed" (identify in which direction). How would you expect the mean
and median values to relate to each other? (2 marks)

f1
90
80
70
60
50
40
30
20
10
0
0 20000 40000 60000 80000 100000 120000 140000

This frequency distributions its symmetrical( Lighth Tailed) in the middle. The mean and
median are the same when in a perfect symmetrical, because in this case has one
mode( unimodal) makes bougth being relatives.

e) Calculate the range, the interquartile range, the variance, and the standard deviation.

(2 marks)

f) Perform calculations in order to provide responses to the questions below:

5 of 14
 (i) How many people were working at the company? (1 mark) A:352

(ii) What is the average salary at this company? (1 mark)-A:54870.36

(iii) What percentage of employees that earn less than £55,000? (1 marks) A: 50%

(iv) How many employees earn more than £55,000? (1 mark) A: 176

(v) The manager decided to deduct all the salaries by 5%. Calculate measures of
central location and dispersion of the new values (mean, median, mode, quartile
1 and 3, Interquartile range, range, variance, standard deviation and standard
error including the highest and the lowest salaries (10 marks)

Not Finish
Interquartile Range=22719.38

g) A rival company published a summary of their financial information indicating that the
mean salary of their employees is £75,000 with a standard deviation of £40,000.
Compare the co-efficient of variation for both companies. (3 marks)

Explain results from all your calculations above and conclude your analysis. Your
answers should include (500 words) (10 marks)

(i) The purpose of the descriptive statistics.

6 of 14
(ii) The results of your analysis of the questions above.

(iii) The interpretation of your results/methods including any suggestions where

appropriate.

(iv) Conclusion – a summary of what you have achieved.

Descriptive statistics its useful for two purposes, to provide basic information about
variables in a dataset and to highlight potential relationships between variables. The
three most common descriptive statistics can be displayed graphically or pictorially
and are measures of: Graphical/Pictorial Methods. And are used to describe the
basic features of the data in a study. They provide simple summaries about the
sample graphic analysis; they form the basis of virtually every quantitively analysis
of data. The measures of Central location it’s the best to describe data, its possible
that two datas have the same mean, this all to explain the measures, it’s going to be
talk about. Mean,Median,Mode,Range,Standard Deviation,Sample
Variance,Interquartilie Range,Semi- Interquartilie Range. In a data set that is
oredered from least to largest, the median is the number in the middle. median
account is =MEDIAN (salaries).Mean is calculated by dividing the total number of
values in data set by the numbeer of values in the data set, = AVERAGE(salaries).
Mode is known for being the common number in a collection of data,
=MODE(salaries). Range is the highest and lowest scores are on a scale of one to
ten, it’s the most basic indicator of variation.The count for range its maxium-
minium.The quartilies, or lower, median, served to put four equal parts with
roughly,equal numbers of observations in each portion.The interquartilie range is
not susceptible to outliers.The count for interquartilie range it’s the upper quartilie-
lower quartilie. Variance is average squared deviation of the values from the mean
is known as variance.The variance incorporaes all values in the estimate,dislikeble
previous measures of variability.Standard Deviation is the difference between any
data and the reason why the standard or common.

Question 2 – Regression Analysis

Weighting – 30% of total marks

7 of 14
The ACE is situated by the harbour in the historic town of Smallville. The ACE is a family
run pub providing bed and breakfast accommodation and delicious home-made bar meals.
Sarah Essen, marketing manager, needs to investigate the company advertising
expenditure and its sales revenue. Sarah believes that the company sales revenue
depends on the advertising expenditure as many small companies run advertising to
support the selling function. The data of advertising expenditure and weekly sales from
last year has been collected and entered on the worksheet entitled ACE.

(a) State, giving a brief explanation, which variable would be the dependent variable and
which variable would be independent variable. (2 mark)

(b) Draw a scatter plot of this data on graph paper and comment on the association
between ‘Advertising Expenditure’ and ‘Weekly Sales’ as indicated by the scatter plot.
(3 marks)

(c) Calculate average of ‘Advertising Expenditure’ and ‘Weekly Sales’, the Correlation
coefficient (R) and Coefficient of Determination (R 2) for this data. What does the value
of your coefficient tell you about the association between the two variables? (6 marks)

8 of 14
(d) State the regression equation for this task. (5 marks)

(e) Using the equation that you achieved from above estimate the sale revenue if

(i) the advertising expenditure were £1,000 (2 marks)

(ii) the advertising expenditure were £1,500 (2 marks)

h) Draw conclusions from the results that you achieved from the questions above. Your
answers should include (300 - 500 words): (10 marks)

(i) The purpose of regression analysis.

(ii) The results of your analysis for each question.

(iii) The interpretation of your results/methods including any suggestions where

appropriate. Your interpretation should include the interpretation of correlation

9 of 14
 between two variables including the strength, direction, and an explanation of the
effect.

(iv) Conclusion – a summary of what you have achieved.

The purpose of regression analysis is to allow us to determine the relationship

between two or more variables of interest.Weekly advertising expenditure is
considered the letter X and independent variable, weekly sa;es it’s considered the
letter Y and it’s a dependent variable because advertising helps increase the sales
so makes the sales to be dependent on advertising. The relationship between these
two are predictors because the sales depend on the advertising. 4% of the sales is
based on variability in advertising. The correlation coefficient is related with two
states of variability. To find the state the calculation is the value of the correlation
and has to do the square. The R^2 is 4.18% and the final correlation coefficient size
is little if any correlation is between 0.01 to 0.30 and the value is 0.20 or 20%. In
this case it’s little but the slope is going in the middle but it's going up a little. The
relationship is normal. 

Question 3 – Time Series and Forecasting (Additive Model)

Weighting – 30% of Total Marks

The manager of the electrical department at Arnold’s store asked a statistician to perform
a time series analysis on the quarterly sales figures of the numbers of TVs sold over the
past 3 years. The statistician was called away on urgent business and you have been
asked to finish the analysis. A copy of the data is shown on ARNOLD worksheet. You
have been told that the aim is to forecast the next 4 quarters’ sales.

Complete the calculations that are required to estimate the sale forecast for Quarter 1, 2, 3
and 4 of Year 4 including the values of sales forecast for the quarter 1, 2, 3 and 4 of year
4. The table should include, Moving Average, Trend Centered Moving Average, Seasonal
variation, adjusted seasonal values, extended trend and sale forecast. (22 marks)

10 of 14
Conclude the results that you achieved from the questions above. Your answers should
include: (100 words) (8 marks).

(i) The purpose of the forecasting.

(ii) Conclusion – a summary of what you have achieved.

The purpose of forecasting is to prepare people and businesses from losing money and to
enhance human comfort. Good forecasts pay off economically not by generating large sums of
money but rather by preventing the loss of a large sum of money. Forecasting serves to predict
what can happen in the future. It’s a strategic tool that assists business people in dealing with the
complexity of what could or could not happen. Forecasting is used by entrepreneurs, government
agencies, analytics and investors to determine how to distribute capital and prepare reports. Also
use it to bugtes for upcoming expenditures.

11 of 14
FORMULAE SHEETS
Descriptive Statistics
Excel Function
Returns the absolute value of a number.
ABSOLUTE The absolute value of a number is the ABS(number)
number without its sign.
Counts the number of cells that contain
numbers and numbers within the list of
COUNT (n) arguments. Empty cells, logical values, COUNT(A1:A100)
text, or error values in the array or
reference are ignored.
Calculates how often values occur within a
FREQUENCY range of values, and then returns a vertical FREQUENCY(data_array,bins_array)
array of numbers.
MAXIMUM Returns the largest value in a set of values. MAX(A1:A100)

Returns the smallest number in a set of

MINIMUM MIN(A1:A100)
values.

TOTAL (SUM) Adds all the numbers in a range of cells. SUM(A1:A100)

Ungrouped data

Coding formula Excel Function

n
∑ X1
X = i=1
n
MEAN ( X ) ∑ sigma – meaning
Returns the arithmetic
mean of the arguments.
AVERAGE(A1:A100)
‘the sum of’,
X is the symbol for
mean
Returns the most
frequently occurring, or
MODE MODE(A1:A100)
repetitive, value in an
array or range of data.
Returns the median of MEDIAN(A1:A100) or
MEDIAN (Q2)
the given numbers. QUARTILE.INC(A1:A100,2)
QUARTILE (A1:A100,1)
LOWER Returns the lower
Or
QUARTILE (Q1) quartile of a data set.
QUARTILE.INC(A1:A100,1)

12 of 14
 QUARTILE (A1:A100,3)
UPPER Returns the upper
Or
QUARTILE (Q3) quartile of a data set.
QUARTILE.INC(A1:A100,3)
Max(A1:A100)-
RANGE Max – Min
Min(A1:A100)
Calculates standard
STANDARD deviation based on the
DEVIATION entire population given STDEV.P(A1:A100)
(Population) as arguments.

STANDARD Estimates standard

DEVIATION deviation based on a STDEV.S(A1:A100)
(Sample) sample.
Calculates variance
VARIANCE
based on the entire VAR.P(A1:A100)
(Population)
population.

Calculates variance
VARIANCE ∑ ( X i−X )2 based on the entire VAR.S(A1:A100)
(Sample) n−1 sample

MEAN Returns the average of

ABSOLUTE the absolute deviations
AVEDEV(A1:A100)
DEVIATION of data points from their
(MAD) mean.

Regression Analysis

Coding
Original Data Excel Function
formula
Co-efficient
of PEARSON(array1,array2)
Correlation

Co-efficient 2

of ‫ۍ‬
𝑛 σ 𝑛𝑖=1 𝑋𝑖 𝑌𝑖 − σ 𝑛𝑖=1 𝑋𝑖 σ 𝑛𝑖=1 𝑌𝑖
‫ې‬ RSQ(known_y's,known_x's)
𝑅 = ‫ێ‬
2 ‫ۑ‬
determination ‫ێ‬
‫ۏ‬
‫ۑ‬
ටሾ𝑛 σ 𝑛𝑖=1 𝑋𝑖2 − ሺσ 𝑛𝑖=1 𝑋𝑖 ሻ2 ሿ× ሾ𝑛 σ 𝑛𝑖=1 𝑌𝑖2 − ሺσ 𝑛𝑖=1 𝑌𝑖 ሻ2 ሿ
‫ے‬

INTERCEPT(known_y's,know
Intercept
n_x's)

Slope SLOPE(known_y's,known_x's)

13 of 14
 Time Series Analysis and forecasting

Coding
Original Data Excel Function
formula
Intercept INTERCEPT(known_y's,known_x's)

Slope SLOPE(known_y's,known_x's)

Y=T+S+I
Time Series T is the Trend
None
(Additive Model) S is the Seasonal
I is Irregular Component

Forecast Y value = Extended Trend value

Forecast Y value None
+ Adjusted Seasonal value
(Additive Model)

14 of 14