Practical Assessment Paper
Semester IV, Business Statistics
Section C and N
Instructions:
Attempt all the questions.
You must submit the Excel sheet which should be saved with your Class Roll Number and Name
(Example: 0123_Ganesh) no later than 8:00 PM.
Question:1. From the following information regarding 5 commodities
Commodity Unit Price Quantity
2022 2023 2022 2023
P Kg 25 28 150 105
Q Metre 160 200 48 30
R Sq. ft. 50 50 125 160
S Kg 40 32 88 166
T Litre 16 24 140 76
You are required to compute the following weighted aggregative price and quantity index
numbers for the year 2023 using Excel
a) Laspeyres’ Price and Quantity Index
b) Paasche’s Price and Quantity Index
c) Fisher’s Ideal Index,
Also Verify that the Fisher Index satisfies both the Time Reversal and Factor Reversal Test.
Question:2. Using the following data for variables X and Y, calculate the following in Excel:
Observation X Y
1 5 8
2 7 11
3 9 14
4 6 10
5 8 13
6 10 16
7 11 17
8 13 20
9 12 18
10 14 22
11 15 24
12 16 26
13 18 28
14 17 27
15 19 30
a) Calculate the Covariance between X and Y.
b) Calculate the Correlation Coefficient between X and Y.
c) Analyse the relationship between X and Y based on your results.
Question:3. The following data shows the sales (in units) of a company over 15 months. Using Excel,
perform the following tasks:
Month Sales
1 220
2 230
3 250
4 270
5 260
6 275
7 290
8 300
9 310
10 320
11 330
12 340
13 355
14 360
15 375
a) Compute the Regression Line Equation of Sales (Y) on Time (X). (i.e., find the regression
coefficients a (intercept) and b (slope))
b) Predict the sales for Month 20 using the regression equation.
c) Perform a Time Series Analysis by plotting the Sales data on a line chart and fitting a trendline.
Question:4 Solve the following probability problems using Excel functions:
A) Binomial Distribution
A factory produces bulbs, and 90% of the bulbs are defect-free.
If 8 bulbs are selected at random:
a) Find the probability that exactly 6 bulbs are defect-free.
b) Find the probability that at least 7 bulbs are defect-free.
c) Find the cumulative probability of selecting up to 5 defect-free bulbs.
B) Poisson Distribution
A call center receives an average of 5 calls per minute.
a) Find the probability that exactly 7 calls are received in a minute.
b) Find the probability that at most 3 calls are received in a minute.
c) Find the probability of receiving more than 5 calls in a minute.
C) Normal Distribution
At a bank, the average customer service time is 8 minutes with a standard deviation of 2 minutes.
Assuming the service times are normally distributed:
a) Calculate the probability that a customer is served in under 10 minutes.
b) Determine the probability that a customer’s service time falls between 6 minutes and 10
minutes.
c) Find the maximum service time for the fastest 5% of customers.
Question:5. You are required to generate a dataset of 100 random numbers between 1 and 200 using
Excel. Using Excel you are required to:
a) Generate 100 random integers between 1 and 200.
b) Calculate the following measures of central tendency for the generated data:
• Mean (Average)
• Median
• Mode
c) Calculate the following additional statistical measures:
• P50 (50th Percentile)
• Q3 (75th Percentile)
• Standard Deviation
• Variance
d) Interpret your results:
• Comment on the symmetry or skewness of the dataset based on the comparison of Mean,
Median, and Mode.
• Discuss the spread of the data based on Standard Deviation and Variance.