Scribd
Upload
0 views2 pages

Assignment

The document outlines assignment questions related to data analysis tasks in Microsoft Excel. It includes creating PivotTables for sales data, managing and analyzing a raw dataset, and performing financial analyses using What-If Analysis, Goal Seek, and Solver. Specific tasks include data import, cleaning, formatting, and applying various Excel functions to derive insights from the datasets.

Uploaded by

maitreyeelohar18
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
0 views2 pages

Assignment

The document outlines assignment questions related to data analysis tasks in Microsoft Excel. It includes creating PivotTables for sales data, managing and analyzing a raw dataset, and performing financial analyses using What-If Analysis, Goal Seek, and Solver. Specific tasks include data import, cleaning, formatting, and applying various Excel functions to derive insights from the datasets.

Uploaded by

maitreyeelohar18
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Assignment Questions

Question 1:
Given the following sales dataset, perform the following tasks in Microsoft Excel:
Dataset:

Product Region Sales (Rs.) Quantity

Laptop North 1200 3

Laptop South 1500 4

Phone North 800 5

Phone East 900 6

Tablet West 700 2

Tablet South 650 3

Laptop West 1300 3

Phone South 1100 7

Tablet North 750 4

Phone West 950 5

a) Create a PivotTable to display the total sales for each product using the given dataset.
b) Modify the PivotTable as follows:
i) Add Region to the Columns section to analyse sales based on region.
ii) Add Quantity to the Values section to display the total quantity sold per product.
c) Apply the following customizations to the PivotTable:
i) Sort the data in descending order based on total sales.
ii) Apply a filter to display sales only for "Laptop" and "Phone."
iii) Format the "Sales (Rs.)" column to display values in currency format.
iv) Use Conditional Formatting to highlight the highest sales value in green and the
lowest in red.
v) Rename the PivotTable title to "Product Sales Summary" and adjust the column
width for better readability.
Question 2:
Given a raw dataset, perform the following data management and analysis tasks in Microsoft
Excel:
[Link]
3nKM3s/view?usp=drive_link
1. Import the dataset from an external source (CSV file, text file, or online database) into
Excel.
2. Remove duplicate entries from the dataset.
3. Handle missing values by either deleting them or replacing them with appropriate
values.
4. Standardize date format to DD/MM/YY across all records.
5. Sort the dataset in ascending order based on Quantity.
6. Apply a filter to display only records where Sales (Rs.) is greater than Rs.1000.
7. Restrict entries in the Quantity column to numeric values between 1 and 100.
8. Create a drop-down list for the Status column with predefined options: Delivered,
Pending, Shipped.
Question 3:
A company sells Product A for Rs. 25 per unit. The variable cost per unit is Rs. 15, and the
fixed monthly expenses are Rs. 5,000.
Perform the following analyses using What-If Analysis, Goal Seek, and Solver in Microsoft
Excel:
Dataset:

Parameter Value

Selling Price per Unit (Rs.) 25

Variable Cost per Unit (Rs.) 15

Fixed Costs (Rs.) 5000

Units Sold 300

1. What-If Analysis (Data Table)


Create a Data Table to show the effect of different selling prices (from Rs.20 to Rs.30) on
total profit.
2. Goal Seek Analysis
Use Goal Seek to determine the minimum number of units that must be sold to break even
(i.e., profit = Rs.0).

You might also like