代写MGTS7202 Information Systems for Management

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

代写MGTS7202 Information Systems for Management