BISM7202 Information Systems for Management
Case Specification: MS Office Excel Assignment
SEMESTER 2, 2024
Specification
Workbook
This specification document is a companion document to the Assessment Guideline.
The Excel template of the expected worksheet is available on the BISM7202 Blackboard site.The Excel template provided must be used as the basis for the assignment.
You may change the visual formatting (correct typographical errors, change color, fonts, data format presentation, column widths, etc.) of the worksheets to provide a professional finished product, and will need to define named ranges, but nothing else unless asked to do so. For example, do not add or remove rows or columns.
You would be well advised to make your work of the highest presentation quality (e.g., freeze panes to long pages, use named ranges where appropriate, use lookup functions instead of nested IFs where appropriate, use hard coding only where appropriate, use appropriate fonts and colors, graph axes and titles, etc.).
Note: Entering student name and student number in the Document Control Sheetshould be the first step that you do in undertaking this assignment.
When you develop your solution, you should use (but are not limited to) the functions and features you were taught in the tutorials. If you need functions or techniques that are not addressed explicitly in tutorial exercises, you should explore your pre-tutorial reading materials and preparation exercises or refer to the help component of Excel (or conduct a web search).
Aspects of the assignment have purposefully been designed to train and test a student’s self- learning ability with a software application, and thus it is possible that not all the functions you need to use have been directly addressed in a tutorial exercise.
This assignment requires you to complete an Excel workbook file using Microsoft 365 Excel / Excel 2019 based on the specification in this document. The Excel workbook contains several sheets you should develop. These sheets are described as follows.
It is highly recommended that, prior to assignment submission, you check that your solution works on the university machines if you have developed it on your own machine.
Saving your files using the default Excel filetype (i.e., xslx) is mandatory. Files saved in other formats, e.g., xlsm, xslb, xls, calc, google sheets, etc., will not be opened.
Overview of the Case Scenario
An innovative, new company called UrbanEats Delivery (UED) has been established to provide a door-to-door grocery delivery service to the urban neighbourhoods of Brisbane. UED is set up as a franchise, meaning there is a ‘head’ franchisor with several franchisees operating under license from the franchisor. UED has five franchisees in Brisbane, each of which is a local grocery shop.
As franchisees of UED, they are licensed to deliver daily groceries in boxes to homes and businesses within their designated areas (their ‘franchise territory’). Over its three years of operation, UED has developed a reliable reputation among its customer bases. UED customers receive a ‘customized grocery’ 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. Michael Davis, the owner of UrbanEats Delivery (UED), has asked you to develop a spreadsheet that will help refine the franchise areas and minimize the distance travelled. Michael is very environmentally conscious and does not want to harm the planet while delivering groceries. He wants you to:
1. Develop a Schedule of Employee Budgeted Salary Costs and Build a Summary Table:
o Create a detailed schedule of employee salaries according to his specifications.
o Summarize the data using database functions in a summary table.
2. Undertake a Solver Analysis on Business Franchise Areas:
o 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:
o Analyse different scenarios for saving money either monthly or fortnightly to fund the construction of a new store ‘What If”, estimating the required amount in a few years.
4. Provide Business-Focused Comments:
o Offer insights and recommendations based on the spreadsheet analysis, focusing on the business impact and environmental benefits.
List of Sheets in Excel Workbook
When submitted, your final solution will have the following sheets:
• Document Control
• Constant
• Employees
• Payroll Summary
• Pivot Table
• Pivot Chart
• Current Franchise Distribution
• Franchise Redistribution
• Solver Analysis Answer Report
• Pivot Table
• Pivot Chart
• New Store Investment
• Scenario Summary
• Comments to Michael
Sheets highlighted in italics, need to be created by following instructions in this document, as they are not in the template file.
Document Control Sheet
|
Hint: Throughout the spreadsheet, cells with a light shaded orange background require youto enter a 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 colour background should not be edited or changed by you unless explicitly directed to do so in this specification document.
|
First enter your details: Student name and student number.
In addition, you should list any assumptions that you have made when you developed your assignment on this sheet. The assumptions allow examiners to understand your work in context.
You should use these assumptions to resolve any ambiguities you might identify in this Case Specification.
The assumptions you make must be logical and consistent with the scenario provided in this Case Specification.
If you do not make any assumptions, please leave this section empty.