What are your recommendation regarding what other data should be collected ?, accounting homework help

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.







Small Business



Office Supplies














3 of 6



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.







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



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:






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).


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.

Calculate Price

Price (USD)