代写MGTS7202 Information Systems for Management

  • 100%原创包过,高质代写&免费提供Turnitin报告--24小时客服QQ&微信:120591129
  • 代写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