Business Data Management · Quiz 2 · May 2025

**1. Understand Relative vs. Absolute Cell References in Excel:** - In the formula `=A1 * $B$1`: - `A1` is a **relative reference** (no `$` signs), so both its column and row adjust when copied to other cells. - `$B$1` is an **absolute reference**: the `$` before `B` locks the column and the `$` before `1` locks the row. - Consequently, the reference to cell `B1` is absolute and will **not change** when the formula is copied to other cells. **Answer: The reference to cell B1 is absolute and will not change when the formula is copied to other cells (Option B).** A: **Incorrect:** Because `$B$1` has dollar signs before both the column letter and row number, it does not adjust when copied. B: **Correct:** The `$` symbols before `B` and `1` make `$B$1` an absolute cell reference that remains fixed when copied across cells. C: **Incorrect:** `A1` has no `$` symbols, so it is a relative reference and will adjust when copied. D: **Incorrect:** This reverses the two references: `A1` is relative and `$B$1` is absolute.

**1. Identify the Government's Roles in the Economic Circular Flow Model:** - In the macroeconomic circular flow model, the Government acts both as: 1. **Producer and Consumer (Participant):** It purchases goods, services, and factor inputs (labor, capital) from households and firms, and provides public goods and services. 2. **Moderator and Participant (Regulator):** It regulates markets, collects taxes, and provides subsidies and transfer payments to maintain economic stability. - Therefore, both pairs of roles apply. **Answer: Both Producer and Consumer & Moderator and Participant (Option C).** A: **Incorrect:** While the Government is a producer and consumer, it also acts as a moderator/regulator and participant. B: **Incorrect:** This omits the explicit Producer and Consumer aspect included in Option C. C: **Correct:** The Government functions as both Producer & Consumer and Moderator & Participant in the circular flow of the economy. D: **Incorrect:** The roles in A and B are both valid.

**1. Define Market Potential for a New Product:** - **Market potential** is the maximum total demand or sales volume that a new product can achieve in a defined target market over a given period. - Estimating it fundamentally depends on: 1. The **size of the target market** (\( N \), total number of potential buyers), and 2. The **percentage of potential customers interested** in adopting/buying the product (penetration/interest rate). **Answer: The size of the target market and percentage of potential customers interested in the product (Option A).** A: **Correct:** Market potential is directly determined by the size of the target population and the proportion of customers interested in purchasing the product. B: **Incorrect:** Sales of unrelated products do not measure demand for the new product. C: **Incorrect:** Production and distribution costs affect supply and profitability, not the external market demand potential. D: **Incorrect:** Existing products' market shares describe current competition rather than the total size of the target market for a new product.

**1. How the `SUBTOTAL` Function Works in Excel:** - Syntax: `=SUBTOTAL(function_num, ref1, ...)`. - Depending on `function_num` (`1` to `11` or `101` to `111`), `SUBTOTAL` can perform **11 different aggregations**, including `AVERAGE` (1), `COUNT` (2), `COUNTA` (3), `MAX` (4), `MIN` (5), `SUM` (9), `STDEV` (7), etc. - Crucially, `SUBTOTAL` **always ignores rows hidden by AutoFilter** (filtered-out rows), making it ideal for dynamic summaries of filtered tables. **Answer: SUBTOTAL can perform various calculations, including sum, average, count, and more, and it ignores rows hidden by filters (Option B).** A: **Incorrect:** `SUBTOTAL` is not limited to `SUM`; it supports 11 operations including average, count, min, max, and standard deviation. B: **Correct:** `SUBTOTAL` supports multiple summary functions (sum, average, count, etc.) and automatically excludes rows hidden by a filter. C: **Incorrect:** `SUBTOTAL` ignores rows hidden by filters (and ONLY includes visible rows). D: **Incorrect:** `SUBTOTAL` operates on a specified cell range and excludes filtered-out rows rather than including hidden filtered rows.

