Description
The Consultative Report (per team) must be in the
range of 2,000 words in length excluding references.
You must reference using the APA referencing style.
Reflective Piece: A further 300 – 500 words reflection
paper should be submitted per student.
A. on your experience and learning in this exercise
and how this may be applied in your own organisation.
Unformatted Attachment Preview
sl
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Quarter
Sep-16 $
Dec-16 $
Mar-17 $
Jun-17 $
Sep-17 $
Dec-17 $
Mar-18 $
Jun-18 $
Sep-18 $
Dec-18 $
Mar-19 $
Jun-19 $
Sep-19 $
Dec-19 $
Mar-20 $
Jun-20 $
Sep-20 $
Dec-20 $
Mar-21 $
Jun-21 $
Sep-21 $
Dec-21 $
Mar-22 $
Jun-22 $
Sep-22 $
Revenue
Research and Development
Expenses
Operational Expenses
3,290,188.00
3,477,541.00
3,636,635.00
3,785,464.00
3,984,859.00
4,285,755.00
4,700,856.00
4,907,270.00
4,999,374.00
5,186,841.00
5,520,992.00
5,923,116.00
6,244,905.00
6,467,434.00
6,767,691.00
7,148,286.00
7,435,637.00
7,644,442.00
8,163,282.00
8,341,777.00
8,483,467.00
8,709,318.00
8,867,767.00
9,188,384.02
9,451,995.60
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
297,506.00
305,912.00
333,871.00
342,484.00
330,223.00
347,132.00
382,310.00
399,095.00
408,620.00
431,789.00
472,764.00
483,233.00
479,776.00
509,376.00
553,817.00
535,045.00
553,802.00
586,936.00
625,207.00
637,321.00
663,887.00
747,470.00
757,530.00
719,086.55
738,210.38
91,861.00
93,532.00
108,943.00
122,317.00
117,324.00
122,459.00
144,612.00
161,524.00
178,628.00
185,530.00
211,952.00
234,657.00
243,174.00
264,586.00
262,087.00
287,236.00
281,624.00
285,539.00
307,196.00
344,845.00
331,790.00
407,790.00
407,928.00
390,525.20
404,050.51
Total Number of
Employees
Total Number of Paid
Memberships
5,339.76
5,700.00
5,888.37
6,084.29
6,288.06
6,500.00
6,903.41
7,336.40
7,801.16
8,300.00
8,605.31
8,923.38
9,254.76
9,600.00
9,793.38
9,991.11
10,193.28
10,400.00
10,842.73
11,306.31
11,791.72
12,300.00
12,832.22
12,721.29
13,048.51
93,280.00
99,090.00
104,360.00
109,040.00
114,020.00
120,640.00
128,900.00
134,350.00
140,420.00
149,260.00
158,860.00
161,560.00
168,330.00
177,090.00
192,860.00
202,950.00
205,150.00
213,660.00
217,640.00
219,180.00
223,560.00
231,840.00
231,640.00
246,223.60
252,932.22
Revenue
Mean
Standard Error
Median
Mode
Standard Deviation
Sample Variance
Kurtosis
Skewness
Range
Minimum
Maximum
Sum
Count
Confidence Level(95.0%)
Research an
6264531.065
398235.1298
6244905
#N/A
1991175.649
3.96478E+12
-1.372044597
0.059877942
6161807.6
3290188
9451995.6
156613276.6
25
821916.9116
Research and Development Expenses
Mean
505696.1171
Standard Error
29701.21813
Median
483233
Mode
#N/A
Standard Deviation
148506.0906
Sample Variance
22054058959
Kurtosis
-1.133171708
Skewness
0.288192577
Range
460024
Minimum
297506
Maximum
757530
Sum
12642402.93
Count
25
Confidence Level(95.0%) 61300.30137
Operational Expenses
Mean
Standard Error
Median
Mode
Standard Deviation
Sample Variance
Kurtosis
Skewness
Range
Minimum
Maximum
Sum
Count
Confidence Level(95.0%)
239668.3884
20939.94427
243174
#N/A
104699.7213
10962031647
-1.167072438
0.180073029
316067
91861
407928
5991709.711
25
43217.92085
Total Number of Employees
Mean
Standard Error
Median
Mode
Standard Deviation
Sample Variance
Kurtosis
Skewness
Range
Minimum
Maximum
Sum
Count
Confidence Level(95.0%)
Number of Employees
9109.81778
487.8003651
9254.761947
#N/A
2439.001825
5948729.904
-1.190233755
0.056374707
7708.753803
5339.761007
13048.51481
227745.4445
25
1006.770472
Total Number of Paid Memberships
Mean
Standard Error
Median
Mode
Standard Deviation
Sample Variance
Kurtosis
Skewness
Range
Minimum
Maximum
Sum
Count
Confidence Level(95.0%)
171873.4326
10128.62044
168330
#N/A
50643.1022
2564723800
-1.391307965
-0.0249023
159652.2154
93280
252932.2154
4296835.815
25
20904.44516
Metrics
Total
Revenue
$ 156,613,276.62 $
Research and Development Expenses
$
12,642,402.93 $
Operational Expenses
$
5,991,709.71 $
Total Number of Employees
227,745
Total Number of Paid Memberships
4,296,836
Operating Profit
$ 137,979,163.98
Mean
6,264,531.06
505,696.12
239,668.39
9,110
171,873
$
$
$
Median
6,244,905.00
483,233.00
243,174.00
9,255
168,330
Task: increase revenue by 20%
Current revenue:
$
Target revenue:
$
9,451,995.60
11,342,394.72
Model 1 – Increase Operational expenses
General and administrative expenses
Intercept
Operational Expenses
$
Revenue
$
1,763,636.47
18.78 $
510,060
11,342,395
510,060 in general expenses to increase revenue by 20%
$
Model 2 – Increase total number of employees
Total number of employees
Intercept
Total number of employees
-$
Revenue
$
1,123,137.66
810.96
15,371.39
11,342,395
15,371 employees to increase revenue by 20%
Model 3 – Increase total number of paid memberships
Total number of paid memberships
Intercept
Total number of paid
memberships
-$
Revenue
$
470,814.47
39.19
301,451.02
11,342,395
301,451 memberships required to increase revenue by 20%
Model 4 – Increase R & D development expenses and total number of paid memberships
Technology and development
expenses
Intercept
Research and Development
expenses
Number of memberships
-$
Revenue
$
$
2,041,682
509,773.23 $
505,696.12
3.31
29.69
16,420,258 $
in technology and
development expenses and
2,041,682.35
342,917
mberships
Number of
membersh
ips
171,873 Mean
342,917
total paid
membersh
ips
required
Millions
Revenue ($) by Quarter
$10.00
$9.00
$8.00
Revenue
$7.00
$6.00
$5.00
$4.00
$3.00
$2.00
$1.00
Jun-22
Mar-22
Dec-21
Sep-21
Jun-21
Mar-21
Dec-20
Sep-20
Jun-20
Mar-20
Dec-19
Sep-19
Jun-19
Mar-19
Dec-18
Sep-18
Jun-18
Mar-18
Dec-17
Sep-17
Jun-17
Mar-17
Dec-16
Sep-16
$-
Quarter
Correlations with Revenue
$700.00
$600.00
$500.00
y = 0.0736x + 44776
R² = 0.9732
$400.00
$300.00
$200.00
$100.00
Thousands
$800.00
Operational Expences
Thousands
Research and Development Expences
Research and Development Expenses
$450.00
$400.00
$350.00
$300.00
$250.00
$200.00
$150.00
$100.00
$-
Thousands
Revenue
14,000.00
12,000.00
10,000.00
8,000.00
6,000.00
4,000.00
2,000.00
–
y = 0.0012x + 1487.4
R² = 0.9867
Total Number of Paid Memberships
Total Number of Employees
Total Number of Employees
300,000.00
250,000.00
200,000.00
150,000.00
100,000.00
50,000.00
Total Number
Revenue
Thousands
Sep-22
Jun-22
Thousands
Operational Expenses ($)
$450.00
$400.00
$350.00
y = 0.0519x – 85605
R² = 0.9751
$300.00
$250.00
$200.00
$150.00
$100.00
$50.00
$-
Thousands
Revenue
Total Number of Paid Memberships
300,000.00
250,000.00
y = 0.0253x + 13070
R² = 0.9934
200,000.00
150,000.00
100,000.00
50,000.00
–
Thousands
Thousands
Revenue
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
Revenue ($)
3,290,188.00
3,477,541.00
3,636,635.00
3,785,464.00
3,984,859.00
4,285,755.00
4,700,856.00
4,907,270.00
4,999,374.00
5,186,841.00
5,520,992.00
5,923,116.00
6,244,905.00
6,467,434.00
6,767,691.00
7,148,286.00
7,435,637.00
7,644,442.00
8,163,282.00
8,341,777.00
8,483,467.00
8,709,318.00
8,867,767.00
9,188,384.02
9,451,995.60
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
Operational Expenses
91,861.00
93,532.00
108,943.00
122,317.00
117,324.00
122,459.00
144,612.00
161,524.00
178,628.00
185,530.00
211,952.00
234,657.00
243,174.00
264,586.00
262,087.00
287,236.00
281,624.00
285,539.00
307,196.00
344,845.00
331,790.00
407,790.00
407,928.00
390,525.20
404,050.51
Predicted Revenue ($)
Residuals
$
3,488,756.26 -$ 198,568.26
$
3,520,137.09 -$
42,596.09
$
3,809,550.68 -$ 172,915.68
$
4,060,710.06 -$ 275,246.06
$
3,966,943.14 $
17,915.86
$
4,063,376.77 $ 222,378.23
$
4,479,402.92 $ 221,453.08
$
4,797,004.80 $ 110,265.20
$
5,118,212.37 -$ 118,838.37
$
5,247,829.69 -$
60,988.69
$
5,744,026.28 -$ 223,034.28
$
6,170,418.82 -$ 247,302.82
$
6,330,365.31 -$
85,460.31
$
6,732,475.73 -$ 265,041.73
$
6,685,545.32 $
82,145.68
$
7,157,835.38 -$
9,549.38
$
7,052,443.84 $ 383,193.16
$
7,125,966.27 $ 518,475.73
$
7,532,677.71 $ 630,604.29
$
8,239,713.71 $ 102,063.29
$
7,994,545.05 $ 488,921.95
$
9,421,800.39 -$ 712,482.39
$
9,424,391.99 -$ 556,624.99
$
9,097,573.05 $
90,810.97
$
9,351,574.00 $ 100,421.60
Regression Analysis
Co-efficients
$
Operational Expenses
18.78 $
Intercept
1,763,636.47
Revenue vs Predicted Revenue – Model 1
$10,000,000.00
$9,000,000.00
$8,000,000.00
$
$7,000,000.00
$6,000,000.00
$5,000,000.00
$4,000,000.00
$3,000,000.00
$2,000,000.00
$1,000,000.00
$1 2 3 4 5 6 7 8 9 10111213141516171819202122232425
Revenue ($)
Predicted Revenue ($)
Axis Title
Axis Title
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
Revenue ($)
3,290,188.00
3,477,541.00
3,636,635.00
3,785,464.00
3,984,859.00
4,285,755.00
4,700,856.00
4,907,270.00
4,999,374.00
5,186,841.00
5,520,992.00
5,923,116.00
6,244,905.00
6,467,434.00
6,767,691.00
7,148,286.00
7,435,637.00
7,644,442.00
8,163,282.00
8,341,777.00
8,483,467.00
8,709,318.00
8,867,767.00
9,188,384.02
9,451,995.60
Total Number of Employees
5,339.76
5,700.00
5,888.37
6,084.29
6,288.06
6,500.00
6,903.41
7,336.40
7,801.16
8,300.00
8,605.31
8,923.38
9,254.76
9,600.00
9,793.38
9,991.11
10,193.28
10,400.00
10,842.73
11,306.31
11,791.72
12,300.00
12,832.22
12,721.29
13,048.51
Predicted Revenue ($)
Residuals
$
3,207,177.87 $
83,010.13
$
3,499,316.13 -$
21,775.13
$
3,652,076.02 -$
15,441.02
$
3,810,958.34 -$
25,494.34
$
3,976,208.45 $
8,650.55
$
4,148,081.57 $ 137,673.43
$
4,475,227.30 $ 225,628.70
$
4,826,368.09 $
80,901.91
$
5,203,263.92 -$ 203,889.92
$
5,607,803.82 -$ 420,962.82
$
5,855,394.73 -$ 334,402.73
$
6,113,340.61 -$ 190,224.61
$
6,382,074.52 -$ 137,169.52
$
6,662,047.67 -$ 194,613.67
$
6,818,869.85 -$
51,178.85
$
6,979,218.33 $ 169,067.67
$
7,143,172.39 $ 292,464.61
$
7,310,813.12 $ 333,628.88
$
7,669,846.72 $ 493,435.28
$
8,045,790.36 $ 295,986.64
$
8,439,440.49 $
44,026.51
$
8,851,631.05 -$ 142,313.05
$
9,283,235.27 -$ 415,468.27
$
9,193,275.77 -$
4,891.75
$
9,458,644.23 -$
6,648.63
Regression Analysis
Total Number of Employees
$
810.96 -$
Co-efficients
Intercept
1,123,137.66
Revenue vs Predicted Revenue – Model 2
$10,000,000.00
$
$9,000,000.00
$8,000,000.00
$7,000,000.00
$6,000,000.00
$5,000,000.00
$4,000,000.00
$3,000,000.00
$2,000,000.00
$1,000,000.00
$1
3
5
7
9
11 13 15 17 19 21 23 25
Revenue ($)
Predicted Revenue ($)
Axis Title
Axis Title
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
Revenue ($) Total Number of Paid MembershipsPredicted Revenue ($)
Residuals
3,290,188.00 $
93,280.00 $
3,184,625.60 $ 105,562.40
3,477,541.00 $
99,090.00 $
3,412,306.84 $
65,234.16
3,636,635.00 $
104,360.00 $
3,618,826.67 $
17,808.33
3,785,464.00 $
109,040.00 $
3,802,225.67 -$
16,761.67
3,984,859.00 $
114,020.00 $
3,997,381.03 -$
12,522.03
4,285,755.00 $
120,640.00 $
4,256,804.41 $
28,950.59
4,700,856.00 $
128,900.00 $
4,580,495.82 $ 120,360.18
4,907,270.00 $
134,350.00 $
4,794,069.46 $ 113,200.54
4,999,374.00 $
140,420.00 $
5,031,939.54 -$
32,565.54
5,186,841.00 $
149,260.00 $
5,378,359.89 -$ 191,518.89
5,520,992.00 $
158,860.00 $
5,754,562.98 -$ 233,570.98
5,923,116.00 $
161,560.00 $
5,860,370.10 $
62,745.90
6,244,905.00 $
168,330.00 $
6,125,671.66 $ 119,233.34
6,467,434.00 $
177,090.00 $
6,468,956.98 -$
1,522.98
6,767,691.00 $
192,860.00 $
7,086,948.94 -$ 319,257.94
7,148,286.00 $
202,950.00 $
7,482,354.06 -$ 334,068.06
7,435,637.00 $
205,150.00 $
7,568,567.27 -$ 132,930.27
7,644,442.00 $
213,660.00 $
7,902,055.64 -$ 257,613.64
8,163,282.00 $
217,640.00 $
8,058,023.17 $ 105,258.83
8,341,777.00 $
219,180.00 $
8,118,372.42 $ 223,404.58
8,483,467.00 $
223,560.00 $
8,290,015.08 $ 193,451.92
8,709,318.00 $
231,840.00 $
8,614,490.25 $
94,827.75
8,867,767.00 $
231,640.00 $
8,606,652.69 $ 261,114.31
9,188,384.02 $
246,223.60 $
9,178,152.21 $
10,231.81
9,451,995.60 $
252,932.22 $
9,441,048.24 $
10,947.36
Regression Analysis
Total Number of Paid Memberships
$
39.19 -$
Co-efficients
Intercept
470,814.47
$
Revenue vs Predicted Revenue – Model 3
$10,000,000.00
$9,000,000.00
$8,000,000.00
$7,000,000.00
$6,000,000.00
$5,000,000.00
$4,000,000.00
$3,000,000.00
$2,000,000.00
$1,000,000.00
$1
3
5
7
9
11 13 15 17 19 21 23 25
Revenue ($)
Predicted Revenue ($)
Axis Title
Axis Title
Revenue ($)
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
3,290,188.00
3,477,541.00
3,636,635.00
3,785,464.00
3,984,859.00
4,285,755.00
4,700,856.00
4,907,270.00
4,999,374.00
5,186,841.00
5,520,992.00
5,923,116.00
6,244,905.00
6,467,434.00
6,767,691.00
7,148,286.00
7,435,637.00
7,644,442.00
8,163,282.00
8,341,777.00
8,483,467.00
8,709,318.00
8,867,767.00
9,188,384.02
9,451,995.60
Research and Development
Expenses
$
297,506.00
$
305,912.00
$
333,871.00
$
342,484.00
$
330,223.00
$
347,132.00
$
382,310.00
$
399,095.00
$
408,620.00
$
431,789.00
$
472,764.00
$
483,233.00
$
479,776.00
$
509,376.00
$
553,817.00
$
535,045.00
$
553,802.00
$
586,936.00
$
625,207.00
$
637,321.00
$
663,887.00
$
747,470.00
$
757,530.00
$
719,086.55
$
738,210.38
Total Number of Paid
Memberships
93,280.00
99,090.00
104,360.00
109,040.00
114,020.00
120,640.00
128,900.00
134,350.00
140,420.00
149,260.00
158,860.00
161,560.00
168,330.00
177,090.00
192,860.00
202,950.00
205,150.00
213,660.00
217,640.00
219,180.00
223,560.00
231,840.00
231,640.00
246,223.60
252,932.22
Predicted Revenue
($)
$
3,243,019.36
$
3,443,293.92
$
3,692,178.03
$
3,859,589.93
$
3,966,900.12
$
4,219,331.95
$
4,580,847.47
$
4,798,134.88
$
5,009,827.54
$
5,348,860.95
$
5,769,322.32
$
5,884,088.60
$
6,073,644.98
$
6,431,563.82
$
7,046,655.98
$
7,284,144.84
$
7,411,466.88
$
7,773,646.97
$
8,018,324.85
$
8,104,091.78
$
8,321,948.77
$
8,844,081.61
$
8,871,401.75
$
9,177,262.33
$
9,439,647.00
$
$
-$
-$
$
$
$
$
-$
-$
-$
$
$
$
-$
-$
$
-$
$
$
$
-$
-$
$
$
47,168.64
34,247.08
55,543.03
74,125.93
17,958.88
66,423.05
120,008.53
109,135.12
10,453.54
162,019.95
248,330.32
39,027.40
171,260.02
35,870.18
278,964.98
135,858.84
24,170.12
129,204.97
144,957.15
237,685.22
161,518.23
134,763.61
3,634.75
11,121.69
12,348.60
Regression Analysis
Total Number of Paid Memberships
$
29.69
Co-efficients
Revenue vs Predicted Revenue
$
Residuals
$10,000,000.00
$9,000,000.00
$8,000,000.00
$7,000,000.00
$6,000,000.00
$5,000,000.00
$4,000,000.00
$3,000,000.00
$2,000,000.00
$1,000,000.00
$1
3
5
7
9 11 13 15 17 19
Revenue ($)
Predicted Revenue ($)
Research and Development Expenses Intercept
$
3.31 -$ 509,773.23
icted Revenue – Model 4
19 21 23 25
Predicted Revenue ($)
Axis Title
Individual Case Study of Target Group – Australia
MBA5004
Managing Decision Making Processes
Assessment 2
Student Name
: S A D S Nathasha Senaratna
Student ID
: 55647
Company
: Target Group
Lecturer
: Dr Priyantha Bandara
Submission Date
: 17th of October 2023
Executive Summary
The primary objective of this initiative was to create a versatile data analysis platform that could
support Target’s strategic goals. Our specific target was to achieve a 20% increase in revenue.
The initial step was selecting and preparing the data that would drive the decision-making
process. An array of variables, including Research and Development Expenses, Operational
Expenses, Total Number of Employees, and Total Number of Memberships, were considered to
enable precise adjustments aimed at achieving the target. The heart of our platform lies in a
robust regression model, thoughtfully constructed within Microsoft Excel. This model, once
provided with the current revenue data, calculates the adjusted values of the relevant variables,
paving the way for informed decision-making.
While our data analysis platform is a powerful tool, it does come with certain limitations. It is
optimized to handle a maximum of 25 entries for Target. Moreover, its accuracy is contingent
upon the quality and reliability of the input data. Therefore, maintaining accurate and up-to-date
data is paramount.
In summary, the development of this data analysis platform empowers our decision-makers with
the insights needed to drive 20% increase in revenue task while providing a comprehensive view
of our organization’s current status.
1
Table of Contents
Executive Summary …………………………………………………………………………………………………………. 1
1.0 Introduction ……………………………………………………………………………………………………………… 3
Aims and Objectives …………………………………………………………………………………………………….. 3
Scope of the report ……………………………………………………………………………………………………… 3
2.0 Methodology and Data selection …………………………………………………………………………………. 4
3.0 Data Analysis and Interpretation …………………………………………………………………………………. 6
4.0 Development of MS Excel Platform ……………………………………………………………………………. 10
4.1 Datasheet ……………………………………………………………………………………………………………. 10
4.2 Graphical Interpretation ………………………………………………………………………………………… 10
4.3 Regression model for Model 1 – Operational Expenses …………………………………………….. 11
4.4 Regression model for Model 2 – Total Number of Employees…………………………………….. 13
4.5 Regression model for Model 3 – Total Number of Paid Memberships …………………………. 14
4.6 Regression model for Model 4 – Research and Development Expenses and Total Number of
Paid Memberships ……………………………………………………………………………………………………… 15
4.7 Models ………………………………………………………………………………………………………………… 16
5.0 Decision Making Outcomes ………………………………………………………………………………………. 19
Conclusion ……………………………………………………………………………………………………………………. 20
References ……………………………………………………………………………………………………………………. 21
2
1.0 Introduction
The primary objective of this initiative was to create a versatile data analysis platform that could
support Target’s strategic goals. Our specific target was to achieve a 20% increase in revenue.
The objective of this report is twofold: firstly, to provide an account of how this data analysis
platform was created, including insights into data selection, preparation, and analysis techniques;
and secondly, to illustrate the practical application of this platform in addressing one Target’s most
pressing goals.
In the following sections, the methodology employed to build this platform within Microsoft Excel
will be delivered, explore the data set selected for analysis, justify the choice of this specific data,
and elucidate the types of data analysis and interpretation employed to extract meaningful
insights.
The decisions made within this assessment revolve around the key objective of Target, which is
20% increase in revenue. Each decision will be meticulously analyzed, and recommendations
provided, ensuring that the management team is well-equipped with the data and insights
required for effective decision-making.
Aims and Objectives
The purpose of this research is to examine Target’s existing marketing strategy, identify areas for
improvement, and make concrete recommendations to strengthen our market presence and
competitiveness. The goals include conducting a thorough review of our existing marketing
campaigns, analyzing their effectiveness through data analysis, and leveraging this analysis to
develop targeted marketing strategies aimed at increasing brand visibility, customer engagement,
and, ultimately, revenue growth.
Scope of the report
The scope of this study is to give a thorough examination of Target Corporation’s financial and
operational strategies in the context of attaining a 20% revenue increase from its present revenue
of $9,451,995.60. To provide specific suggestions, the paper employs four separate models: by
focuses on reducing operational expenses , Model 2 discusses about the workforce, Model 3
emphasizes operational expenses and Model 4 emphasizes on technology and development
expenses and the goal of increasing total paid memberships This study is intended to assist Target
in making decisions that will improve revenue growth and overall financial performance.
3
2.0 Methodology and Data selection
The data was gathered quarterly, considering the period of September 2016 to September 2022.
The data contained Revenue in AUD, Research & Development Expenses in AUD, Operational
Expenses in AUD, Total number of Employees, and Total number of paid memberships. The
considered data set is shown in Table 2.1
Table 2.1: Dataset
For the analysis, following computational equations were used in calculations.
= – –
Operating Profit
= $ 156,613,276.62 – $ 12,642,402.93 – $ 5,991,709.71
= $ 137,979,163.98
And also, the mean, median was calculated as follows to identify the characteristics of the
dataset.
4
Table 2.2: Initial Analysis
Metrics
Revenue
Research and Development Expenses
Operational Expenses
Total Number of Employees
Total Number of Paid Memberships
Operating Profit
$
$
$
$
Total
156,613,276.62 $
12,642,402.93 $
5,991,709.71 $
227,745
4,296,836
137,979,163.98
Mean
6,264,531.06 $
505,696.12 $
239,668.39 $
9,110
171,873
Median
6,244,905.00
483,233.00
243,174.00
9,255
168,330
5
3.0 Data Analysis and Interpretation
An initial Data Analysis was conducted to have a brief idea of the status of the organization.
Sep-22
Jun-22
Mar-22
Sep-21
Dec-21
Jun-21
Mar-21
Dec-20
Jun-20
Sep-20
Mar-20
Dec-19
Sep-19
Jun-19
Mar-19
Dec-18
Sep-18
Jun-18
Dec-17
Mar-18
Sep-17
Jun-17
Mar-17
Dec-16
$10.00
$9.00
$8.00
$7.00
$6.00
$5.00
$4.00
$3.00
$2.00
$1.00
$-
Sep-16
Revenue
Millions
Revenue ($) by Quarter
Quarter
Figure 3.1: Revenue by Quater
According to figure 3.1, the revenue has increased the over the time.
And, as the ultimate objective of the model is to increase the revenue by 20%, it is much
needed to check the variation of the variables with the revenue. Thus, the correlations were
plotted and analyzed as follows.
Thousands
Research and Development Expences
Research and Development Expenses
$800.00
$700.00
$600.00
$500.00
y = 0.0736x + 44776
R² = 0.9732
$400.00
$300.00
$200.00
$100.00
$-
Thousands
Revenue
Figure 3.2: Correlation between Revenue ($) and the Research and Development Expenses ($)
6
As a result, the An R-squared value of 0.9732 indicates that variations in Research and
Development Expenses ($) can explain roughly 97.32% of the variability in Revenue ($). In
layman’s words, this suggests that these two variables have a strong and positive link. In practice,
this means that changes in Research and Development Expenses ($) are a strong predictor of
changes in Revenue ($) in the dataset given. This data can be useful for making business choices
since it reveals that spending more in R&D may result in higher revenues, while cutting such costs
may result in fewer revenues, depending on the type of the data and the context of your study.
Thousands
Operational Expences
Operational Expenses ($)
$450.00
$400.00
$350.00
y = 0.0519x – 85605
R² = 0.9751
$300.00
$250.00
$200.00
$150.00
$100.00
$50.00
$-
Thousands
Revenue
Figure 3.3: Correlation between Revenue ($) and the Operational Expenses ($)
An R-squared (R²) value of 0.9751 suggests an exceptionally strong positive correlation between
Revenue ($) and Operational Expenses. This indicates that operational expenses significantly
influence and explain approximately 97.51% of the variation in revenue.
7
Total Number of Employees
Total Number of Employees
14,000.00
y = 0.0012x + 1487.4
R² = 0.9867
12,000.00
10,000.00
8,000.00
6,000.00
4,000.00
2,000.00
–
Thousands
Revenue
Figure 3.4: Correlation between Revenue ($) and Total Number of Employees
The correlation research demonstrates a remarkably significant and positive relationship
between Revenue ($) and Total Employees (R2 = 0.9867). This suggests that the total number of
workers accounts for about 98.67% of the variation in revenue. In practice, changes in worker
size have a significant impact on revenue outcomes.
Total Number of Paid Memberships
Total Number of Paid Memberships
300,000.00
250,000.00
y = 0.0253x + 13070
R² = 0.9934
200,000.00
150,000.00
100,000.00
50,000.00
–
Thousands
Revenue
Figure 3.5: Correlation between Revenue ($) and Total Number of Paid Memberships
8
The exceptionally (R²) value of 99.34% indicates an extremely strong and positive correlation
between Revenue ($) and the Total Number of Paid Memberships. This suggests that the number
of paid memberships has a profound and almost complete explanatory power, accounting for
approximately 99.34% of the variability in revenue.
Thus, this can be observed that there’s a high positive correlation between the revenue and the
all the other variables (Research and Development Expenses, Operational Expenses, Total
Number of Employees and Total Number of Memberships).
9
4.0 Development of MS Excel Platform
The tool was developed using Microsoft 365. The tool contains following pages Datasheet,
Models, Graphical Representations, Regression model for model 1, Regression model for model
2, Regression model for model 3, and Regression model for model 4. In the chapter it is shown
how the tool is built in the Excel platform.
4.1 Datasheet
Figure 4.1: Datasheet Interface
In here the organization update this table by inserting their data. When it is inserted the graphical
representations and regression model automatically change. linear regression, the intercept
coefficient is an integral part of the equation that defines the relationship between variables. It
represents the initial value of the dependent variable when all independent variables are zero,
while regression as a whole helps quantify how changes in independent variables affect the
dependent variable. In order to get the date calculated automatically we have used LINEST
function if excel to calculate the intercept and co-efficient in regression equation.
4.2 Graphical Interpretation
In the graphical interpretation sheet, if interprets Time vs Revenue plot as well as the correlations
of variables with Revenue plots. These help the decision maker to have and overall idea about
their organization’s current status.
10
Figure 4.2: Time vs Revenue plot
Figure 4.3: Correlation plots
4.3 Regression model for Model 1 – Operational Expenses
In here it is shown the Regression Analysis for the model 1. These values are automatically
updated with any change of the datasheet and calculates the respective coefficients and
residuals. By using the LINEST option on excel.
11
Figure 4.4: Regression Analysis for Model 1
12
4.4 Regression model for Model 2 – Total Number of Employees
In here it is shown the Regression Analysis for the model 2. These values are automatically
updated with any change of the datasheet and calculates the respective coefficients and
residuals.
Figure 4.5: Regression Analysis for Model 2
13
4.5 Regression model for Model 3 – Total Number of Paid Memberships
In here it is shown the Regression Analysis for the model 3. These values do automatically updated
with any change of the datasheet and calculates the respective coefficients and residuals.
Figure 4.6: Regression Analysis for Model 3
14
4.6 Regression model for Model 4 – Research and Development Expenses and Total Number of
Paid Memberships
In here it is shown the Regression Analysis for the model 4. These values do automatically updated
with any change of the datasheet and calculates the respective coefficients and residuals.
Figure 4.7: Regression Analysis for Model 4
15
4.7 Models
In the model there are 4 models which adjust automatically when the datasheet gets updated.
The user must input their current revenue. With that input tool calculated and display the
outcome values of the variables which need to be changed in order to achieve the task.
Figure 4.8: User inputs
Figure 4.9: Model 1 Interface
Using the interception and the coefficient calculated in Figure 4.4 are as follows.
Operational expenses coefficient = $ 18.78
Intercept = $ 1,763,636.47
Using that, the regression model for revenue can be built as,
Revenue = $ 1,763,636.47 + $ 18.78 x Operational Expenses
When calculating the required Operational Expense to increase revenue up to $ 9,451,995.60,
$ 9,451,995.60 = $ 1,763,636.47 + $ 18.78 x Required Operational Expenses
Thus, the required operational expenses = $ 510,060
16
Figure4.10: Model 2 Interface
Using the interception and the coefficient calculated in Figure 4.5 are as follows.
Total number of employees coefficient = 810.957
Intercept = – $ 1,123,137.66
Using that, the regression model for revenue can be built as,
Revenue = – $ 1,123,137.66 + 810.957x Total number of employees
When calculating the required Total number of employees to increase revenue up to $
9,451,995.60,
$ 9,451,995.60 = – $ 1,123,137.66 + 810.957x Total number of employees
Thus, the required Total number of employees = 15,371
Figure 4.11: Model 3 Interface
Using the interception and the coefficient calculated in Figure 4.6 are as follows.
Total number of paid memberships coefficient = 39.188
Intercept = – $ 470,814.47
17
Using that, the regression model for revenue can be built as,
Revenue = – $ 470,814.47+ 39.188 x Total number of paid memberships
When calculating the required Total number of paid memberships to increase revenue up to $
9,451,995.60,
$ 9,451,995.60 = – $ 470,814.47 + 39.188x Required Total number of paid memberships
Thus, the required Total number of paid memberships = 301,451
Figure 4.12: Model 4 Interface
Using the interception and the coefficient calculated in Figure 4.7 are as follows.
Number of memberships coefficient = $29.69
Technology and development expenses coefficient = $3.31
Intercept = – $ 509,773.23
Using that, the regression model for revenue can be built as,
Revenue = -$509,773.23 + 3.31 x Research & Development Expenses + 29.69 x Number of
Memberships
When calculating the required amounts to increase revenue up to $ 9,451,995.60,
$11,342,394.72 = -$509,773.23 + 3.31 x Research & Development Expenses + 29.69 x Number
of Memberships
Where; mean of Research & Development Expenses and Number of Memberships are
$505,696.12 and 171,873 respectively. (Table 2.2).
18
5.0 Decision Making Outcomes
Thus, according to the model sheet , the results are shown to the user. By considering this the
user can make decisions on behalf of their organization. For example, as the current revenue of
Target is $ 9,451,995.60, expected revenue should be $ 11,342,394.72 when the revenue
increases by 20%. When the user inputs the current revenue into the model. It outputs as follows.
To increase the revenue by 20%,
Model 1: Operational Expenses should be $ 510,060, suggests saving $510,060 in operating
expenditures by optimizing supply chain logistics, simplifying retail operations, or discovering
cost-effective procurement alternatives.
Model 2: Total number of Employees should be 15,371, indicates that Target may need to
consider increasing its workforce to 15,371 individuals to support revenue growth, highlighting
prospective recruiting requirements in various categories such as sales associates, warehouse
personnel, or technology specialists.
Model 3: Operational Expenses should be $301,451, which 3 recommends a $301,451 decrease
in operational expenditures, highlighting the need of cost control and efficiency improvements in
areas such as inventory management and retail operations.
Model 4: Technology and development expenses should be = $ 2,041,682 & total paid
memberships = 342,917. emphasizes the need to invest in technology and development costs,
notably a $2,041,682 increase. This might include improving Target’s e-commerce platform,
establishing novel consumer interaction techniques, and remaining competitive in the everchanging retail industry. It also advises increasing total paid memberships to 342,917, which may
be accomplished through Target’s reward programs, marketing campaigns, or customer retention
measures.
19
Conclusion
In conclusion, this model sheet offers the user with significant insights and suggestions relevant
to Target Corporation, allowing them to make educated decisions on behalf of the business. Using
Target’s current sales of $9,451,995.60 as a starting point, the model offers a strategy for
increasing revenue by 20% to $11,342,394.72. Target may use these ideas to help it meet its sales
target and make strategic decisions that will lead to long-term development and success in the
highly competitive retail business.
20
References
1. Orlov, M. L. (1996). Multiple linear regression analysis using Microsoft Excel. Chemistry
Department.
2. K
