Business Information Systems: FedFone Co. Or FFC

Question:

Describe about the Business Information System?

Answer:

Executive Summary

The FedFone Co. or FFC is a growing business in Ballarat area. The company has simple business process. It has 3 shops at different places in Ballarat area and 5 sales executives. They sales mobile phones and accessories to those shops.

As the business is a growing business, so it needs to understand the opportunities of improvement of their business process, build stronger customer relationship. For that purpose, it has hired some consultancy for analysis of their previous business records. The analysis will help them to understand better sales possibilities in future and help them to take better business strategies.

Calculations

The summary of total income as subtotaled by the shops, sales executive and phone models is given in the following pivot table,

Row Labels	Sum of Total Income ($A)
High Street Save Time On Research and Writing Hire a Pro to Write You a 100% Plagiarism-Free Paper. Get My Paper
Paul
Apel v17 phone	73213
HTS model 1	17560
HTS model 2	6929
HTS model 3	18576
Motorolar newstyle phone	6831
Motorolar razzr phone	6150
Nokkia phone 1111	2369
Nokkia phone 2222	3215
Sansumg 250 phone	5476
Sansumg 300 phone	15516
Paul Total	155835
Ringo
Apel v17 phone	8298
HTS model 1	12240
HTS model 2	10353
HTS model 3	21533
Sansumg 250 phone	3621
Ringo Total	56045
High Street Total	211880
Mall
George
Apel v17 phone	17450
HTS model 1	3634
HTS model 2	4576
HTS model 3	16468
Motorolar newstyle phone	8618
Motorolar razzr phone	7524
Nokkia phone 1111	2812
Nokkia phone 2222	5190
Sansumg 250 phone	5232
Sansumg 300 phone	8624
George Total	80128
Stuart
Apel v17 phone	8095
Sansumg 250 phone	8275
Sansumg 300 phone	78971
Stuart Total	95341
Mall Total	175469
Suburb
John
HTS model 1	12359
HTS model 2	11393
HTS model 3	19255
Motorolar newstyle phone	3883
Motorolar razzr phone	1570
Nokkia phone 1111	3268
Nokkia phone 2222	2291
Sansumg 250 phone	3528
Sansumg 300 phone	6139
John Total	63686
Suburb Total	63686
Grand Total	451035

1. The summary of total expense as subtotaled by the shops and sales executive is given in the following pivot table,

Row Labels	Sum of Total Expense ($A)
High Street
Paul	118100
Ringo	40453
High Street Total	158553
Mall
George	60510
Stuart	53528
Mall Total	114038
Suburb
John	38982
Suburb Total	38982
Grand Total	311573

1. The summary of total profit as subtotaled by the shops, sales executives and phone models is given in the following pivot table,

Row Labels	Sum of Total Profit ($A)
High Street
Paul
Apel v17 phone	6428
HTS model 1	7545
HTS model 2	1497
HTS model 3	8974
Motorolar newstyle phone	1524
Motorolar razzr phone	777
Nokkia phone 1111	860
Nokkia phone 2222	964
Sansumg 250 phone	1092
Sansumg 300 phone	8074
Paul Total	37735
Ringo
Apel v17 phone	438
HTS model 1	4400
HTS model 2	918
HTS model 3	9394
Sansumg 250 phone	442
Ringo Total	15592
High Street Total	53327
Mall
George
Apel v17 phone	1075
HTS model 1	1380
HTS model 2	506
HTS model 3	7314
Motorolar newstyle phone	1023
Motorolar razzr phone	957
Nokkia phone 1111	931
Nokkia phone 2222	1320
Sansumg 250 phone	744
Sansumg 300 phone	4368
George Total	19618
Stuart
Apel v17 phone	1230
Sansumg 250 phone	1285
Sansumg 300 phone	39298
Stuart Total	41813
Mall Total	61431
Suburb
John
HTS model 1	5872
HTS model 2	2931
HTS model 3	8989
Motorolar newstyle phone	617
Motorolar razzr phone	322
Nokkia phone 1111	1268
Nokkia phone 2222	685
Sansumg 250 phone	816
Sansumg 300 phone	3204
John Total	24704
Suburb Total	24704
Grand Total	139462

1. The summary of repair income and expense as subtotaled by phone models, sales executives and shops is given below.

Row Labels	Sum of Expense for Phone Repair ($A) [Partially reimbursed Postal charges]	Sum of Income from Phone Repair ($A)
Apel v17 phone
George
Mall	0	0
George Total	0	0
Paul
High Street	238	85
Paul Total	238	85
Ringo
High Street	28	10
Ringo Total	28	10
Stuart
Mall	70	25
Stuart Total	70	25
Apel v17 phone Total	336	120
HTS model 1
George
Mall	0	0
George Total	0	0
John
Suburb	140	180
John Total	140	180
Paul
High Street	28	36
Paul Total	28	36
Ringo
High Street	0	0
Ringo Total	0	0
HTS model 1 Total	168	216
HTS model 2
George
Mall	0	0
George Total	0	0
John
Suburb	0	0
John Total	0	0
Paul
High Street	0	0
Paul Total	0	0
Ringo
High Street	0	0
Ringo Total	0	0
HTS model 2 Total	0	0
HTS model 3
George
Mall	0	0
George Total	0	0
John
Suburb	0	0
John Total	0	0
Paul
High Street	0	0
Paul Total	0	0
Ringo
High Street	0	0
Ringo Total	0	0
HTS model 3 Total	0	0
Motorolar newstyle phone
George
Mall	0	0
George Total	0	0
John
Suburb	0	0
John Total	0	0
Paul
High Street	0	0
Paul Total	0	0
Motorolar newstyle phone Total	0	0
Motorolar razzr phone
George
Mall	0	0
George Total	0	0
John
Suburb	0	0
John Total	0	0
Paul
High Street	0	0
Paul Total	0	0
Motorolar razzr phone Total	0	0
Nokkia phone 1111
George
Mall	0	0
George Total	0	0
John
Suburb	140	150
John Total	140	150
Paul
High Street	0	0
Paul Total	0	0
Nokkia phone 1111 Total	140	150
Nokkia phone 2222
George
Mall	0	0
George Total	0	0
John
Suburb	0	0
John Total	0	0
Paul
High Street	0	0
Paul Total	0	0
Nokkia phone 2222 Total	0	0
Sansumg 250 phone
George
Mall	0	0
George Total	0	0
John
Suburb	84	48
John Total	84	48
Paul
High Street	70	40
Paul Total	70	40
Ringo
High Street	0	0
Ringo Total	0	0
Stuart
Mall	112	64
Stuart Total	112	64
Sansumg 250 phone Total	266	152
Sansumg 300 phone
George
Mall	0	0
George Total	0	0
John
Suburb	56	32
John Total	56	32
Paul
High Street	70	40
Paul Total	70	40
Stuart
Mall	0	0
Stuart Total	0	0
Sansumg 300 phone Total	126	72
Grand Total	1036	710

