Business Information Systems Assignment
Aims: To analyse a set of data (in Microsoft Excel), and write a brief report (in Microsoft Word), identifying and explaining your insights into the operation of ABC Superstore Victoria.
Learning Objectives: In the process of this assessment task you will:
- Plan, schedule and execute project tasks with a view to improve your personal productivity;
- Gain awareness of some typical issues related to the operation of a small-to-medium size business;
- Use the functionality of Microsoft Excel 2007/2010 to manipulate data, analyse it and visualise it in tabular and chart form; and
- Use the functionality of Microsoft Word to write a brief report of your business observations and recommendations.
ABC Superstore Victoria: Analysing Sales Data
Introduction
ABC Superstore Victoria is a medium-sized retailer of furniture, technology and office supplies. It is selling its products to consumers, smaller businesses and corporate houses through its three stores around Melbourne and Geelong. The company's founder, Mr David Scott, like all business owners, is always interested in finding ways to increase revenues and decrease expenses.
Mr Scott has recently hired you as a business analyst and poses some operational-level questions about the performance of his business. He is also eager to hear your thoughts and ideas on how to improve the business and requires you to make recommendations on how to improve the company's performance, especially in relation to the following business objectives:
- Improving the sales strategy;
- Improving internal efficiencies and effectiveness; and
- Building strong lasting relationships with its customers
Mr. Scott has provided you with last year's sales information in the "ITECH1005-Assignment-data-S1-2017.xlsx" file. The data needs to be analysed and visualised to help observations related to the business operation and its shortcomings.
The data worksheet "Sales" contains collected information of the ABC Superstore's sales in 2016. These details include:
- Order ID
- Customer Name
- Customer Segment
- Product Name
- Product Category
- Order Quantity
- Unit Cost Price
- Unit Selling Price
Note the followings:
- Order ID is the identification of each order - an order includes one or many products.
- Customer Segment is either Corporate, Small Business or Consumer - three types of customers
- Product Category is either Office Supplies, Technology, Furniture - three categories of products
- Unit Cost Price is the price that ABC Superstore paid for each item of a product to its manufacturer or supplier
- Unit Selling Price is the price that ABC Superstore charges customers for each item of a product
ABC Superstores offers a discount to its customers on products under Technology and Furniture categories (there is no discount on products under Office Supplies category). On Technology and Furniture products, corporate customers receive 15% discount and other customers (Small Business and Consumer) receive 10% discount on the total sale amount. The discount policy is summarized in Table 1.
|
|
Corporate
|
Small Business
|
Consumer
|
|
Office Supplies
|
0%
|
0%
|
0%
|
|
Technology
|
15%
|
10%
|
10%
|
|
Furniture
|
15%
|
10%
|
10%
|
|
Table 1: ABC Superstore's discount policy
|
ABC Superstore offers commission to its sales representatives. Sales representatives receive a commission of 5% of the total amount charged to customer on Technology and Furniture products only (i.e., no commission is offered on Office Supplies).
Assignment tasks:
1. Calculations: Using excel formula, calculate the followings in excel worksheet.
a. Total sale amount [based on the Unit selling price (without deducting any discount) and quantity]
b. Discount [based on the Total sale amount calculated on (a) using discount rule in Table 1]
c. Total amount charged to customer [based on total sale amount (a) and discount (b)]
d. Total cost price [based on the Unit cost price and quantity]
e. Commission paid to sales representative [based on the Total amount charged to customer (c) and product category]
f. Total cost amount [based on the Total cost price (d) and Sales rep. commission (e)]
g. Total profit [based on Total amount charged to customer (c) and Total cost amount (f)]
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.
a. 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.
b. 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.
3. Recommendations: Mr Scott also requires you to make recommendations concerning the following business-level questions.
a. Should ABC Superstore focus on any particular (a) customer segment(s) and (b) Product categories in future, and why?
b. 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).
Attachment:- Assignment Files.rar