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.