Chapter -4 : Practice Questions & Materials

Food Data

Sales data of a grocery brand is available in the sheet. Perform following activities.

Filter the data for “Carrot” product and remove this from the data table. While removing make sure that blank rows are not created.

Group cost price and unit price then hide both columns.

Put conditional formatting on cost price such that high number is going to be red while low number is going to be green.

Put conditional formatting on Unit price such that high number is going to be green while low number is going to be red.

Put arrow conditional formatting on Margin using following rules.

Up Arrow : margin above 10%.

Horizontal arrow: Margin between 5% to 10%.

Down Arrow: Margin below 5%.

Assignment Scores:

In 3rd semester of a university, there are 16 assignments a student needs to submit for successfully completing the semester. However, it is seen that some students are skipping the assignment for various reasons. In the table, the scores for every student across different assignment are mentioned. In case any assignment is missed, the reason for the missing the assignment is mentioned in the table as well. Using this data, calculate average marks scored by every students in all the assignments

However, there are 2 problems in the data

By mistake, students from Biotechnology are also included in the list. Hence we need to remove those students from calculation.

Students who skipped the assignment should be given zero marks and then the average marks should be calculated.

Rectify these issues from the data and then calculate average marks for every student -

Hydropower:

To harness hydropower energy of a river, govt has installed 142 turbines at various places in river. Hourly output of every turbine is mentioned in the “Hydropower” sheet. Find out average hourly power output at turbine level and identify most power generating turbine from the data. However, there is problem in the data. Due to some sensor issues, the zero output for certain hours. While for the fact you know that this cannot be zero for any hour of that data. Hence you decides to rectify the data by replacing 0 with 20. Now calculate the best performing turbines from the data.

Profit and Loss Statement-

In PnL sheet, monthly level Pnl from 2000 to 2010 is mentioned. The sheet is not formatted properly. Also some of the calculations are missing. Make this PnL presentable by following these steps- • Total Variable Cost = GST+Performance Marketing+Cost of material+Packaging Charges • Gross Profit = GMV – Total Variable cost • Total Fixed Cost = Rent+Salary+Broadband Charges+Branding Activities+Software Charges+Website Hosting. • EBITDA = Gross Profit – Total Fixed Cost • EBIT = EBITDA - Depreciation-Amortization • EBT = EBIT – Interest • Net Profit = EBT – Tax • Group Jan to Dec data of same year in one group. Overall Yearly performance should not be part of the grouped columns. • Group variable cost in one and fixed cost component in another group.

Dataset -

⁠

Modifying Workbook and Sheets.xlsx

105.3 kB

⁠

Solution sheet -

⁠

2.1 Workbook - Modifying Workbook and Sheets (Solution).xlsx

134.4 kB

⁠

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.