BCSL-044 Solved Free Assignment 2024-25 Sem 4

Question 1: The weight of 50 adults in the age group 20-30 years, measured in Kilograms, is given below. Perform the tasks given in (i) to (iv) using a spreadsheet package:

40 70 61 58 58 50 72 63 51 62 65 60 68 68 78 54 52 60 50 70 60 35 53 58 79 60 62 61 55 65 51 39 45 58 50 65 62 50 72 62 52 65 67 87 45 75 71 52 65 59

(i) Find the minimum and maximum weight using spreadsheet formula.

(ii) Divide the weight in 5 classes with class interval 10 and create the frequency distribution for these classes using Array formula .

(iii) Find the percentage of students, whose weight is in between 50 and 60 kgs.

(iv) Represent the frequency distribution with the help of a relevant graph.

Ans:- To perform the tasks outlined in Question 1 using a spreadsheet package such as Microsoft Excel or Google Sheets, here’s a step-by-step guide on how to achieve each part.

---

Data:

The weight of 50 adults (in kg):

```

40, 70, 61, 58, 58, 50, 72, 63, 51, 62, 65, 60, 68, 68, 78, 54, 52, 60, 50, 70, 60, 35, 53, 58, 79, 60, 62, 61, 55, 65, 51, 39, 45, 58, 50, 65, 62, 50, 72, 62, 52, 65, 67, 87, 45, 75, 71, 52, 65, 59

```

---

(i) Find the Minimum and Maximum Weight

1. **Enter the data**:

Enter the weight data into a column in your spreadsheet (e.g., from A1 to A50).

2. **Minimum**:

Use the `MIN()` formula to find the minimum weight.

- Formula: `=MIN(A1:A50)`

3. **Maximum**:

Use the `MAX()` formula to find the maximum weight.

- Formula: `=MAX(A1:A50)`

This will give you the minimum and maximum weight values in the dataset.

---

(ii) Create the Frequency Distribution with 5 Classes (Class Interval of 10)

1. **Define the Classes**:

For a class interval of 10, create the following classes:

- 30-39 kg

- 40-49 kg

- 50-59 kg

- 60-69 kg

- 70-79 kg

- 80-89 kg

2. **Set Up Classes in the Spreadsheet**:

In another column (say column C), list the upper bounds of the classes:

