Remembering (Recalling or recognizing information):

1. Define business analytics. List and briefly describe the three main types of business
[Link] five common descriptive statistics functions available in Excel.
2. What are key performance indicators (KPIs) in the context of business analytics?
Provide five examples of common KPIs used in different business functions (e.g., sales,
marketing, operations).
3. What Excel function can you use to calculate the average of a range of cells?
4. Name three different ways to calculate the central tendency of a dataset in
Excel.
5. Which Excel function returns the largest value in a given range?
6. Identify the Excel function used to count the number of cells that contain
numerical values within a range.
7. List and briefly define five different Excel functions used for calculating the depreciation
of an asset. Provide the syntax for each function.
8. What are the required arguments for the IPMT and PPMT functions in Excel? Explain
what each of these functions calculates in the context of a loan amortization schedule.
9. List five common data source types that can be connected to using Power Query.
10. List five key components or areas of the Excel PivotTable interface (e.g., Report Filter,
Column Labels, Row Labels, Values, Fields List).
11. State the probability density function (PDF) of a standard normal distribution. Define all
the parameters involved.
12. Explain the key differences between a discrete probability distribution and a continuous
probability distribution. Provide one real-world example for each.
Understanding (Comprehending the meaning, translation, interpolation, interpretation
of instructions and problems):
6. Explain in your own words why business analytics has become increasingly important
for organizations in today's business environment. Provide two specific examples to
support your explanation.
7. Differentiate between structured and unstructured data, providing examples of each.
Explain the challenges and opportunities associated with analyzing unstructured data
for business insights.
8. Explain the difference between the AVERAGE and MEDIAN functions in Excel
and when you might prefer to use one over the other.
9. Describe what the standard deviation measures in a dataset and identify the
relevant Excel function.
10. How would you interpret a high value for the standard deviation calculated
using Excel's STDEV.S function?
11. Explain the purpose of the COUNTIF function and provide a simple example of
its usage.
12. Differentiate between the COUNT, COUNTA, and COUNTBLANK functions in Excel.
13. Explain the fundamental difference between the SLN and DB depreciation methods in
Excel. Describe a scenario where using the DB method would be more appropriate than
the SLN method.
14. Explain how the concept of a "period" differs when using the SYD function versus the
other depreciation functions like SLN or DB in Excel. Provide an example to illustrate this
difference.
15. Explain in your own words the purpose of the "Applied Steps" pane in the Power Query
Editor.
16. Explain in your own words the primary purpose of using a PivotTable in Excel, and
provide a simple example of a scenario where it would be beneficial.
Applying (Using a concept in a new situation or unprompted use of an abstraction):
11. A marketing team wants to predict which customers are most likely to respond to a new
product launch campaign. Describe a predictive analytics approach they could use and
the type of data required for this analysis.
12. A retail company has collected sales data for the past year. Describe two different
business analytics techniques they could apply to this data and explain what kind of
insights each technique might reveal.
13. You have a list of student scores in an Excel sheet. Write the Excel formula to
calculate the average score.
14. Given a dataset of sales figures in column B, write the Excel formula to find
the highest sales value.
15. You have a list of customer names in column A. Write the Excel formula to
count the total number of customers.
16. Using the data in cells C1 to C10, write the Excel formula to calculate the
median sales figure.
17. You have a list of product categories in column D. Write an Excel formula to
count how many times the category "Electronics" appears.
18. A company purchased a machine for ₹5,00,000 with a salvage value of ₹50,000 and a
useful life of 10 years. Using the appropriate Excel function, calculate the depreciation
expense for the 3rd year using the straight-line method. Show the Excel formula you
would use.
19. A company took out a loan of ₹10,00,000 with an annual interest rate of 8% for a period
of 5 years. Using the appropriate Excel function, calculate the amount of interest paid in
the second year. Show the Excel formula you would use.
20. You have a column named "Product Code" that contains both letters and numbers (e.g.,
"ABC-123", "DEF-456"). Describe the Power Query steps you would take to create a new
column containing only the numeric part of the "Product Code".
21. You have a dataset with columns for "Product Category," "Region," and "Sales Amount."
Describe the steps you would take to create a PivotTable that shows the total sales
amount for each product category in each region.
22. A company claims that the average lifespan of their light bulbs is 1000 hours with a
standard deviation of 50 hours. You test a sample of 49 bulbs and find the sample mean
to be 980 hours. Based on this data, do you find the company's claim plausible? Justify
your answer using the concept of sampling distributions (you don't need to perform a
formal hypothesis test, just explain the reasoning).
Analyzing:
16. Consider a scenario where a company's sales have been declining despite increased
marketing expenditure. Identify two potential underlying reasons for this situation that
business analytics could help uncover. Explain how specific analytical techniques could
be used to investigate these reasons.
17. Critically evaluate the statement: "More data always leads to better business decisions."
Discuss the factors that can limit the effectiveness of business analytics, even with large
datasets.
18. Compare and contrast the roles of a business analyst and a data scientist in an
organization. Highlight their key responsibilities and the skills typically required for each
role.
19. You have two sets of sales data for different regions. How could you use
Excel's descriptive statistics functions to compare the central tendency and
spread of sales in these two regions? Explain the steps involved.
20. You have been provided with a spreadsheet containing the annual depreciation
expenses calculated using an unknown Excel function for an asset purchased 5 years
ago. Describe the steps you would take to identify which depreciation function was used
and analyze the pattern of depreciation over the asset's life.
21. You are given a dataset with some missing values. How would you use Excel
functions to calculate the average of a specific column while ignoring these
missing values?
22. You have a large dataset of customer ages. Describe how you would use a
combination of Excel functions to determine the percentage of customers
within a specific age range (e.g., 25-35).
23. You have a dataset of employee salaries. Explain how you could use Excel
functions to identify potential outliers in the salary distribution.
24. You have collected survey data with responses on a Likert scale (1 to 5).
How would you use Excel functions to analyze the distribution of responses
for a particular question and identify the most common response?
25. A manager is debating between using the VDB and DB functions in Excel for calculating
the depreciation of a new piece of equipment. Compare and contrast these two
functions, highlighting their strengths and weaknesses. Recommend which function
might be more suitable and justify your reasoning.
26. You have a dataset showing the monthly principal and interest payments for a loan
calculated using Excel's financial functions. Describe how you would use these functions
and the data to verify the accuracy of the loan amortization schedule.
27. Consider a scenario where you have two tables: one with sales data containing
"CustomerID" and "Sales Amount," and another with customer details including
"CustomerID" and "Region." Outline the steps in Power Query to merge these tables and
then filter the combined data to show only sales from the "East" region.
28. Consider a PivotTable displaying the average sales price by month and product. What
potential insights could you derive from this table, and what further analysis might you
perform to understand the data better?

