代写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