COM3006NFD
Using Numeracy, Data & IT Portfolio
Student Number:
Module Name:
Submission Deadline:
Word Count: 1881 Words
Section 1:
Total Cost for Tickets to Edinburgh:
Traveling by train is a popular option for many, especially when visiting distant cities like Edinburgh. For a family traveling to Edinburgh, ticket pricing plays a crucial role in budgeting
Total Cost of the Ticket to Edinburgh of Single Adult Ticket = £290
Total Cost of the Ticket to Edinburgh of Single Child Ticket = £100
Calculation of Overall Cost of the 3 Adult Tickets and 2 Child Tickets:
Total Cost of the 3 Adult Tickets and 2 Child Tickets =(3× £ 290)+(2× £ 100)
Total Cost of the 3 Adult Tickets and 2 Child Tickets= £ 870 + £ 200
Total Cost of the 3 Adult Tickets and 2 Child Tickets = £1070
Refund Case for 1 Adult and 1 Child:
In scenarios where plans change, refunds are essential to consider. For our calculations.
Using the total cost from part (a), calculate the refund:
Refund for 1 adult ticket = £290
Refund for 1 child ticket = £100
The refund is:
Refundfor 1 adult ticket and 1 child ticket =£ 290 +£ 100
Refund 1 adult ticket and 1 child ticket = £ 390
After obtaining the refund, the new total cost is computed by subtracting the refund from the original total:
New total cost after considering the refund amount =£ 1070 – £ 390
New total cost after considering the refund amount =£ 680
Family and Friends Railcard for Manchester:
When traveling to Manchester, discounts through railcards can significantly reduce travel expenses. Here are the relevant ticket prices,
Single adult ticket to Manchester = £190
Single child ticket to Manchester = £60
Using the railcard discounts, the prices of tickets are:
1/3 off adult tickets: Discounted amount of adult ticket = £ 190 × 33% = £ 76
60% off child tickets: Discounted amount of child ticket= £ 60 × 60% = £ 36
Price of adult ticket after consideringdiscounted amount of adult ticket=£ 190 - £ 76=£114
Price of Child ticket after considering discounted amount of child ticket =£ 60- £ 36=£ 24
i). Cost of One Adult Ticket = £ 114
Cost of Two Adults Ticket = £ 114 × 2
Cost of Two Adults Ticket = £ 228
ii). Cost of Child Ticket = £ 24
iii). Full ticket price for the family = Cost of Two Adults Ticket + Cost of one Child Ticket
Full ticket price for the family = £ 228 + £ 24
Full ticket price for the family = £ 252
iv). Savings without the railcard:
Full price for the familywithout the railcard discounts =(2×£ 190)+£ 60
Full price for the family without the railcard discounts = £ 440
Full ticket price for the family after using the railcard discounts = £ 252
Savings = Full price for the family without the railcard discounts - Full ticket price for the family after using the railcard discounts
So, the savings are=£ 440 – £ 252
Total Savings after availing the railcard discounts = £ 182
(d) Group Discount: Manchester vs. Leeds.
When considering group travel, comparing ticket prices can yield significant savings. Below are the ticket prices for both destinations,
Total cost of Single adult ticket to Manchester = £190
Total cost of Single adult ticket to Leeds = £ 150
Total cost of Single adult ticket to Manchester (After availing the Group Discount Promotion) = £ 190 x (0.7)
al cost of Single adult ticket to Manchester (After availing the Group Discount Promotion) = £ 133
Total cost of Single adult ticket to Leeds (After Group Discount Promotion) = £ 150 x (0.9)
Total cost of Single adult ticket to Leeds (After Group Discount Promotion) = £ 135
Total cost of 4 Single adult ticket to Manchester (After Group Discount Promotion) = £ 133 x 4
Total cost of 4 Single adult ticket to Manchester (After Group Discount Promotion) = £ 532
Total cost of 4 Single adult ticket to Leeds (After Group Discount Promotion) = £ 135 x 4 = £ 540
Conclusion: The group should travel to Manchester compared to Leedsbecause the tickets prices of the 4 adults is cheaper (£ 532for Manchester vs £ 540 for Leeds).
(e) Price of Birmingham ticket before a 5% increase:
The current price of a Birmingham ticket is £ 60
To find the price before a 5% increase, use the reverse percentage formula:
Original price=Current price+Percentage increase
Original price = 1.05 of Old Ticket Price
Original price=£ 60
Current price?=£ 60/1.05?=£57.14
So, the price last year was approximately £ 57.14
(f) Edinburgh ticket price after 3 years of 5% increases.
The current price of an Edinburgh ticket is assumed to be £290
Use the compound interest formula to calculate the price after 3 years of 5% increases:
Future price=£ 290 ×(1+0.05) ^3
Future price =£ 290 ×1.157625=£ 335.71
So, the price in 3 years will be approximately £ 335.71
Section 2:
Excel
Average Monthly Cost of a SIM-only:
Average monthly cost of a SIM-only is £ 8.2.
Mean cost = £ 82/10 ?=£ 8.2
Median Number of Gigabytes (GB):
i). Median number of Gigabytes (GB) of data on offer by the ten mobile networks:
According to Geeks for Geeks (2024), median is a statistic term which is the middle term of any given set regardless of whether the data are presented in ascending or descending order. The statistics are divided in half by a median. Finding the median provides us with a valuable understanding of the supplied data set. The median is one of the three measures of central tendency.
Step 1: Arranging the data in ascending order:
10,25,30,30,50,60,70,70,140,750
Median Value is the middle value = 50+60/2 = 110/2 = 55
ii). Mean average of Gigabytes (GB) of data on offer by the ten mobile networks:
According to Britannica (2024), mean is also a statistical method used to calculate the average of the values of given set.
Sum of the data values = 1235
Mean Average = 1235/10 = 123.5
iii). Better Indication:
Better Indication would be median value which is 55GB.
iv).The statistical terms mean (or average) and median perform a role that is quite comparable when trying to ascertain the central tendency of a set of statistical scores. While the midpoint of a sample has historically been represented by an average, this approach has the disadvantage of being affected by any single result that is too high or low relative to the sample as a whole. Because of this, a median is sometimes thought to be a more accurate depiction of the middle. The reason of better indication is because the median is less affected by extreme values (Diffen, 2024).
If I were to choose any network out of the two, I would choose ESCOT Network because of its high data offering of 750 GB.
Section 3:
Percentage of recorded offences of shoplifting were groceries in 2022/23
= Groceries Offence shoplifted in the year 2022/23 / Total Offence shoplifted in the year 2022/23
= 2,90,959 / 4,21,679 = 69%
Two major reasons are,
Basic Necessity: Groceries are daily useful product which makes it highly desirable for the people who are facing financial challenges and tough times.
Easy to Shoplift: Groceries are often small and easy to pick up as compared to large category goods.
I). To calculate the ratio, formula to be used,
= Number of homeware items which are shoplifted in the year 2019/20 / Number of electrical items which are shoplifted in the year 2019/20
= 43,002 / 21,501
= 2
II). The ratio between Number of homeware items shoplifted and Number of electrical items shoplifted is 2 which means that the Number of homeware items shoplifted are easy to shoplifted compared with Number of electrical items shoplifted. And the items of electrical are less likely to picked or shoplifted compared with the items of homeware.
Total number of recorded offences between 2013/14 and 2022/23 using excel is find out to be 33,96,491. Formula Used: =Sum(range)
To find this all the values of number of recorded offences between 2013/14 and 2022/23 are added and the figure calculated is the total number of recorded offences.
Minimum number of recorded offences between 2013/14 and 2022/23 using excel is find out to be 2,20,653. Formula Used: =Min(range)
Conditional Formula for counting years with offences greater than 340,000 is:
= COUNTIF (range, ‘>340,000’)
Commenting on the graph presented above, the total offences of shoplifting was constant for three consecutive years with no spikes. Later in couple of years the offences were increased by more than 30%. Furthermore, in the financial year 2020/21, total offences of shoplifting have seen a fall due to COVID-19. And, with re open of shops the offences reached to similar levels which were in Pre COVID period.
Section 4:
I). To show the relationship between sales and advertising expenses, suitable graph should be used is a scatter plot. This will reflect how deviations in advertising expenses correlate with the changes in sales.
II).
III). Considering the above scatter plot, the points are on rise which shows that the correlation between sales and advertising expenses are positive.
IV). When both variables flow in combination, or in the same direction, they are said to have a positive correlation. A positive correlation exists when one factor increases while another rises or declines while the other falls. Positive correlation means that as the advertising costs increase, sales also increase, which suggests that higher the spending on advertising lead to better sales performance.
V). As a manager, the correlation is positive, which means that the increase in advertising expenses seems boosts the sales.
I).
II). The biggest sector of the pie chart does not represent the most popular pet name because the income from these is based on the total number of letters of the name of dog, and longer the name more the income they generate and less frequent they are used.
III). The formula to be used to add up the total number of sales pets whose names begin with the letter J is = COUNTIF (A2:A10, "J*")
The COUNTIF Function counts the quantity of cells that satisfy a certain standard. Excel Statistical functions are the category under which the function falls. The COUNTIF function is very useful in financial analysis. For instance, COUNTIF can be used to tally the instances in which a salesperson surpasses their goal (Corporate Finance Institute, 2024).
IV). The formula to be used to find the average income from sales from those pets whose names have more than 4 letters is = AVERAGEIF (A2:A10, ">4", B2:B10)
This is also a statistical function used in excel to average the range of cells based on a particular criterion (Corporate Finance Institute, 2024a).
References:
Britannica (2024) Mean. Available at: https://www.britannica.com/science/mean(Accessed on: 8 October 2024).
Corporate Finance Institute (2024) COUNTIF Function. Available at:https://corporatefinanceinstitute.com/resources/excel/countif-function/(Accessed on: 7 October 2024).
Corporate Finance Institute (2024a) AVERAGEIF Function. Available at:https://corporatefinanceinstitute.com/resources/excel/averageif-function/#:~:text=The%20AVERAGEIF%20Function%5B1%5D%20is,numbers%20in%20a%20statistical%20distribution. (Accessed on: 8 October 2024).
Diffen (2024)Mean vs. Median. Available at:https://www.diffen.com/difference/Mean_vs_Median(Accessed on: 8 October 2024).
Geeks for Geeks (2024) Median in Statistics. Available at: https://www.geeksforgeeks.org/median/(Accessed on: 7 October 2024).
