Excel Questions Business Analysis

2

MIDTERM EXAM — EXCEL ANALYTICS APPLICATION

Total Points: 200

Time Expectation: 2–3 Hours

Instructions : Complete ALL work in Excel. Show formulas and results clearly. Each question should be solved on a separate sheet. Label worksheets logically (scenario 1, scenario 2, etc.). Include final decision statements where required.

SCENARIO 1 — Capital Investment Portfolio Decision (NPV)

NorthPeak is evaluating three automation investments and must select the best financial option.

Year	Project A	Project B	Project C
0	-1400000	-1650000	-2100000
1	380000	470000	590000
2	420000	520000	640000
3	470000	580000	710000
4	520000	630000	770000
5	600000	710000	850000

Discount Rate: 9%

Calculate NPV for each project.

Create NPV comparison chart.

Recommend which project should be selected and justify.

SCENARIO 2 — Break-Even Launch Decision

Finance must determine minimum sales required before product launch approval.

Variable	Value
Selling Price	72
Variable Cost	41
Fixed Manufacturing	1050000
Fixed Marketing	480000

Calculate contribution margin.

Calculate total fixed cost.

Calculate break-even units and revenue.

Create break-even chart.

Determine if 60,000 units annual sales is acceptable and justify.

SCENARIO 3 — Pareto Quality Decision

Defect	Count
Seal Leak	175
Sensor Error	132
Bluetooth Failure	94
Battery Seating	70
Packaging Damage	51
Cosmetic Scratch	34
Label Error	26
App Sync	22
Other	18

Sort defect data by frequency.

Calculate cumulative totals and percentages.

Create Pareto chart.

Identify priority defects and justify decision.

SCENARIO 4 — Conditional Formatting Performance Risk

Production Line	Defect Rate %
Line A	1.2
Line B	2.9
Line C	1.7
Line D	3.6
Line E	0.9

Apply conditional formatting to highlight highest defect rates.

Create color scale visualization.

Identify lines needing immediate review and justify.

SCENARIO 5 — Excel Modeling Best Practices (Appendix 1)

Use absolute references in at least one formula.

Use named ranges for cost variables.

Use fill handle to expand formulas.

Explain how these reduce modeling errors.

SCENARIO 6 — Sales Data Visualization and Strategy

Month	West	Central	East	Online
Jan	4200	3100	2900	5200
Feb	4500	2950	3050	5600
Mar	4800	3300	3250	6100
Apr	5200	3500	3600	6400
May	5600	3800	3950	7000
Jun	6100	4100	4200	7600

Create clustered column chart comparing regions.

Create line chart showing monthly trend.

Create pie or stacked chart for channel contribution.

Interpret patterns and recommend one strategy.

SCENARIO 7 — Basic Excel Calculations (Chapter 2)

Units	Unit Cost	Total Cost
1500	34
2200	35
3100	33
2800	36

Calculate total cost using formulas.

Use SUM to calculate total cost.

Identify highest cost batch and interpret impact.

SCENARIO 8 — Supplier Lookup Decision

Supplier	Component A	Component B	Component C
Alpha	12.5	8.4	5.2
Beta	13.1	7.9	5.6
Gamma	11.9	8.75	5.1
Delta	13.6	8.1	5.45

Build lookup model retrieving component cost by supplier.

Test with 3 suppliers.

Identify lowest total cost supplier and justify.

SCENARIO 9 — Production Table Risk Analysis

Order ID	Region	Units	Unit Cost	Supplier
2101	West	2200	34	Alpha
2102	East	3100	37	Beta
2103	Central	2800	33	Gamma
2104	Online	4500	38	Delta
2105	West	2600	36	Beta
2106	Central	3000	34	Alpha
2107	East	3400	39	Delta

Convert dataset into Excel table.

Sort by units descending.

Filter unit cost >= 37.

Identify highest cost risk orders and justify.

SCENARIO 10 — Pricing Revenue Optimization Decision

Price	Demand
55	9200
60	8700
65	8100
70	7400
75	6900
80	6200

Build revenue model (Price × Demand).

Create revenue vs price chart.

Identify revenue maximizing price.

Recommend pricing strategy and justify.