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