ISYS104 Introduction to Business Information Systems 代写

  • 100%原创包过,高质代写&免费提供Turnitin报告--24小时客服QQ&微信:120591129
  • ISYS104 Introduction to Business Information Systems 代写

    ISYS104 Introduction to Business Information Systems
    2017 Assignment 1 Specification (Advanced Spreadsheets)
    Due:  Monday, 3 April 2017, 9:00 AM
    To be submitted by 3 rd April 2017 9am and
    To be presented to your tutor in your workshop class in week 6
    Software Used: Microsoft Excel
    Assignment1 worth 10%marks
    In this assignment, you will:
     Be able to utilise multiple sheets within a spreadsheet
     Perform calculations across multiple sheets
     Perform advanced calculations
     Use conditional formatting to highlight information
     Create a pivot table and pivot chart
    An Overview of the Data
    The A1datafile in the workbook (available on ilearn) represents the sales records of an
    Australian company selling computers, helpdesk systems, printers, servers, software and
    toner cartridges, for a period of 6 months. The purpose of this assignment is to summarize the
    half yearly sale records to create meaningful data that can be used to make business
    decisions.
    Setup
    1. Make a copy in your directory of the Excel workbook A1datafile.xls
    2. Create a new sheet called “Coversheet”.
    3. On‘Coversheet’ sheet, cell A1, enter you student number.
    4. On‘Coversheet’ sheet, cell B1, enter your name.
    Assessment
    Your practical class supervisor will mark your assignment in your scheduled practical class in
    week 6. You should have your excel file opened ready for assessment when you ask your
    practical supervisor to mark it. There is no need to print anything for assessment of this
    assignment. Your practical teacher will ask you a range of questions to see if you actually
    understand the material. This practical part of the assignment is worth 3 marks, calculations
    and operations are worth 7 marks in total. Calculations and Operations are divided into 2
    Sections which are 3.5 marks each. You will gain 0.5 marks for each item in these sections.
    Section-1: Basic Calculations (3.5 marks - 0.5 marks each)
    ISYS104 Introduction to Business Information Systems 代写
     
    In Business Statements sheet:
    1. Calculate the total number of sales for each month and each product over 6 month
    period using SUM function. Calculate the average number of sales for each product
    over 6 month period using AVG function. Round the average number of sales for
    each product to 1 decimal.
    a. What is the total number of sales for the first half year? Highlight this cell.
    b. What is the average number of sales for the first half year? Highlight this cell.
    2. Merge cells A1 to I3 and Insert a heading named ‘Macquarie Computer Sales’ and
    change the format by choosing a different font size and colour. Insert an appropriate
    clipart under the heading and resize it so that it fits in between A2 and A4.
    3. Change the formats of headings in Row 4 and Column A to bold and a different text
    font.
    4. Insert the right formulas in Rows 11-13, Column H and I, to calculate minimum and
    maximum sales, in addition to the total sales.
    a. Which month and product hold the lowest number of sales? Calculate this in
    I12.
    b. Which month and product hold the highest number of sales? Calculate this in
    I13.
    5. You need to use Absolute Cell Referencing in Row 14 using the value given in K1
    (Total * Depreciation Value). (Hint: An absolute cell reference in a formula, such as
    $A$1, always refer to a cell in a specific location.)
    6. All the numerical values need to be in Currency (Dollar) Format and 1 decimal point.
    7. Create Borders in and around the data area
    Section-2: Advanced Excel Operations (3.5 marks - 0.5 marks each)
    1. In ‘Sort Me’ sheet, sort Sale by Descending Order.
    a. Which business partner holds the greatest sale record in given 6 months?
    Highlight the row.
    2. In ‘Multi-sorting’ sheet, sort by the following order:
    a. Business Partner (Ascending)
    b. Region (Descending)
    c. Sale (Ascending)
    3. In ‘Format’ sheet, sort Sale by Ascending Order and highlight cells (Sales Column)
    for the worst 10 sales to identify the business partners, regions, and months.
    4. In ‘Software Megamart’ sheet, display only sales by ‘Software Megamart’ Business.
    5. In ‘Subtotal’ sheet, find Subtotal of sales by ‘Business Partner’, Enter a row after each
    sorted Business partner and indicate the Subtotal in Column A and calculate the
    Subtotal in Column D.
    6. In ‘If-Statements’ sheet, Count the sales in the region ‘East’ and Count the Sum of
    sales greater than $999 by ‘Toner Guru’s’ (referring to the Subtotal Sheet).
    7. In ‘Pivot Table’ worksheet, create a Pivot Table that shows for each Business partner,
    a column where their sales for each region is calculated.
    ISYS104 Introduction to Business Information Systems 代写