All the questions in the attachment need to be answered
Assignment tasks:

4.1 Calculations:

Using excel formula, calculate the followings in excel worksheet.

1. Total sale amount [based on the Unit selling price (without deducting any discount) and quantity]
2. Discount [based on the Total sale amount calculated on (a) using discount rule in Table 1]
3. Total amount charged to customer [based on total sale amount (a) and discount (b)]
4. Total cost price [based on the Unit cost price and quantity]
5. Commission paid to sales representative [based on the Total amount charged to customer (c) and product category]
6. Total cost amount [based on the Total cost price (d) and Sales rep. commission (e)]
7. Total profit [based on Total amount charged to customer (c) and Total cost amount (f)]

4.2 Analysis and observations:

Mr Scott requires you to analyze the data and help him with the following questions. Use Pivot Table(s) and/or appropriate graph(s)/chart(s) to summarize, visualize and present your observations.

1. Total profit, sub-totalled by each (a) Product category, and (b) Customer type. Use Pivot Table(s) and appropriate graph(s)/chart(s) to summarise these calculations.
2. Who are ABC Superstore’s five best and five worst customers by profit? Use appropriate table(s) and/or graph(s)/chart(s) to summarise these observations.

Â	Corporate	Small Business	Consumer
Office Supplies	0%	0%	0%
Technology	15%	10%	10%
Furniture	15%	10%	10%

3 of 6

Assignment

School Engineering and Information Technology

ITECH1005: Business Information Systems

4.3 Recommendations:

Mr Scott also requires you to make recommendations concerning the following business-level questions.

a. b.

5.

Should ABC Superstore focus on any particular (a) customer segment(s) and (b) Product categories in future, and why?
What are your recommendation regarding what other data should be collected to improve decision making for ABC Superstore?

Assignment requirements:

Mr Scott has asked you to provide him with the details of how you have accomplished these tasks. Therefore, you must include the functions and the formulae you’ve used in your analyses, and not simply report the answers.

As a paid consultant, your submission must be professionally presented; all analyses, calculations and summaries in the Excel file must have headings and supported with explanatory notes; and the formal business report must be preceded by a ‘covering letter’ (i.e. incorporate a letter as first page of your Word document with your report on following pages).

Note: All recommendations in your report must be clearly justified (e.g. include charts/graphs and/or refer to specific analyses/summaries from the Excel spreadsheet).

6. Some Hints

The business options are linked to a number of questions posed by Mr Scott. Your role is to answer these specific questions and to support your answers with your data analysis as presented in the appropriate tables and/or charts. Ensure that your recommendations address the company’s objectives, and that they are clearly linked to your observations.

There is no ‘ideal’ or ‘best’ way of doing this project. You’ll be assessed on your insight of the data, on your ability to make observations by analysing (with formulas) and visualising (with charts) data, and your ability to link your observations to business issues.

There are numerous Microsoft Excel facilities and functions that may be useful in this project. While planning your workbook, you may wish to review the lab exercises on the related topics (e.g. Excel functions, absolute and relative cell referencing, formatting, chartings and managing large worksheets, as well as on the analysis of Excel data and drawing recommendations).

The Excel workbook contains all the necessary data for your assignment. Columns headings required for calculations (Task 4.1) are provided in the Excel workbook. You may have to add (or insert) more columns (for some intermediate calculations) and/or worksheets for the rest of the calculations and observations.

4 of 6

Assignment

School Engineering and Information Technology ITECH1005: Business Information Systems

You will very likely need to analyse beyond the assessable tasks to gain sufficient insight into the business operations to provide recommendations to Mr Scott.

Make sure you review the marking guide (attached at the end of this assignment specification), to ensure you complete all required tasks before submission.

7. Assignment Assessment

The questions posed to you in this assignment range from easy, through medium to challenging. However, it is expected that all students will succeed in all the tasks. Some of the tasks are very similar to those covered in tutorials and the associated exercises. Some of the challenging tasks may rely on insight that requires self-directed study (e.g. using help facilities in Excel to explore in depth some of the topics covered in the tutorials).

This assignment is worth 30% of total marks for this course and will be marked on:

8.

Your demonstrated understanding of the problems; Completeness of the tasks;
Exploration of the features in Microsoft Office; and
Quality of the submission (Excel workbook & Word document).

Submission

All students are required to submit two (2) files – (a) Excel workbook with all calculations, graphs and charts, and (b) well-formatted report on word, for this assignment, via Moodle, prior to the due date as specified in the course description.