BISM7202 Excel Assignment Guide: Database Functions, Solver & Scenario Analysis

Introduction to the BISM7202 Excel Assignment

The BISM7202 Excel assignment challenges you to apply spreadsheet skills to a realistic business case: UrbanEats Delivery (UED). This franchise delivers grocery boxes to homes and businesses. Your task involves summarizing data with database functions, optimizing franchise areas using Excel Solver, analyzing savings scenarios for a new store, and providing business insights. This guide will help you understand the core techniques without copying the solution.

Understanding Database Functions for Summary Tables

Database functions like DSUM, DAVERAGE, DCOUNT, and DMAX are powerful for extracting summarized information from large datasets. In the UED case, you'll have a table of delivery data with fields such as order date, customer suburb, box type, revenue, and distance. The key is to set up a criteria range that specifies conditions (e.g., all deliveries to a specific franchise area in a given month).

Example: Suppose you want the total revenue for franchise area 'Northside' in June 2026. Create a criteria range with column headings matching the data table. In cells below, enter 'Northside' under 'FranchiseArea' and '>=06/01/2026' and '<=06/30/2026' under 'OrderDate'. Then use: =DSUM(DataTable, "Revenue", CriteriaRange). This returns the total revenue meeting both conditions.

Trend Connection: Think of database functions like filtering a social media feed. Just as you set criteria to see only posts from friends or a specific hashtag, DSUM filters your data to sum only the rows that match your conditions.

To make your workbook professional, use named ranges for the data table (e.g., DeliveryData) and criteria range (e.g., Criteria). This makes formulas easier to read and maintain.

Optimizing Franchise Areas with Excel Solver

Solver is an optimization tool that finds the best way to allocate resources. In this assignment, you must minimize the total distance traveled from each store to its assigned franchise areas. You'll have decision variables representing which areas each store serves, constraints (each area served by exactly one store, store capacity limits), and an objective cell (total distance).

Setting up Solver: Use binary variables (0 or 1) for each store-area pair. For example, if you have 3 stores and 5 areas, you need 15 binary cells. The objective cell sums the product of distance and the binary variable for each pair. Constraints: each area's sum of binary variables = 1; each store's sum of binary variables ≤ its capacity.

Running Solver: Go to Data > Solver. Set objective to minimize total distance. Add constraints using the Simplex LP method (since binary variables are linear). Solver will find the optimal allocation. This mimics real-world logistics used by companies like Amazon or Uber Eats to reduce delivery costs.

Trend Connection: In 2026, AI-powered route optimization is huge. Solver is like a simplified version of what ride-sharing apps use to match drivers to riders efficiently.

Scenario Analysis for Saving Monthly or Fortnightly

UED wants to build a new store in a few years. You'll analyze different saving strategies: saving a fixed amount monthly vs. fortnightly, and varying the interest rate. Use the FV (Future Value) function: =FV(rate, nper, pmt, pv, type). For monthly: divide annual rate by 12, nper = years*12, pmt = monthly savings. For fortnightly: divide annual rate by 26, nper = years*26, pmt = fortnightly savings. Compare results to see which yields more.

Scenario Manager: Use Data > What-If Analysis > Scenario Manager to create scenarios like 'Optimistic' (high interest, high savings), 'Realistic', and 'Pessimistic'. Each scenario changes input cells (interest rate, savings amount). Then generate a summary report showing the future value under each scenario. This helps UED decide how much to save and how often.

Trend Connection: With interest rates fluctuating in 2026, scenario analysis is crucial for personal finance apps like Mint or YNAB to help users plan for goals.

Business-Focused Comments and Recommendations

Your final deliverable includes insights. For each analysis, write a short paragraph explaining what the numbers mean for UED. For example: "The Solver optimization reduced total distance by 15%, potentially lowering fuel costs and carbon emissions. We recommend implementing this allocation to improve sustainability." For scenarios: "Saving fortnightly yields slightly higher future value due to more frequent compounding. UED should consider automatic fortnightly transfers."

Use professional formatting: freeze panes on large sheets, use consistent fonts and colors, and include charts (e.g., bar chart comparing scenario outcomes). Named ranges and lookup functions (VLOOKUP, XLOOKUP) make your workbook robust.

Common Pitfalls to Avoid

Hardcoding values that should be cell references (e.g., interest rate typed in formula instead of referencing a cell).
Forgetting to use absolute references ($A$1) when copying formulas across cells.
Not setting up criteria ranges correctly for database functions; column labels must match exactly.
Ignoring Solver constraints like integer or binary requirements; use the 'int' constraint.
Overlooking environmental benefits in recommendations; UED values sustainability.

Final Tips for Success

Start early and test each part separately. Use Excel's help and online resources for functions you're unsure about. Remember, the assignment tests your ability to learn independently. By mastering database functions, Solver, and scenario analysis, you'll gain skills applicable to real business analytics roles. Good luck!