BISM7202 Information Systems for Management
Case Specification: MS Office Excel Assignment
SEMESTER 2, 2025
Specification
1. Workbook
This document outlines the detailed specifications for your assignment and should be used alongside the Assessment Guideline document. An Excel template is provided on the BISM7202 Ultra Blackboard site, and you must use this template as the foundation for your work.
You are permitted to adjust the visual formatting of the worksheets to produce a polished, professional result. This includes correcting typos, changing fonts or colors, adjusting data formats, and modifying column widths. However, do not add or remove any rows or columns unless specifically instructed to do so. You are also expected to define named ranges where appropriate.
To achieve a high-quality submission, ensure you:
o Use freeze panes for easier navigation on long sheets
o Apply lookup functions (instead of nested IFs, where suitable)
o Avoid unnecessary hardcoding
o Format your graphs with clear titles and axis labels
o Use consistent and readable color schemes and layout
You should rely on the Excel functions and techniques introduced in tutorials. However, not all required functions will be covered directly in class. You are expected to build on your tutorial knowledge through:
o Pre-tutorial readings
o Preparation exercises
o Excel’s built-in help features or online resources
The assignment is designed to assess both your technical skills and your ability to learn independently using Excel.
Technical Requirements:
o Your completed work must be submitted as a Microsoft Excel file in .xlsx format.
o Do not submit files in other formats such as .xlsm, .xlsb, .xls, OpenOffice Calc, or Google Sheets — these will not be accepted or opened.
o The Excel file will include multiple worksheets, which you are required to complete based on the specifications provided in this document.
o If you are working on your personal computer, it is strongly recommended that you test your file on university lab computers to ensure full compatibility with Microsoft 365 Excel / Excel 2019.
2. Overview of the Case Scenario
An innovative, new company called BrisMart Solutions (BMS) has been established to provide door-to-door retail product delivery services to urban neighborhoods of Brisbane. BMS is set up as a franchise, meaning there is a ‘head’ franchisor with several franchisees operating ‘under license’ from the franchisor. BMS has four franchisees in Brisbane, each of which is a local retail store.
As franchisees of BMS, they are licensed to deliver daily retail essentials, such as household products, snacks, groceries and beverages, to homes and businesses within their designated areas (their ‘franchise territory’). Over its three years of operation, BMS has developed a reliable reputation among its customer bases. BMS customers receive a ‘customized retail box’ each week via their membership subscription program. The length of membership varies in terms of 12, 24, or 36 weeks, and subscription fees vary accordingly.
Mr. Ryan Fletcher, the owner of BrisMart Solutions (BMS), has asked you to develop a spreadsheet that will help refine the franchise areas and minimize the distance travelled. Ryan is very environmentally conscious and does not want to harm the planet while delivering retail products. He wants you to:
1. Develop a Schedule of Employee Budgeted Salary Costs and Build a Summary Table:
a. Create a detailed schedule of employee salaries according to his specifications.
b. Summarize the data using database functions in a summary table.
2. Undertake a Solver Analysis on Business Franchise Areas:
a. Use Excel's Solver tool to analyse and reallocate franchise areas to minimize the distance travelled from each store.
3. Undertake a Scenario Analysis for Saving Monthly/Fortnightly:
a. Analyse different scenarios for saving money either monthly or fortnightly to fund the construction of a new warehouse or retail store, estimating the required amount in a few years.
4. Provide Business-Focused Comments:
a. Offer insights and recommendations based on the spreadsheet analysis, focusing on the business impact and environmental benefits
5. Simulate Future Staffing Needs Using Synthetic Data:
a. To support potential expansion, Ryan is also exploring how the payroll model scales with additional employees. You are invited to use Generative AI to create synthetic employee records and reflect on their integration and implications for business growth.
3. List of Sheets in Excel Workbook
Your template Excel file already includes the following worksheets:
· Document Control
· Constant
· Employees
· Payroll Summary
· Current Franchise Distribution
· Franchise Redistribution (to be renamed by the student as per instructions)
· New Store Investment
· Comments to Ryan
In your final submitted Excel workbook, you must also create and add the following additional worksheets as instructed in this document:
· Pivot Table -Payment
· Pivot Chart-Payment
· Franchise Redistribution Solver
· Solver Analysis Answer Report
· Pivot Table -Redistribution
· Pivot Chart- Redistribution
· Scenario Summary
Task–Tutorial Mapping (High-Level Guide)
· This table is provided as a quick guide only. It does not include every task or reference in full detail, and some tasks may be linked to more than one tutorial. Do not rely solely on this table, you must attend tutorials and review the weekly content to complete the assignment correctly.
· Final Note: Tutorial numbers are always one less than the teaching week (e.g., Teaching Week 5 = Tutorial 4)
|
Sheet Name
|
Tutorial #
|
|
Constant
|
TUT 3
|
|
Employees Sheet
|
TUT1, TUT 2, TUT 7, TUT 8, TUT 9
|
|
Payroll Summary & PivotTable & PivotChart 1
|
TUT 5, TUT 11
|
|
Current Franchise Distribution
|
TUT 1, TUT 2, TUT 4, TUT 7, TUT 8
|
|
Franchise Redistribution
|
TUT 1, TUT 2, TUT 4, TUT 6, TUT 8
|
|
Solver Analysis Answer Report
|
TUT 6
|
|
PivotTable & PivotChart- Franchise Redistribution
|
TUT 5, TUT 11
|
|
New Store Investment
|
TUT 1, TUT 2, TUT 4, TUT 6, TUT 7
|
|
Scenario Report/Scenario Summary
|
TUT 6
|
Hint: Throughout the spreadsheet,
· Cells with a light shaded blue background require you to enter value or a formula in them or take some actions with them.
· Cells with a light-orange background are to be populated by either the Solver or Scenario Manager tools, not you.
· Cells with no color background should not be edited or changed by you unless explicitly directed to do so in this specification document.