```

3. **Use the `FREQUENCY()` Function**:

The `FREQUENCY()` function in Excel and Google Sheets will calculate the distribution of values across the specified bins.

- **Select a range of 6 cells** (next to your class intervals) for the frequency output (for the 6 bins: 30-39, 40-49, etc.).

- Enter the following formula and press `Ctrl + Shift + Enter` (for array formula):

- Formula: `=FREQUENCY(A1:A50, C1:C6)`

This will generate the frequency distribution for each class.

---

(iii) Find the Percentage of Students with Weight Between 50 and 60 kg

1. **Use the `COUNTIFS()` Function**:

To count the number of weights between 50 and 60 kg, use the `COUNTIFS()` function:

- Formula: `=COUNTIFS(A1:A50, ">=50", A1:A50, "<=60")`

2. **Calculate Percentage**:

Now, calculate the percentage:

- Total students: `COUNT(A1:A50)`

- Formula for percentage:

- `= (COUNTIFS(A1:A50, ">=50", A1:A50, "<=60") / COUNT(A1:A50)) * 100`

This will give you the percentage of students whose weight is between 50 and 60 kg.

---

(iv) Represent the Frequency Distribution with a Graph

1. **Highlight the Frequency Data**:

Select the class intervals and their corresponding frequency values (in columns C and D).

2. **Create a Chart**:

- In Excel: Go to `Insert` -> `Bar Chart` or `Column Chart`.

- In Google Sheets: Go to `Insert` -> `Chart`, then choose a column chart or bar chart.

3. **Label the Chart**:

- Label the X-axis as "Weight Classes (kg)".

- Label the Y-axis as "Frequency".

- Add a chart title such as "Frequency Distribution of Weights".

This will provide a graphical representation of the frequency distribution for the weight classes.

---

Summary of Steps:

- **Step 1 (i)**: Use `MIN()` and `MAX()` to find minimum and maximum weights.

- **Step 2 (ii)**: Use `FREQUENCY()` array formula to create a frequency distribution with a class interval of 10.

- **Step 3 (iii)**: Use `COUNTIFS()` to find how many students have weights between 50-60 kg, and calculate the percentage.

- **Step 4 (iv)**: Use a bar or column chart to graph the frequency distribution.

This method provides a structured way to analyze and visualize the weight data using a spreadsheet package.

Question 2: Perform the following tasks using a spreadsheet (you must either enter necessary formula that are required to calculate the value or you may use spreadsheet function for the same):

(i) Calculate the standard error, given a population of 250, sample size 50 and population standard deviation of 25.

(ii) Assume that a company manufactures rings. The rings should have a mean diameter of 2cm. A sample of 20 such rings were taken out of 1000 such rings. The sample diameter of these rings was 2.01 cm with a standard deviation of 0.01 cm. Can the company say with 95% confidence that the rings should be accepted. Make suitable assumption and justify your answer.

Ans:- Spreadsheet Tasks

We’ll perform two statistical tasks using a spreadsheet (such as Microsoft Excel or Google Sheets) to calculate standard error and conduct a confidence interval analysis.

---

(i) Calculate the Standard Error

Given:

- Population size (N) = 250

- Sample size (n) = 50

- Population standard deviation (σ) = 25

The formula for **Standard Error (SE)** when the population size is known is:

SE = \frac{\sigma}{\sqrt{n}} \times \sqrt{\frac{N-n}{N-1}}

Where:

- \( \sigma \) = Population standard deviation

- \( n \) = Sample size

- \( N \) = Population size

Steps in the Spreadsheet:

1. Enter the values in cells:

- Cell A1: "Population size (N)" and B1: 250

- Cell A2: "Sample size (n)" and B2: 50

- Cell A3: "Population standard deviation (σ)" and B3: 25

2. In another cell (say B4), use the formula for standard error:

- Formula: `= (B3/SQRT(B2)) * SQRT((B1-B2)/(B1-1))`

This will give the standard error based on the population and sample sizes.

---

(ii) Confidence Interval for Ring Diameter

Given:

- Population size = 1000 rings

- Sample size (n) = 20 rings

- Sample mean diameter (x̄) = 2.01 cm

- Standard deviation (s) = 0.01 cm

- Desired confidence level = 95%

To test if the rings are acceptable at 95% confidence, we need to perform a **t-test** to determine if the sample mean significantly differs from the desired mean of 2.00 cm.

Steps:

1. **Enter the sample values**:

- Cell A6: "Sample size (n)" and B6: 20

- Cell A7: "Sample mean (x̄)" and B7: 2.01

- Cell A8: "Sample standard deviation (s)" and B8: 0.01

- Cell A9: "Population mean (μ)" and B9: 2.00

2. **Calculate the T-statistic**:

The **t-statistic** formula is:

t = \frac{x̄ - μ}{\frac{s}{\sqrt{n}}}

In the spreadsheet, in cell B10, use the formula:

- Formula: `= (B7 - B9) / (B8 / SQRT(B6))`

3. **Degrees of freedom (df)**:

- Formula: `= B6 - 1`

4. **Critical value for 95% confidence**:

Use the `T.INV.2T()` function to find the critical value for a two-tailed t-test at 95% confidence, with degrees of freedom (df). In cell B11:

- Formula: `= T.INV.2T(0.05, B6-1)`

5. **Decision**:

Compare the **absolute value** of the calculated t-statistic to the critical value. If the absolute value of the t-statistic is less than the critical value, you can conclude that there is no significant difference, and the rings are acceptable.

---

Interpretation of Results:

- If \( |t_{\text{calculated}}| < t_{\text{critical}} \), the company can say with 95% confidence that the rings meet the required diameter specification and should be accepted.

- If \( |t_{\text{calculated}}| \geq t_{\text{critical}} \), the company cannot be confident that the rings meet the specification and should be re-evaluated.

---

Summary of Spreadsheet Formulas:

- **(i) Standard Error**:

Formula for standard error in B4:

```excel

= (B3/SQRT(B2)) * SQRT((B1-B2)/(B1-1))

