Data Analysis and Visualization using Spreadsheet — DU B.Sc. (Hons) Computer Science Previous Year Question Papers

4. Parts of a question must be answered together. Employe Salar (i) Write a formula to calculate the average salary in the HR department. (i) Write a formula to calculate the highest salary in the IT department. f) How does a Pivot Table help in data analysis? Explain its advantages and 5 steps to create it in Excel. g) Explain the data types in excel with an example. 2 h) What are different types of cell referencing in Excel? Explain absolute and 5 relative referencing with an example 2 a) A company manufactures a product with the following details: 9 Selling Price per unit - 250 Cost per unit = 230 Profit = (Selling Price - Cost) × Quantity The management wants to analyze different situations to improve profit. (i) Use Goal Seck to find how many units must be sold to carn a profit of (ii) Use Scenario Manager to compare profits under three cases: Best Case: Quantity = 800 Average Case: Quantity = 500 Worst Case: Quantity • 200 (iii) Create a Data Table to display how the profit changes when the quantity sold increases from 100 to 1000. b) The following data shows the relationship between advertising expenditure 6 (X) and sales (Y): Advertising Sales (i) Find the regression equation of Y on X using Excel. (ii) Estimate sales when advertising expenditure is 60. a) A company records salaries as follows: (i) Write the syntax of "COUNTIF" to count the employees earning more than 30000. (ii) Calculate the Range and Standard Deviation 4 (iii) Explain how sorting and filtering can be used. Filtering high-salaried employees 4 (iv) Write down the steps for creating a Line Chart. 4 4 a) Write the syntax and example of the following function in MS Excel 10 (iii)MEDIAN (V) AVERAGE b) What do you mean by measures of central tendency? Explain its types with 5 the following data: 10, 20, 30, 40, 50 5 a) Differentiate the following: 6 (i) Normal and Uniform distribution (ii) Formula and Function (ill) SUBSTITUTE and REPLACE in excel A company has created a Data Model. in Excel containing a table named Product Region Year SalesAmount Mobile North 2022 30000 Mobile South 2022 35000 Mobile North 2023 38000 Mobile South 2023 42000 Retrieve and analyze data from the Data Model using CUBE functions. (i) Write a formula to extract total sales from the data model. 2 (ill) Write a formula to retrieve sales for Region = "North" and Year = 2023. (iv) Explain how CUBEVALUE helps in dynamic reporting. correlation? Describe its properties and method of calculation. Also describe how to interpret correlation values with appropriate examples. b) What is a box plot? Explain the steps involved in creating a Box and 5 Whisker chart in Excel. 7 A month-wise sales figure of the salesman is given in the following spreadsheet: Write a formula to: (i) Calculate the total sales of each salesman. (ii) Calculate the average monthly sales of each salesman. 2 (iii) Calculate commission (5% if sales» 60000 else 3%) (vii) Draw a pie chart representing the sales made by a salesman in March: 2