**1. Understand What a Pivot Table Does and Does Not Do:** - A **Pivot Table** is a read-only analytical summary tool in spreadsheets that allows you to: - **Filter** data to focus on specific subsets (Option A), - **Group** records by categories, dates, or numerical ranges (Option C), and - **Summarize/Aggregate** numerical fields using sum, count, average, etc. (Option D). - A Pivot Table **never modifies or creates new records in the original source dataset** (Option B). **Answer: Creating new records in the original dataset (Option B).** A: **Incorrect:** Filtering records via report filters or slicers is a standard pivot table feature. B: **Correct:** Pivot tables only summarize and aggregate existing source data; they do not insert or create new records in the underlying dataset. C: **Incorrect:** Grouping rows and columns by categories or date intervals is a core pivot table operation. D: **Incorrect:** Aggregating values using sum, count, or average is the primary purpose of a pivot table.

**1. Identify the Chart Type That Encodes Data Intensity/Magnitude via Size:** - A **Bubble Chart** extends a 2D scatter plot by adding a third numerical dimension: the **size (area) of each plotted bubble** represents the magnitude, volume, or intensity of the data point at \( (x, y) \). **Answer: Bubble Chart (Option B).** A: **Incorrect:** A standard scatter plot uses uniform-sized points to show the relationship between two variables \( (x, y) \) without varying point size. B: **Correct:** A Bubble Chart uses the size/area of each bubble to represent the magnitude or intensity of a third variable. C: **Incorrect:** A histogram displays the frequency distribution of a single continuous variable across bins. D: **Incorrect:** A box plot displays the five-number summary (quartiles and median) of a distribution.

**1. Define SOR in Retail and E-Commerce:** - In retail and e-commerce inventory models, **SOR** stands for **Sale or Return**. - Under a Sale or Return arrangement, a supplier provides goods to a retailer or platform, and the retailer only pays for the merchandise that is actually sold to end customers, retaining the right to return unsold inventory to the supplier. **Answer: Sale or Return (Option B).** A: **Incorrect:** 'Sales of Resource' is not a standard e-commerce term. B: **Correct:** SOR stands for 'Sale or Return', an arrangement where unsold goods can be returned to the vendor/supplier. C: **Incorrect:** The grammatical and industry term is 'Sale or Return', not 'Sales of Return'. D: **Incorrect:** 'Sales on Regular' is not what SOR stands for.

**1. Contrast a Niche Market with a Mass Market:** - **Niche Market:** Focuses on a small, narrowly defined segment of consumers who have specific, specialized preferences or needs. - **Mass Market:** Targets a large, broad population of consumers with general, everyday needs using standardized products. - This is accurately described by **Option A**. **Answer: A niche market targets a small, specific group of customers with specialized needs, while a mass market targets a large, broad group of customers with general needs (Option A).** A: **Correct:** A niche market serves a narrow segment with specialized needs, whereas a mass market serves a wide audience with general needs. B: **Incorrect:** This reverses the two concepts: mass markets typically offer lower-priced standardized goods to a wide audience, while niche markets often serve smaller, specialized segments. C: **Incorrect:** Mass markets use large-scale mass media advertising, whereas niche markets rely on highly targeted marketing. D: **Incorrect:** Standardized products for everyone characterize a mass market, whereas tailored/specialized products characterize a niche market.

**1. Compute Total Revenue at Each Income Level:** - **Income Level 1 (Rs 15,000):** - Quantity demanded \( Q_1 = 60 \text{ units} \), Price \( P_1 = \text{Rs } 40 \). - Total Revenue \( TR_1 = Q_1 \times P_1 = 60 \times 40 = \text{Rs } 2400 \). - **Income Level 2 (Rs 25,000):** - Quantity demanded \( Q_2 = 90 \text{ units} \), Price \( P_2 = \text{Rs } 38 \). - Total Revenue \( TR_2 = Q_2 \times P_2 = 90 \times 38 = \text{Rs } 3420 \). **2. Calculate the Relative (Percentage) Change in Total Revenue:** - Following the hint ('if your ans is 98% then enter 0.98'): \[ \text{Relative Change in } TR = \frac{TR_2 - TR_1}{TR_1} = \frac{3420 - 2400}{2400} = \frac{1020}{2400} = 0.425 \text{ (i.e., } 42.5\%\text{)} \] **Answer: 0.425.**