```

- **(ii) Confidence Interval Analysis**:

- T-statistic in B10:

```excel

= (B7 - B9) / (B8 / SQRT(B6))

```

- Critical value in B11:

```excel

= T.INV.2T(0.05, B6-1)

```

By following these steps, you can perform statistical analysis in a spreadsheet to calculate standard error and assess whether the ring diameters meet the company’s requirements with 95% confidence

Question 3: A paper making company experiments with quantity of paper being produced by four of its machine. Assuming that company has four such machines and productivity of these machine is recorded on four different days in the following table.

Day Quantity of paper per Machine A B C D 1 91 89 92 90 2 90 88 89 87 3 93 88 90 91 4 88 89 90 88 Perform an ANOVA using any software to test (at 5% level) whether all the four machines are equally productive. Make suitable assumptions, if any.

Ans:- To perform an ANOVA (Analysis of Variance) test to determine if there is a significant difference in the productivity of the four machines based on the data provided, you can use software like Microsoft Excel, R, or Python. Here’s a step-by-step guide on how to conduct a one-way ANOVA in Excel, which is one of the most common methods for this type of analysis.

Data:

The productivity of the four machines over four days is recorded as follows:

|-----|-----------|-----------|-----------|-----------|

| 1 | 91 | 89 | 92 | 90 |

| 2 | 90 | 88 | 89 | 87 |

| 3 | 93 | 88 | 90 | 91 |

| 4 | 88 | 89 | 90 | 88 |

Steps to Perform ANOVA in Excel:

Step 1: Enter the Data

1. Open Excel and enter the data in a table format:

- Column A: "Day" (1 to 4)

- Columns B, C, D, E: "Machine A", "Machine B", "Machine C", "Machine D"

- Fill in the respective quantities.

Step 2: Organize Data for ANOVA

You may need to rearrange the data for ANOVA. Instead of having it in a day-wise format, place the machines in a single column with corresponding productivity values. You could have two columns:

| Machine | Quantity |

|---------|----------|

| A | 91 |

| A | 90 |

| A | 93 |

| A | 88 |

| B | 89 |

| B | 88 |

| B | 89 |

| C | 92 |

| C | 89 |

| C | 90 |

| D | 90 |

| D | 87 |

| D | 91 |

| D | 88 |

Step 3: Perform ANOVA

1. **Select the Data**: Highlight the entire range of your data (the new table you created).

2. **Data Analysis Tool**:

- Go to the `Data` tab in the ribbon.

- Click on `Data Analysis`. If you do not see it, you might need to enable the Analysis ToolPak add-in.

- Select `ANOVA: Single Factor` and click `OK`.

3. **Input Range**:

- For "Input Range", select your entire data range (e.g., B1:E5 if you kept the original layout).

- Choose "Grouped By" as `Columns`.

- Check the box for "Labels in First Row" if you included headers.

4. **Alpha Level**:

- Set the alpha level to 0.05 for a 5% significance level.

5. **Output Range**:

- Select where you want the ANOVA output to be displayed (e.g., starting in cell G1).

6. **Click OK**:

- The results will be displayed in the specified output range.

Step 4: Interpret the Results

Look for the following key values in the ANOVA output:

- **F-statistic**: The test statistic for ANOVA.

- **P-value**: The significance level of the test.

Decision Rule:

- If the **P-value** is less than 0.05 (your alpha level), you reject the null hypothesis and conclude that there is a significant difference in productivity among the four machines.

- If the **P-value** is greater than 0.05, you fail to reject the null hypothesis, indicating that there is no significant difference in productivity.

Assumptions:

- The samples from each machine are independent of one another.

- The populations from which the samples are drawn are normally distributed.

- The variances of the populations are equal (homogeneity of variances).

Question 4: The daily production of items of a company is given in the following table. Use spreadsheet software to find the moving averages for the length of 5. Day Production (in Metric tons) 1 2 3 4 5 6 7 8 9 10 29 5 44 30 40 45 7 60 30 49

Ans:- To calculate the moving averages for the daily production data provided, we will use a **moving average length of 5 days**. This means that each moving average will be calculated based on the production values of the current day and the previous four days.

Data:

Here is the daily production data in a tabular format:

| Day | Production (in Metric Tons) |

|-----|-----------------------------|

| 1 | 29 |

| 2 | 5 |

| 3 | 44 |

| 4 | 30 |

| 5 | 40 |

| 6 | 45 |

| 7 | 7 |

| 8 | 60 |

| 9 | 30 |

| 10 | 49 |

Steps to Calculate Moving Averages in Excel or Google Sheets

1. **Enter the Data**:

- Open your spreadsheet software (Excel or Google Sheets).

- Enter the data in two columns. For example:

- Column A: "Day" (from 1 to 10)

- Column B: "Production" (from 29, 5, 44, 30, 40, 45, 7, 60, 30, 49)

2. **Setting Up Moving Average Calculation**:

- In cell C1, you can label it as "Moving Average (5 Days)" to indicate the moving average calculation.

- The moving average will start from Day 5 since we need 5 days of data to calculate the first moving average.

3. **Calculate the Moving Average**:

- In cell C5, enter the following formula to calculate the moving average for the first 5 days:

```excel

