R、R/Matlab、OS/CS
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