Coursework 1: Analysis of Expenditure, Project Management, and Financial Planning

This coursework involves analysis of expenditure, project management, and financial planning. It includes tasks such as finding sum and variance, constructing tables, drawing network diagrams, calculating coefficients, and interpreting results.

Coursework 1

Contents
PART 1...................................................................................................................................................5
TASK 1....................................................................................................................................................5
(a) Find the sum and variance in excel...............................................................................................5
(b) Interpret and analyze mean and standard deviation...................................................................5
TASK 2....................................................................................................................................................5
(a) Construct the three tables in excel and paste it here by calculate the value given......................5
(b) Why there is a difference in the mean and standard deviation of grouped and ungrouped data
and which one is more accurate?......................................................................................................6
(c) Give a comment on the minimum amount of the consumer needs estimated to spend on the
top 25% of the expenditure...............................................................................................................7
TASK 3....................................................................................................................................................7
Give the difference between the cross – sectional data and time series data with reference to the
examples considering the demographic profile and lifestyle nature of the consumers....................7
TASK 4....................................................................................................................................................8
(a) Draw a network diagram which shows the movement of forwards and backward passes..........8
(b) Identify the critical path and duration of the project...................................................................9
(c) Distinguish between critical and non – critical activities............................................................10
TASK 5..................................................................................................................................................10
(a) Construct a correlation matrix and calculate the coefficients between quarterly sales and total
cost and average order value and gross profit................................................................................10
(b) Identify the best predictor of the quarterly sales, explain the reason.......................................10
(c) Draw a scatter graph and show a trend line of the regression equation....................................11
(d) Interpret the coefficient of correlation and determination........................................................11
(e) State the equation of regression and interpret the value of intercepts.....................................11
TASK 6..................................................................................................................................................12
1. Calculate the coefficients of variation for business A and B........................................................12
2. By assuming that the risk of retailer is negative, which project should be chosen?....................12
TASK 7..................................................................................................................................................12
Give a reflective learning through the project.................................................................................12
PART 2.................................................................................................................................................12
1. Estimate the variable cost through high – low method...............................................................12
2. Calculate the monthly fixed cost.................................................................................................13
3. Calculate break even of January 2021.........................................................................................13
4. How many t-shirts should be sold to achieve the target profit in January 2021..........................13
5. Calculate margin of safety...........................................................................................................13

6. Interpret the above calculations..................................................................................................13
REFERENCES........................................................................................................................................14

PART 1
TASK 1
(a) Find the sum and variance in excel sheet.
Expenditure (£) on Stand Mixers
Mean 224.00
Standard Error 6.58
Median 224.00
Mode 224.00
Standard Deviation 75.00
Sample Variance 5625.00
Kurtosis -0.49
Skewness -0.32
Range 301.00
Minimum 68.00
Maximum 369.00
Sum 29120.00
Count 130.00
Coefficient of Variation 33.5%
(b) Interpretation and analysis of mean and standard deviation.
From the data it has been interpreted that, the mean is stated as 224 and standard
deviation is stated as 75. It means that there is a significant difference between the both
factors. Therefore, it is revealing that the data point which are used, are very far from the
value of the mean.
TASK 2
(a) Construction of the three tables in excel and paste it here by calculating the value given.
Table 1
Expenditure (£) Frequency
Frequency
(%)
Under 100 14 11%
100 and under 200 35 27%
200 and under 300 65 50%

