代写MGTS7202 Information Systems for Management
100%原创包过,高质量代写&免费提供Turnitin报告--24小时客服QQ&微信：273427
代写MGTS7202 Information Systems for Management
MGTS7202 Information Systems for Management
Semester 2, 2016
1
Microsoft Office 2013 Excel
Assignment: Technical Component
Your Task
This assignment requires you to complete an Excel workbook file using Microsoft Excel 2013 based
on the specification in this document. The Excel workbook should contain a number of sheets you
develop.
Background and Scenario
At the beginning of each semester, the University’s Campus Bookstore is always crowded because
students search through the bookshelves to select the textbooks for their courses. Often there are
long queues lined up at the cashiers. The crowdedness at peak times does not only hurt service
quality and customer satisfaction, but also creates other issues such as staff scheduling problems
and security concerns.
As a response to these problems, management of the Campus Bookstore has recently set up a web
store. Consumers can now – in a self-serving manager – browser book information, e.g. search a
particular category of books, or a book by a particular author or title, and purchase books online.
Currently, staff manage the web store purchases via email orders. The online store has alleviated
some of the peak load problems, increased sales and customer satisfaction, and reduced operational
costs. However, management believes that online services could be the future of the bookstore.
Therefore, it wishes to encourage students to use the newly developed online services by giving
them incentives, but also wishes to do such that profits will not be negatively affected greatly.
Although a web store was set up, management also want to revamp their computer-based
information systems for staffing, inventory and sales management. It is one of the tasks for this
assignment that you assist the bookstore in this plan.
Documentation Sheet
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. If you do not make any
assumptions, please leave the section empty. Assumptions to be considered when marking must be
logical.
2
Constant Sheet
This sheet contains all the lookup tables that you will need to use in the assignment. Two of the
tables have been completed for you already; the annual tax table and fuel allowance lookup. You
are required to complete the remaining tables. When using lookup tables in your formulas, please
make sure they are accessed using appropriate named ranges.
Annual Tax Table
Tax is withheld using the following tax rates for 2016-17. This information has been entered for
you in the Constant Sheet.
Table 1: Australian Taxable Income Table for 2016-17
Taxable income Tax on this income
$0 - $18,200 Nil
$18,201 - $37,000 19c for each $1 over $18,200
$37,001 - $80,000 $3,572 plus 32.5c for each $1 over $37,000
$80,001 - $180,000 $17,547 plus 37c for each $1 over $80,000
$180,001 and over $57,547 plus 45c for each $1 over $180,000
Fuel Allowance Lookup
Employees are eligible to receive a fuel allowance from the company. The allowance is based on
how far they live away from the office. The monthly fuel allowance for each suburb has been
recorded already for you in the Constant Sheet.
Monthly Book Distribution of Sales Table
Sales within the bookshop are not consistent throughout the year but vary from month to month.
Below is the average percentage of sales for each month for textbooks and non-textbooks within the
shop.
Table 2: Monthly Book Distribution of Sales
January
February
March
April
May
June
July
August
September
October
November
December
Textbook 6% 11% 8% 7% 7% 10% 11% 9% 6% 9% 11% 5%
Non-
Textbook
12% 7% 10% 8% 9% 9% 11% 8% 7% 6% 8% 5%
3
Online Discount Table
The bookshop is considering offering discounts to online sales, doing so will increase online sales
and decrease in-store sales. Below is the predicted increase and decrease for different discounts
offered.
Table 3: Online Discount & Increase/Decrease for Online/In-store
Textbook Non-Textbook
Rate Discount Online
Increase
In-store
Decrease Discount Online
Increase
In-store
Decrease
Low 2.5% 8% 2% 3.5% 10% 4%
Medium 5% 15% 8% 6% 17% 10%
High 7.5% 20% 12% 8.5% 23% 18%
Employee Sheet
The employee sheet keeps track of employees currently employed at the bookshop. Insert formulas
to calculate the age of each employee based on when the spreadsheet is opened. Insert formulas to
calculate the employer superannuation contribution in Australian dollars. Calculate the annual fuel
allowance paid to employees (it is taxable). Calculate the amount of tax withheld. Calculate the
total annual amount deposited into each employee’s bank account.
Book Sales Sheet
This sheet contains a large number of records of book sales. On this sheet you are required to use a
number of formulas to summarise the sales of different categories of books, and you should note
that the “Sales” keyword refers to the number of Sales. Using the records of sales produce a pivot
table that compares the each day’s profit from sales, showing the profit for textbooks and nontextbooks
that are sold online and in-store. The profit should be calculated as part of the pivot table.
The pivot table should be placed on a new sheet and named appropriately. From this pivot table,
create a pivot chart (to be placed as a Chart Sheet and named appropriately). The pivot table and
pivot chart should also allow for the user to filter by ISBN, title and author. The chart should be
presented in a professional format.
Sales Planning Sheet
All sales recorded in the workbook are for the bookshop’s financial month of July (even those in
different calendar months). This sheet is for planning the sales for in-store and online shops when
discounts are offered on online purchases. Using the average book prices and books sold calculated
on the previous sheet, calculate the base number of sales without discounts for a year of sales (see
Cells B12:F24). Use the monthly distribution on the Constants Sheet. Calculate the base profits
without discount using the average profit (Cells B30:F42).
Hint:
4
1. Monthly Sales without discount (MSWD) = (Base Sales Number for July / July’s monthly
distribution percentage) * Lookup month’s distribution percentage
2. Monthly Sales with discount = ( MSWD– Discount*MSWD + Overall Increase/Decrease *
MSWD)
This sheet (right hand side) will be set up for using scenario manager.
I3 – This will be discount rate for sales – Low, Medium, or High.
I4 – This is the base staffing costs – Equal to total salary, superannuation and fuel allowance for
employees as calculated on the Employee Sheet.
I6 – This is the decrease to in-store staffing costs. This is a percentage on the base staffing costs –
Minus 3%, or Minus 5%. (Minus 3% is linked with Plus 2% below, Minus 5% is linked with Plus
3% below.)
I7 – This is the increase in online staffing costs. This is a percentage on the base staffing costs. –
Plus 2%, or Plus 3%
J6 – This is the calculated value of change in in-store staffing costs.
J7 – This is the calculated value of change in online staffing costs.
L3 – This is external pressure overall increase/decrease to sales for the year – Minus 2.5%, Plus
2.5%, Plus 5%.
L4 – This is the calculated value of new staffing costs.
M7 – This is the calculated net profit of the bookshop considering staff costs and book profits.
I12:M25 – This is the calculated number of sales with discount, total and change between with and
without discount.代写MGTS7202 Information Systems for Management
I30:M43 – This is the calculated profit with discounts (the discount applied is to the profit on sales),
total and change between with and without discount.
Using scenario manager produce a report on a new sheet which shows the above (3 x 2 x 3) = 18
scenarios.
Separately to B12:E23, I12:L23, B30:E41, and I30:L41 apply a colour scale conditional formatting
which shows the best cell in green, and the worst in red (middle yellow).
Advanced Sales Planning Sheet
This sheet is similar to the previous sheet but will be set up for using Solver. Complete the left side
of the sheet as per the previous sheet. Use the base staffing costs as per the previous page.
Changing cells
H5, K5 – This is the discount rate for textbooks and non-textbooks on the online store. It changes
between 1% and 10%.
I5, L5 – This is the online percentage increase in sales. It changes between 1.5x the discount rate
and 3.5x the discount rate.
J5, M5 – This is the in-store percentage decrease in sales. It changes between 0.8x the discount rate
and 2x the discount rate. Note that the original value of the changing cells must be 0%.
5
Objective cell
M8 – The aim is to maximise the net profit. This is the calculated net profit of the bookshop
considering staff costs and book profits.
Other cells
I13:M25 – This is the calculated number of sales with discount, total and change between with and
without discount.
I31:M44 – This is the calculated profit with discounts (the discount applied is to the profit on sales),
total and change between with and without discount.
Using Solver produce a report on a new sheet which shows the optimised solution for the net profit.
代写MGTS7202 Information Systems for Management