Observations

The table and chart for the income from different phone plans sold by FFC in 3 shops are,

PhonePlan	Sum of Total Income ($A)
Optrus plan 1	135857
Optrus plan 2	109734
Raldi plan	57108
Telstrar plan 1	92444
Telstrar plan 2	9235
Teltsrar plan 2	40936
Vodrfon plan 1	4550
Vodrfon plan 2	2207
Grand Total	452071

And Thus, the (a) most income providing plan is Optrus plan 1 and the (b) least income providing plan is, Vodrfon plan 2.

1. The table and chart for the repair details of the phone models sold by FFC are,

PhoneModel	Count of RepairDetails	Sum of Duration of Phone Repair (Days)
Apel v17 phone	24	720
HTS model 1	12	73
HTS model 2		0
HTS model 3		0
Motorolar newstyle phone		0
Motorolar razzr phone		0
Nokkia phone 1111	10	132
Nokkia phone 2222		0
Sansumg 250 phone	19	222
Sansumg 300 phone	9	86
Grand Total	74	1233

And, For (a) and (b) the worst phone having the most total number of repairs and the longest duration for the repairs is, Apel v17 phone.

1. The table and chart for the income summary of the sales representatives are,

StaffName	Sum of Total Income ($A)
George	80128
John	64106
Paul	156241
Ringo	56073
Stuart	95523
Grand Total	452071

And,

Thus, by total sales (income) the best sales representative is Paul and the worst sales representative is Ringo.

The chart for comparing the total profit in both scenarios is,

Thus, the profit is more in case of hour based training service charge than the flat charge.

Recommendations

Following table shows a summary of income from different sales activities of FFC in different shops.

	Shop
Values	High Street	Mall	Suburb	Grand Total
Sum of Income from Phone Model ($A)	194140	161610	47525	403275
Sum of Income from Phone Plan ($A)	3475	3892	1584	8951
Sum of Income from Phone Cover ($A)	2838	1810	2997	7645
Sum of Income from Spare Battery ($A)	3650	4500	3590	11740
Sum of Income from Charge Pack ($A)	2360	2080	4320	8760
Sum of Income from Software Update ($A)	315	90	215	620
Sum of Income from Training Service ($A)	4960	1560	3000	9520

Based on this summary, the recommendation for improvement of their sales activities are,

• FFC should focus on different shops. Because all shops are not performing well. Especially is suburb are the income from sales is very low. Same is true for the sales representative. There is a huge gap between the best and worst performing sales representatives.

• FFC can concentrate on selling more phone accessories and insurance. When people are buying new phones, it is likely that they will by accessories like phone cases, insurance etc.

Based on the analysis results so far, the recommendations for better decision making are,

• FFC should provide more detailed sales information for better and in depth analysis.

• Data should be cleaned before using in analysis work. There are several issues with the sales information provided by FFC. Such uncleansed data tends to give erroneous results.

• Other than sales information, FFC should focus on customer demographics also.

Based on the sales information provided by FFC, the recommendation for better and quality record keeping are,

• Use of separate sheets for different entities like product, sales activities, sales representatives etc.

• Limiting the scope of manual data entry and use of more referencing and calculation using formulas etc.

• Use of data validation and for example, there is no data validation in any column. Column A, B and M contains Dates, name of the shops and duration of training hours respectively. There is no data validation used in any of these columns. Thus in column A, instead of date any string can be written. That will inject error in the analysis. In the same way, anything can be written on other two columns. But with proper data validation, column A should be restricted to have date values in proper format, column B should have a list of values with the names of three shops. And column M should be restricted to have any whole number from the range 0 to 24.

References:

Gross, D., Akaiwa, F., & Nordquist, K. (2013). Succeeding in Business with Microsoft Excel 2013: A Problem-Solving Approach. Cengage Learning.

Jelen, B. (2010). Microsoft Excel 2010 In Depth. Pearson .

Jelen, B., & Alexander, M. (2013). Excel 2013 Pivot Table Data Crunching. Que Publishing.

Ralston, B. (2011). PowerPivot for Business Intelligence Using Excel and SharePoint. Apress.

Rockoff, L. (2013). Microsoft Excel 2013 for the Business Analyst. Cengage Learning.

Winston, W. L. (2009). Microsoft® Excel Data Analysis and Business Modeling. Microsoft Press.

Turn in your highest-quality paper
Get a qualified writer to help you with

“ Business Information Systems: FedFone Co. Or FFC ”

Get high-quality paper

NEW! AI matching with writer