300 and over 16 12%
Total: 130 100%
Table 2
Expenditure (£) Frequency
Cumulative
Frequency Cumulative Frequency (%)
Under 100 14 14 11%
Under 200 35 49 38%
Under 300 65 114 88%
Under 400 16 130 100%
Total: 130
Table 3
Expenditure (£)
Frequency
(f)
midpoint
(x) fx (x-mean) (x-mean)2 f(x-mean)2
Under 100 14 49.5 693 -163.8461538 26845.56213 375837.8698
100 and under 200 35 149.5 5232.5 -63.84615385 4076.331361 142671.5976
200 and under 300 65 249.5 16217.5 36.15384615 1307.100592 84961.53846
300 and over 16 349.5 5592 136.1538462 18537.86982 296605.9172
Total: 130 27735 -55.38461538 50766.86391 900076.9231
Mean 213.3461538
Variance 390.5143377
Standard Deviation 19.76143562
(b) Why there is a difference in the mean and standard deviation of grouped and ungrouped
data and which one is more accurate?
The grouped data mean and standard deviation is approximately stating at 214 and 20. While
on the other hand, the standard deviation and mean of ungrouped data is 224 and 75. As it can be
clearly seen that there is a considerably main difference in the values of the both the terms. This
happens since the ungroup data is the raw data and the function and calculated on the basis of raw
data itself. But, in the case of grouped data, it is simply divided into group and the functions are
performed. Therefore, the grouped data 1 is considered as more accurate as compared to other.

(c) Give a comment on the minimum amount of the consumer needs estimated to spend on
the top 25% of the expenditure.
The least needs of the consumer of the top 25% of the people expenses are approximately
around 250.
TASK 3
Give the difference between the cross – sectional data and time series data with reference to
the examples considering the demographic profile and lifestyle nature of the consumers.
Cross – sectional Data Time Series Data
It focusses on the thought of regular factors
on the same point at the same time period
(Kallner, 2017). Such as, the income which
has been generated by the company of
various goods in a year.
It focusses on a factor over a specific period
of time.
For example, the total income of a product
is 3 years.
The retailer should think about using cross-sectional data because it gathers
information on a variety of characteristics such as lifestyle preferences and demographic
profiles. As a result, the store will be able to understand their target clients using this plan.

TASK 4
(a) Draw a network diagram which shows the movement of forwards and backward passes.
s

(b) Identify the critical path and duration of the project.
The critical path which has been identified is A-C-E-I-J of 23 weeks. While the time duration
of the project is stated as 10.

(c) Distinguish between critical and non – critical activities.
Critical actions are those which helps in clearly defining the end and start time. If these
operations are significantly postponed, the project will almost certainly be delayed as well. Non-
critical tasks, on the other hand, determine the path that does not have any time constraints and can
work in a more suitable and effective manner.
TASK 5
(a) Construct a correlation matrix and calculate the coefficients between quarterly sales and
total cost and average order value and gross profit.
Coefficient of
correlation
Coefficient of
determination
Quarterly Sales Revenue and total
costs 0.54003673 0.29163967
Quarterly Sales Revenue and
Average order value 0.974847027 0.950326726
Quarterly Sales Revenue and gross
profit 0.423092657 0.179007396
(b) Identify the best predictor of the quarterly sales, explain the reason.
The finest forecaster should be mostly in between the coefficient of average order value and quarterly
income with 0.97. As the best predictor is the highest correlation coefficient.

(c) Draw a scatter graph and show a trend line of the regression equation.
0 500 1000 1500 2000 2500 3000
0.00
20.00
40.00
60.00
80.00
100.00
120.00
f(x) = 0.0311935609089604 x + 10.2667468286301
R² = 0.950326726324606
Quartely sale revenue and average order value
Quarterly sales revenue
Average order value
(d) Interpret the coefficient of correlation and determination.
The coefficient of correlation reveals that the level of degree of relatability at which the
variable are linked to each other. While on the other hand, determination shows that approximately it
is 95% related to the regression model and applicable in the data.
(e) State the equation of regression and interpret the value of intercepts.
The equation of Regression is mentioned below:
Y = 0.0312X + 10.267
Coefficient
s
Standar
d Error t Stat
P-
value Lower 95%
Upper
95%
Lower
95.0%
Upper
95.0%
Intercept
9.82273122
9
2.07508
5
4.73365
1
8.17E
-05
5.53996532
8
14.105
5
5.53996
5
14.105
5
Sales
Revenue 0.03146122
0.00149
7 21.019
5.78E
-17
0.02837197
8
0.0345
5
0.02837
2
0.0345
5