**1. Compute the Three Components of Overall Equipment Effectiveness (OEE):** - **Availability:** - Planned Production Hours (\( \text{PPH} \)) = \( 400 \), Lost Time (\( \text{LOT} \)) = \( 30 \). \[ \text{Availability} = \frac{\text{PPH} - \text{LOT}}{\text{PPH}} = \frac{400 - 30}{400} = \frac{370}{400} = 0.925 \] - **Performance:** - Actual Assembly Speed (\( \text{AA} \)) = \( 160 \text{/hr} \), Designed Assembly Speed (\( \text{DA} \)) = \( 180 \text{/hr} \). \[ \text{Performance} = \frac{\text{AA}}{\text{DA}} = \frac{160}{180} = \frac{8}{9} \approx 0.8889 \] - **Quality:** - Total Units Assembled (\( \text{TU} \)) = \( 8000 \), Defective Units (\( \text{DU} \)) = \( 320 \). \[ \text{Quality} = \frac{\text{TU} - \text{DU}}{\text{TU}} = \frac{8000 - 320}{8000} = \frac{7680}{8000} = 0.96 \] **2. Multiply to Find OEE:** \[ \text{OEE} = \text{Availability} \times \text{Performance} \times \text{Quality} = 0.925 \times \frac{8}{9} \times 0.96 = 0.78933 \approx 0.79 \] **Answer: 0.79.**

**1. Calculate the Re-Order Point (ROP):** - Given inventory parameters: - Daily Usage (\( d \)) = \( 5 \text{ units/day} \) - Lead Time (\( L \)) = \( 10 \text{ days} \) - Safety Stock (\( SS \)) = \( 20 \text{ units} \) - The Re-Order Point formula is: \[ \text{ROP} = (\text{Daily Usage} \times \text{Lead Time}) + \text{Safety Stock} = (5 \times 10) + 20 = 50 + 20 = 70 \text{ units} \] - Therefore, you should initiate a re-order when the inventory level drops to **70 units**. **Answer: Initiate a re-order when the inventory level drops to 70 units (Option A).** A: **Correct:** \( \text{ROP} = (5 \text{ units/day} \times 10 \text{ days}) + 20 \text{ units safety stock} = 70 \text{ units} \). B: **Incorrect:** 50 units is only the lead-time demand \( 5 \times 10 \) without adding the 20 units of safety stock. C: **Incorrect:** 30 units equals \( 50 - 20 \) (subtracting safety stock instead of adding it). D: **Incorrect:** 15 units is below even the safety stock level of 20 units.

**1. Calculate the Number of Days Until Inventory Reaches the Re-Order Point:** - Starting Inventory = \( 100 \text{ units} \). - Re-Order Point (\( \text{ROP} \)) = \( 70 \text{ units} \). - Daily Usage = \( 5 \text{ units/day} \). - Number of units consumed before reaching ROP = \( 100 - 70 = 30 \text{ units} \). - Number of days to trigger the re-order: \[ \text{Days} = \frac{100 - 70}{5} = \frac{30}{5} = 6 \text{ days} \] **Answer: 6.**

**1. Compare the Ratio of Opening Stock to Average Monthly Sales (Days of Inventory):** - Since Days of Inventory is proportional to \( \frac{\text{Opening Stock}}{\text{Avg. Monthly Sales}} \), let us compute this ratio for each candidate SKU: - **LAP002:** \( \frac{250}{105.0} \approx 2.381 \text{ months} \) - **LAP003:** \( \frac{190}{75.0} \approx 2.533 \text{ months} \) (**Highest**) - **LAP006:** \( \frac{50}{22.5} \approx 2.222 \text{ months} \) - **LAP007:** \( \frac{40}{17.5} \approx 2.286 \text{ months} \) - Therefore, **LAP003** has the highest Days of Inventory. **Answer: LAP003 (Option B).** A: **Incorrect:** For LAP002, \( \frac{250}{105} \approx 2.38 \), which is less than LAP003's \( 2.53 \). B: **Correct:** LAP003 has the highest ratio of Opening Stock to Average Monthly Sales (\( \frac{190}{75} \approx 2.533 \) months, or \( \approx 76 \) days). C: **Incorrect:** For LAP006, \( \frac{50}{22.5} \approx 2.22 \), which is lower than LAP003. D: **Incorrect:** For LAP007, \( \frac{40}{17.5} \approx 2.29 \), which is lower than LAP003.