Creating (Synthesis):
29. Imagine you are a business consultant advising a small e-commerce startup. Outline a
basic business analytics framework that they could implement to track their online sales and
customer behavior. Include the key stages and considerations.
30. Design an Excel worksheet that allows a user to input the cost, salvage
value, and useful life of an asset. The worksheet should then calculate and
display the annual depreciation expense for each year of the asset's life using
both the straight-line and double-declining balance methods side-by-side.
Include appropriate labels and formatting.
31. Discuss the advantages and disadvantages of using Power Query to clean and
transform data compared to performing similar operations directly in Excel worksheets.
Provide at least two advantages and two disadvantages.
32. Describe the steps you would take in Power Query to unpivot a table where
multiple columns represent monthly sales figures (e.g., "Jan Sales," "Feb
Sales," "Mar Sales") into a long table with "Month" and "Sales" columns.
33. You have a column with full names (e.g., "John Doe", "Jane Smith"). Outline
the Power Query steps to split this column into two separate columns: "First
Name" and "Last Name".
34. Explain how you would use conditional columns in Power Query to
categorize products as "High Value," "Medium Value," or "Low Value" based on
their "Price" column.
35. Describe a scenario where using a custom function in Power Query would be
beneficial for data transformation. Provide a brief example of what this
custom function might do.
36. You have a table with dates in a text format (e.g., "2024-12-31"). Explain the
Power Query steps required to convert this column to a proper date data type
and then create a new column that extracts the year from the date.
37. Discuss the advantages and disadvantages of using a PivotTable compared to using
standard Excel formulas (like SUMIF or COUNTIFS) for summarizing and analyzing data.
Provide specific examples to support your points.
38. Design a PivotTable layout using the fields "Customer Name," "Order Date,"
"Product Name," and "Quantity" to show the total quantity of each product
ordered by each customer, with the order dates grouped by year. Describe the
steps to achieve this layout.
39. You have sales data with "Salesperson," "Product," "Month," and "Revenue."
Explain how you would use PivotTable features (beyond basic row/column
summarization) such as calculated fields or value filters to identify the top-
performing salespeople based on revenue growth compared to the previous
month.
40. Describe a scenario where you would need to use the "Report Filter" in a
PivotTable. Explain how it works and how it can help in analyzing your data.
41. Explain the purpose and functionality of the "Values" area in a PivotTable.
Discuss the different types of calculations you can perform in this area (e.g.,
Sum, Average, Count, Max, Min) and when you might use each one.
42. You have a large dataset with multiple columns. Outline a strategy for
efficiently creating a PivotTable to explore the relationships between different
categorical variables and a numerical variable (e.g., how different customer
segments contribute to total profit). Include considerations for choosing
appropriate row/column fields and value calculations.
43. Consider two different binomial experiments. Experiment A has n=20 trials and a
probability of success p=0.3. Experiment B has n=50 trials and a probability of success p=
0.1. Without performing detailed calculations, compare and contrast the expected
number of successes and the potential spread (variance) of the number of successes in
these two experiments. Justify your reasoning.