BISM7202 Information Systems for Management
Semester 2, 2018
1
Case Specification: Computer-Based Assessment Microsoft Office Assignment Excel 2016
Specification
Template
This specification document is a companion document to the Assessment Guideline.
The Excel template of the expected worksheets are 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 colour, fonts, data format presentation, column widths, etc) to provide a professional finished product, but nothing else. For example, do not change the workbook’s structure or cell locations (except when you are asked to do so).
This assignment requires you to complete an Excel workbook file using Microsoft Excel 2016 / Microsoft Excel 365 based on the specification in this document. The Excel workbook contains several sheets you should develop. These sheets are described as follows.
Background and Scenario
An innovative, fairly new, company called Gary’s Green Grocers to Go (GGG2G) has been set up that provides a door-to-door vegetable delivery service to the inner-city suburbs of Brisbane. GGG2G is set up as a franchise, which means that there is a ‘head’ franchisor with several franchisees who operate ‘under license’ from the franchisor. GGG2G has four franchisees in Brisbane. Each franchisee is a local vegetable shop.
As a franchisee of GGG2G, they are licenced to deliver fresh vegetables by the box to homes and businesses in nearby suburbs (their ‘franchise area’). Over its two years of operation, GGG2G has built up a trustworthy reputation among its customer base. GGG2G customers receive a ‘set vegetable’ box of in-season vegetables each week via their membership subscription program. The length of membership varies in terms of 13, 26, or 52 weeks, and subscription fees vary by membership terms.
Winona Trescott, the owner of GGG2G, has asked you to develop a spreadsheet that will help refine the franchise area and lower the distance travelled. Winona is very environmentally conscious and does not want to damage the planet to deliver vegetables. She wants you to:
(1) Develop a schedule of employee salary costs according to her specifications;
(2) Complete the workbook analysing the business franchise areas to determine a reallocation of franchise areas by distance from the store;
(3) Undertake a scenario analysis for obtaining loans for a proposed capital expansion; and
(4) Provide some business-focussed comments to Winona relating to the process of undertaking the preceding analysis.
BISM7202 Information Systems for Management
Semester 2, 2018
2
List of Sheets in Excel Workbook
When submitted, your final solution will have the following sheets:
• Document Control
• Constant
• Employees
• Current Franchise Distribution
• Franchise Redistribution
• Answer Report
• Pivot Table
• Pivot Chart
• Capital Expansion
• Scenario Summary
• Comments to Winona
Sheets in italics need to be created by you as they are not in the template file.
Document Control Sheet Hint: Cells with a light shaded green background require you to enter a value or a formula in them, or take some action with them. Cells with a yellow background are to be populated by either the Solver or Scenario Manager tools.
First enter your details: Student name and student number.
In addition, list any assumptions that you have made when you developed your assignment. 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.
Constant Sheet
This sheet contains all the lookup tables that you will need to use in the assignment. When using lookup tables in your formulas from the Constant sheet, make sure they are accessed using appropriate named ranges.
There are 9 lookup tables or values contained in this Constant Sheet. You are to complete these as directed below.
Employee Salary Table
Employees are paid at different rates based on their job title. Each job comes with a different employer superannuation percentage rate. The details of the different job descriptions are presented below.
You are required to complete the data entry of the table in the workbook.
BISM7202 Information Systems for Management
Semester 2, 2018
3
Table 1: Employee Salary Table for 2018-19
Job Title
Annual Salary
Employer Super
Commission Pool Rate
Accountant
$75,854
15%
0.25%
Operation Manager
$60,461
12%
0.25%
Owner
$113,496
20%
5%
Delivery Service Manager
$65,070
12.5%
2%
IT Manager
$77,255
11.5%
0.25%
Franchisee Manager
$90,457
14%
1%
Senior Delivery Service Manager
$70,480
9.5%
3%
All employees receive a sales commission. The commission received is determined by the employee’s Commission Pool Rate, which is multiplied by the store’s Commission Pool to determine Commission. The Commission Pool is determined by the Commission Pool Percentage. Currently, the Commission Pool Percentage is 3%.
For example, a store might make $5,000,000 in revenue. $150,000, or 3% of that revenue, is placed into the Commission Pool. A Delivery Service Manager with 2% Commission Pool Rate would receive 2% of $150,000 as sales commission, or $3,000, as a commission.
Commission Pool Percentage
The Commission Pool Percentage is 3%. It is used to calculate the Revenue Pool and the commissions paid according to each employee’s Commission Pool (determined by Job Title).
You are required to complete the data entry of the table in the workbook.
Annual Tax Table
Tax is withheld using the following tax rates for 2018-19. This information has been entered for you in the Constants Sheet.
Table 2: Australian Taxable Income Table for 2018-19
Taxable Income
Tax on this Income
$0 - $18,200
Nil
$18,201 - $37,000
19c for each $1 over $18,200
$37,001 - $90,000
$3,572 plus 32.5c for each $1 over $37,000
$90,001 - $180,000
$20,797 plus 37c for each $1 over $90,000
$180,001 and over
$54,097 plus 45c for each $1 over $180,000 Note: The above rates do not include the Medicare levy of 2%.
BISM7202 Information Systems for Management
Semester 2, 2018