ITECH1005-5005 Business Information Systems Assignment

ITECH1005-5005 Business Information Systems Assignment

ITECH1005-5005 Business Information Systems Assignment

Task 1: Calculations

I. Perform the following calculations using formula:

No.

Basis for calculations

Total amount in AUD $

1

Service fee for each value added services

Refer excel calculations attached along with

2

Cost for organising each V.A.S

Refer excel calculations attached along with

3

Total revenue from ticket sales

$ 43,480.50

4

Commission received from space rent

$ 90,756.00

5

Sales representative’s wage

$ 81,200.00

6

Sales representative’s commission

$ 27,114.72

7

Total income

$ 367,536.50

8

Total expenses

$ 287,864.72

9

Total profit

$ 79,671.78

All other calculations required is mentioned in sales tab of excel attached along with this file

II. Total profit, sub-totalled by each

(a)Trade Fair

            Trade fair

Sum of Profit (AUD)

Books

5444.64

Builders

3829.38

Clothing

12329.04

Computing

9510.64

Food

15143.10

Furniture

4343.52

Government

3907.72

HRM

4347.46

Manufacturing

1469.98

Sports

2671.80

Vehicles

1979.84

Wine

14694.66

Grand Total

79671.78

Trade Fair

From the above Pivot table and pie chart it has been observed that clothing, food, wine and computing are the segments of trade fair which constitute major portion of overall profits.

(b)Sales Representative

SALES representative

Sum of Profit (AUD)

Alex

10409.96

Chris

32007.82

Jane

11226.54

Luke

26027.46

Grand Total

79671.78

Sales Representative

From the above Pivot table and pie chart it has been observed that Alex and Luke are the Sales representative which provides maximum contribution profits of BMEC

III. Total hours worked, sub-totalled by each

(a)Trade Fair

Trade Fair

Sum of Time spent by Sales Representative (hours)

Books

54.17

Builders

46.83

Clothing

205.67

Computing

175.83

Food

259.00

Furniture

88.00

Government

30.67

HRM

105.17

Manufacturing

34.67

Sports

54.50

Vehicles

32.17

Wine

266.67

Grand Total

1353.33

Trade Fair

From the above Pivot table and pie chart it has been observed that clothing, food, wine and computing are the segments of trade fair which takes major time spent by sales representative in hours

(b)Sales Representative

Sales Representative

Sum of Time spent by Sales Representative (hours)

Alex

221.17

Chris

522.50

Jane

216.50

Luke

393.17

Grand Total

1353.33

Sales Representative

From the above Pivot table and pie chart it has been observed that Chris and Luke are the Sales representative which take maximum time in hours

Task 2: Observations

I.BEMC’s five best and five worst customers by total profit

BEMC’s five best and five worst customers by total profit

(a)Worst 5 customers of BMEC in terms of profits

Worst 5 customers

Customers

Sum of Profit (AUD)

jobsmgtco

-304.16

plonk2u

-205.9

Hick!

-73.2

buttoneddownco

-65.56

Hoolden

-57.7

Grand Total

-706.52

Worst 5 customers of BMEC in terms of profits

The above table and charts shows the negative profits or losses attained due to worst 5 customers of the group. BMEC should work in the direction of improving or enhancing the performance of particular customers to at least bring them to breakeven level

(b)Top 5 customers of BMEC in terms of profits

Top 5 customers

Customers

Sum of Profit (AUD)

onlyscotchco

1897.66

magazone

1816.68

bottomsup

1720.06

readit

1591.48

kerplonk

1453.02

Grand Total

8478.9

Top 5 customers of BMEC in terms of profits

As per the above charts and pie diagrams it tabulates and present in the pictorial form the top leading profit generating customer of BMEC group. Company should sustain these customer and create further relations to increase the revenue.

II.BEMC’s best and worst Trade Fairs by total profit

Trade fairs

Row Labels

Sum of Profit (AUD)

Manufacturing

1469.98

Vehicles

1979.84

Sports

2671.80

Builders

3829.38

Government

3907.72

Furniture

4343.52

HRM

4347.46

Books

5444.64

Computing

9510.64

Clothing

12329.04

Wine

14694.66

Food

15143.10

Grand Total

79671.78

BEMC’s best and worst Trade Fairs

III.BEMC’s best and worst sales representatives by total income

Sales representative by total income

Row Labels

Sum of sales representative total income

Alex

16743.04

Jane

18072.96

Luke

32169.04

Chris

41329.68

Grand Total

108314.72

BEMC’s best and worst sales representatives by total income

Task 3: Recommendations

I.Should BEMC focus on any particular

(a)Customer

Although BMEC should not neglect any customers, as customers are the king pin of the market and neglecting customer will decrease the brand image of the group. Still special attention is given to the special group of customer which provides sustainable markets, are regular customers of the group. Onlyscotchco is returning customer of the group and lead the chart of top 5 customer of the group in terms of profitability.

(b)Trade Fair

BMEC should put its major strive of work on food and wine segment of the trade fair. In terms of profitability both leads the chart and is main revenue supplier for the group. Further steps are taken to improve the particular segment. Company should categorise the total number of fairs according to ABC analysis. Top revenue generating trade fair segments are placed in A category. Accordingly other trade fairs are categorised in B and C category.

II.What are your recommendation regarding what other data should be collected to improve decision making for BEMC?

Company should add a column for sales representative earnings made by them in quantitative form. Further to improve the decision making apart from past cost and profit data future predicts on the basis of prospective budgets should also be made accordingly. Preparation of budgets and various tools of financial management will help the business in making analysis and proper decision making.