Get Instant Help From 5000+ Experts For

Writing: Get your essay and assignment written from scratch by PhD expert

Rewriting: Paraphrase or rewrite your friend's essay with similar meaning at reduced cost

Question:

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

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

Cite This Work

"Business Information System Analysis - Opportunities For Improvement." My Assignment Help, 2016, https://myassignmenthelp.com/free-samples/business-information-systems-fedfone-co-or-ffc.

My Assignment Help (2016) Business Information System Analysis - Opportunities For Improvement [Online]. Available from: https://myassignmenthelp.com/free-samples/business-information-systems-fedfone-co-or-ffc
[Accessed 04 August 2024].

My Assignment Help. 'Business Information System Analysis - Opportunities For Improvement' (My Assignment Help, 2016) <https://myassignmenthelp.com/free-samples/business-information-systems-fedfone-co-or-ffc> accessed 04 August 2024.

My Assignment Help. Business Information System Analysis - Opportunities For Improvement [Internet]. My Assignment Help. 2016 [cited 04 August 2024]. Available from: https://myassignmenthelp.com/free-samples/business-information-systems-fedfone-co-or-ffc.

Get instant help from 5000+ experts for

Writing: Get your essay and assignment written from scratch by PhD expert

Rewriting: Paraphrase or rewrite your friend's essay with similar meaning at reduced cost