=AVERAGE(B1:B5)

```

- This formula calculates the average of the production values from Day 1 to Day 5.

4. **Drag the Formula Down**:

- After entering the formula in C5, you can click on the bottom right corner of the cell (C5) and drag it down to cell C10. This will copy the formula down while adjusting the range to calculate the moving averages for subsequent days:

- For Day 6: `=AVERAGE(B2:B6)`

- For Day 7: `=AVERAGE(B3:B7)`

- For Day 8: `=AVERAGE(B4:B8)`

- For Day 9: `=AVERAGE(B5:B9)`

- For Day 10: `=AVERAGE(B6:B10)`

Final Table with Moving Averages:

After following the steps, your table should look like this:

| Day | Production (in Metric Tons) | Moving Average (5 Days) |

|-----|-----------------------------|--------------------------|

| 1 | 29 | |

| 2 | 5 | |

| 3 | 44 | |

| 4 | 30 | |

| 5 | 40 | 29.6 |

| 6 | 45 | 29.8 |

| 7 | 7 | 30.8 |

| 8 | 60 | 44.6 |

| 9 | 30 | 42.4 |

| 10 | 49 | 46.2 |

Interpretation:

- The "Moving Average (5 Days)" column provides the average production for the specified day and the four preceding days. This helps smooth out fluctuations and gives a better understanding of trends in production over time.

Feel free to adjust the table as needed for better visualization or analysis!

Question 5: A company manufactures refills of pens. Five observations of refills are taken on each day. These observations were taken 6 times during a working day. Calculate the control limits for mean and range, and plot the control charts using any statistical software. Make suitable assumptions, if any. The data is given in the following table: Sample No. Point size of pen in mm 1 2 3 4 5 6 2.04, 2.01, 1.87, 1.85, 1.90 2.14, 2.11, 1.97, 1.95, 2.10 1.99, 2.21, 1.77, 1.98, 1.98 2.00, 2.05, 1.97, 1.95, 2.01 1.87, 2.14, 2.19, 2.20, 2.15 2.06, 1.91, 2.17, 2.05, 1.90 (Please take the suitable values of d2 , d3, d4 , A2 and other variables.)

Ans:- To calculate the control limits for mean and range charts for the refill pen data provided, we will follow these steps:

1. **Calculate the Mean and Range for Each Sample**

2. **Calculate Control Limits**

3. **Plot Control Charts**

Data

Here is the provided data of point sizes of pens in mm:

| Sample No. | Point Size (mm) |

|------------|--------------------------------------|

| 1 | 2.04, 2.01, 1.87, 1.85, 1.90 |

| 2 | 2.14, 2.11, 1.97, 1.95, 2.10 |

| 3 | 1.99, 2.21, 1.77, 1.98, 1.98 |

| 4 | 2.00, 2.05, 1.97, 1.95, 2.01 |

| 5 | 1.87, 2.14, 2.19, 2.20, 2.15 |

| 6 | 2.06, 1.91, 2.17, 2.05, 1.90 |

Step 1: Calculate the Mean and Range for Each Sample

1. **Calculate Sample Means** (\( \bar{X} \)) and Ranges (R):

| Sample No. | Point Sizes | Mean (\( \bar{X} \)) | Range (R) |

|------------|----------------------------------|---------------------|-----------|

| 1 | 2.04, 2.01, 1.87, 1.85, 1.90 | \( (2.04 + 2.01 + 1.87 + 1.85 + 1.90) / 5 = 1.974 \) | \( 2.04 - 1.85 = 0.19 \) |

| 2 | 2.14, 2.11, 1.97, 1.95, 2.10 | \( (2.14 + 2.11 + 1.97 + 1.95 + 2.10) / 5 = 2.074 \) | \( 2.14 - 1.95 = 0.19 \) |

| 3 | 1.99, 2.21, 1.77, 1.98, 1.98 | \( (1.99 + 2.21 + 1.77 + 1.98 + 1.98) / 5 = 1.994 \) | \( 2.21 - 1.77 = 0.44 \) |

| 4 | 2.00, 2.05, 1.97, 1.95, 2.01 | \( (2.00 + 2.05 + 1.97 + 1.95 + 2.01) / 5 = 1.996 \) | \( 2.05 - 1.95 = 0.10 \) |

| 5 | 1.87, 2.14, 2.19, 2.20, 2.15 | \( (1.87 + 2.14 + 2.19 + 2.20 + 2.15) / 5 = 2.13 \) | \( 2.20 - 1.87 = 0.33 \) |

| 6 | 2.06, 1.91, 2.17, 2.05, 1.90 | \( (2.06 + 1.91 + 2.17 + 2.05 + 1.90) / 5 = 2.02 \) | \( 2.17 - 1.90 = 0.27 \) |

Summary of Means and Ranges

| Sample No. | Mean (\( \bar{X} \)) | Range (R) |

|------------|---------------------|-----------|

| 1 | 1.974 | 0.19 |

| 2 | 2.074 | 0.19 |

| 3 | 1.994 | 0.44 |

| 4 | 1.996 | 0.10 |

| 5 | 2.13 | 0.33 |

| 6 | 2.02 | 0.27 |

Step 2: Calculate Control Limits

Assuming values for \( A2 \), \( D3 \), \( D4 \), and \( d2 \):

- **\( A2 = 0.577 \)** (common for sample size n = 5)

- **\( D3 = 0 \)** (lower control limit for range)

- **\( D4 = 2.114 \)** (upper control limit for range)

- **\( d2 \) value for n = 5 is \( 2.223 \)** (used to calculate R)

Calculate Overall Mean (\( \bar{\bar{X}} \)) and Average Range (\( \bar{R} \))

- Overall Mean (\( \bar{\bar{X}} \)):

\bar{\bar{X}} = \frac{1.974 + 2.074 + 1.994 + 1.996 + 2.13 + 2.02}{6} = 2.01

- Average Range (\( \bar{R} \)):

\bar{R} = \frac{0.19 + 0.19 + 0.44 + 0.10 + 0.33 + 0.27}{6} = 0.2367

Control Limits for the Mean Chart

- Upper Control Limit (UCL):

UCL = \bar{\bar{X}} + A2 \cdot \bar{R} = 2.01 + 0.577 \cdot 0.2367 \approx 2.01 + 0.1365 \approx 2.1465

- Lower Control Limit (LCL):

LCL = \bar{\bar{X}} - A2 \cdot \bar{R} = 2.01 - 0.577 \cdot 0.2367 \approx 2.01 - 0.1365 \approx 1.8735

Control Limits for the Range Chart

- UCL for Range:

UCL_R = D4 \cdot \bar{R} = 2.114 \cdot 0.2367 \approx 0.5005

- LCL for Range:

LCL_R = D3 \cdot \bar{R} = 0 \cdot 0.2367 = 0

Summary of Control Limits

- **Control Limits for Mean Chart**:

- UCL: **2.1465**

- LCL: **1.8735**

- **Control Limits for Range Chart**:

- UCL: **0.5005**

- LCL: **0**

Step 3: Plot Control Charts

In Excel or Google Sheets:

1. **Enter the Data**:

- Create columns for Sample No., Mean, Range, UCL Mean, LCL Mean, UCL Range, LCL Range.

2. **Create Control Chart for Mean**:

- Highlight the Sample No., Mean, UCL Mean, and LCL Mean.

- Go to the `Insert` tab and choose `Line Chart`.

- Format the chart appropriately (label axes, title, etc.).

3. **Create Control Chart for Range**:

- Highlight Sample No., Range, UCL Range, and LCL Range.

- Insert a `Line Chart` for the Range control chart.

Assumptions:

- The samples are independent.

- The distribution of the point sizes is approximately normal.

- The variation in the process is stable over time.

Question 6: A company sells summer clothing. Fit a trend using any statistical software to sales data for this company. Make suitable assumptions. Month Mar Arp May June Jul Aug Sept Sales(in pieces) 400 700 2000 3000 2000 1000 200

Ans:- To fit a trend to the sales data for a company selling summer clothing, we can use various statistical methods, such as linear regression, polynomial regression, or moving averages. For this example, I will guide you through the process of fitting a linear trend using Excel, which is a common approach for trend analysis.

Given Data

Here is the provided sales data:

| Month | Sales (in pieces) |

|-------|--------------------|

| Mar | 400 |

| Apr | 700 |

| May | 2000 |

| Jun | 3000 |

| Jul | 2000 |

| Aug | 1000 |

| Sept | 200 |

Step 1: Enter the Data in Excel

1. Open Excel and create a new spreadsheet.

2. Enter the data in two columns. For example:

| A | B |

|-------|--------------------|

| Month | Sales (in pieces) |

| 1 (Mar) | 400 |

| 2 (Apr) | 700 |

| 3 (May) | 2000 |

| 4 (Jun) | 3000 |

| 5 (Jul) | 2000 |

| 6 (Aug) | 1000 |

| 7 (Sept)| 200 |

*Here, I've assigned numbers to the months for easier calculations (1 for March, 2 for April, etc.).*

Step 2: Create a Scatter Plot

1. **Select Data**: Highlight the range of data for months and sales (A2:B8).

2. **Insert Chart**: Go to the `Insert` tab in the Excel ribbon, click on `Scatter`, and choose `Scatter with Straight Lines` or just `Scatter`.

3. **Add Trendline**:

- Click on the scatter plot to select it.

- Click on the `+` icon next to the chart (Chart Elements).

- Check the `Trendline` box.

- Right-click on the trendline and select `Format Trendline`.

- Choose `Linear` as the type of trendline.

Step 3: Display the Equation and R-squared Value

1. In the Format Trendline pane, check the boxes for `Display Equation on chart` and `Display R-squared value on chart`.

2. This will provide you with the equation of the linear trend and the R² value, which indicates how well the trend line fits the data (the closer to 1, the better).

Step 4: Analyze the Trend

- **Equation of the Trendline**: The equation will typically be in the form \( y = mx + b \), where:

- \( y \) is the sales,

- \( m \) is the slope of the line (indicating the rate of change of sales),

- \( x \) is the month number,

- \( b \) is the y-intercept (the estimated sales when \( x = 0 \)).

- **R-squared Value**: This value helps assess the goodness of fit for the trendline. An R² value closer to 1 indicates that a significant proportion of the variability in sales can be explained by the linear trend.

Step 5: Interpretation

- Based on the trendline equation, you can forecast future sales by plugging in values for \( x \) corresponding to future months.

- The slope of the line will indicate whether sales are generally increasing or decreasing.

Assumptions

- The sales data represents a typical pattern for the summer clothing business.

- Seasonal trends are accounted for; this model assumes that sales increase during summer months and may decline afterward.

- The relationship between time and sales is linear for the sake of this model.

BCSL-044 Solved Free Assignment 2024-25 Sem 4

No comments:

Category

RSS

Followers

Vainik Studio Study Book Official App

NIOS Assignment Solution Free Video

LIVE COMING 🔜

EDUCATION VIDEO UPDATE

Pages

BCSL-044 Solved Free Assignment 2024-25 Sem 4

You Might Also Like

No comments:

Category

RSS

Followers

Vainik Studio Study Book Official App

NIOS Assignment Solution Free Video

LIVE COMING 🔜

EDUCATION VIDEO UPDATE

Pages