**1. Compute Total Units Sold and Total Revenue for LAP005:** - From the table for **LAP005 (EclipseBook x360)**: - Unit Price = \( \$1,499 \). - Monthly Sales across Months 1 to 6 = \( 20 + 30 + 40 + 50 + 60 + 70 = 270 \text{ units} \) (or \( 6 \times 45.0 = 270 \text{ units} \)). - Total Revenue for LAP005 = \( 270 \times 1,499 = \$404,730 \). **2. Calculate Total Profit at 10% Profit Margin:** \[ \text{Total Profit} = 10\% \times 404,730 = 0.10 \times 404,730 = 40,473 \] **Answer: 40473 (Option D).** A: **Incorrect:** 42,505 does not match \( 0.10 \times 270 \times 1499 \). B: **Incorrect:** 40,890 does not match \( 0.10 \times 270 \times 1499 \). C: **Incorrect:** 41,782 does not match \( 0.10 \times 270 \times 1499 \). D: **Correct:** Total units of LAP005 sold = \( 270 \); total revenue = \( 270 \times 1499 = 404,730 \); 10% profit = \( 40,473 \).

**1. Compute Total 6-Month Revenue for Each SKU:** - **LAP001:** Total units = \( 100+120+150+180+200+220 = 970 \), Price = \( \$1,299 \implies \text{Rev}_1 = 970 \times 1299 = \$1,260,030 \) - **LAP002:** Total units = \( 6 \times 105 = 630 \), Price = \( \$1,799 \implies \text{Rev}_2 = 630 \times 1799 = \$1,133,370 \) - **LAP003:** Total units = \( 6 \times 75 = 450 \), Price = \( \$2,499 \implies \text{Rev}_3 = 450 \times 2499 = \$1,124,550 \) - **LAP004:** Total units = \( 6 \times 55 = 330 \), Price = \( \$1,999 \implies \text{Rev}_4 = 330 \times 1999 = \$659,670 \) - **LAP005:** Total units = \( 6 \times 45 = 270 \), Price = \( \$1,499 \implies \text{Rev}_5 = 270 \times 1499 = \$404,730 \) - **LAP006:** Total units = \( 6 \times 22.5 = 135 \), Price = \( \$1,199 \implies \text{Rev}_6 = 135 \times 1199 = \$161,865 \) - **LAP007:** Total units = \( 6 \times 17.5 = 105 \), Price = \( \$999 \implies \text{Rev}_7 = 105 \times 999 = \$104,895 \) **2. Calculate Combined Percentage for LAP004 and LAP005:** - Combined revenue of LAP004 and LAP005 = \( 659,670 + 404,730 = \$1,064,400 \). - Total revenue across all 7 SKUs = \( 1,260,030 + 1,133,370 + 1,124,550 + 659,670 + 404,730 + 161,865 + 104,895 = \$4,849,110 \). - Percentage share = \( \frac{1,064,400}{4,849,110} \times 100\% \approx 21.95\% \approx 20\% \). **Answer: 20% (Option B).** A: **Incorrect:** 10% is too low; LAP004 alone contributes ~13.6% of total revenue. B: **Correct:** LAP004 (\( \$659,670 \)) and LAP005 (\( \$404,730 \)) together generate \( \$1,064,400 \) out of \( \$4,849,110 \) (~22%, closest to 20%). C: **Incorrect:** 15% underestimates the combined share (~22%). D: **Incorrect:** 25% is higher than the closest rounded option (20%) in the official key.