TASK 6
1. Calculate the coefficients of variation for business A and B.
Venture A:
Coefficient of variation = (Standard Deviation / Net EV) * 100 %
= (30.08 / 47.0) * 100 = 64
Venture B:
Coefficient of variation = (Standard Deviation / Net EV) * 100 %
= (13.32 / 41.2) * 100 = 32.33
2. By assuming that the risk of retailer is negative, which project should be chosen?
The retailer should consider going in with the Project B which was for the purpose of
investment as the low coefficient of variation portrays the lower level of dispersion around the value
of mean.
TASK 7
Give a reflective learning through the project.
By taking part in this specific project, I can improve a variety of talents. It provides
me with a comprehensive representation of the situation as well as financial authority that
will be advantageous to my professional future. When I completed this project, I was
delighted since I knew it would boost my knowledge and efficiency. After completing the
above presentation, my confidence grew. The most difficult aspect of the project is
miscalculating numbers. I had glitches matching the numbers because of these issues. The
very next time, I'll concentrate on understanding formulas that will make my future project's
difficult numbers easier to understand.
PART 2
1. Estimate the variable cost through high – low method.
Variable Cost per unit = (Highest Activity Cost - Lowest Activity Cost) / (Highest Activity
Units - Lowest Activity Units)

= (4,00,000 - 1,83,200) / (36,000 - 13,320) = 9.56
2. Calculate the monthly fixed cost.
Fixed Costs = Highest Activity Cost - (Variable Cost Per Unit * Highest Activity Units)
= 4,00,000 - (9.55 * 36,000) = 56200
3. Calculate break even of January 2021.
No of units to be sold in January to meet break – even point = Fixed costs / (Selling price per
unit - Variable cost per unit)
= 56200 / (12.45 – 9.55) = 19380
4. How many t-shirts should be sold to achieve the target profit in January 2021.
No of units to be sold in January to achieve a profit = (Estimated Profit + Fixed Costs) /
(Selling price per Unit- Variable cost per unit)
= (2,00,000 + 56200) / (12.45 - 9.55) = 256200 / 2.9 = 88344.82 units
5. Calculate margin of safety.
Margin of safety = (Current Sales Level – break-even point) / Current Sales level
= (88345 – 19380) / 88345 = (68965 / 88345) * 100 = 78.06%
6. Interpret the above calculations.
(a) The high-low method has the benefit of just using sales in units and total cost to calculate variable
and fixed costs. Because it employs the highest and lowest unit and cost actions.
(b) The fixed cost is factored into the total, whereas the variable cost is determined per unit. The
variable cost per unit is 9.56, whereas the fixed cost per unit is 56200 for all units sold and produced.
(c) A minimum of 19380 units must be manufactured to achieve breakeven. It's roughly the number of
units the company sells on an annual basis between July and December.
(d) The company needs sell at least 88345 units in order to make a profit of £200,000. When
compared to the firm's monthly sales, this is a substantial sum. It implies that it is required.
(e) On the sales units of the target profit that must be attained, the margin of safety is 78.06 percent.
(f) Disadvantages of Breakeven Analysis:

• The calculation's precision is determined on the accuracy of the data.
• There was no way of knowing what the relationship was between variable expenses and sales.

REFERENCES
Books and Journals
Hoffmann-Jørgensen, J., 2017. Probability with a view toward statistics. Routledge.
Kallner, A., 2017. Laboratory statistics: methods in chemistry and health sciences. Elsevier.
Park, E. O., Chae, B. and Kwon, J., 2018. Toward understanding the topical structure of
hospitality literature: Applying machine learning and traditional
statistics. International Journal of Contemporary Hospitality Management.
Mattiuzzi, C. and Lippi, G., 2020. Cancer statistics: a comparison between world health
organization (WHO) and global burden of disease (GBD). European journal of public
health, 30(5), pp.1026-1027.
Denis, D. J., 2020. Univariate, bivariate, and multivariate statistics using R: Quantitative
tools for data analysis and data science. John Wiley & Sons.