Total Income

Questions:

Tasks

a. Total income for the three month period, sub-totalled by each (a) Shop, (b) Sales Representative, and (c) Phone. Use Pivot Table(s) to summarise these calculations.

b. Total expenses for the three month period, sub-totalled by each (a) Shop, and (b) Sales Representative. Use Pivot Table(s) and appropriate graph(s)/chart(s) to summarise these calculations.

c. Total profit for the three month period, sub-totalled by each (a) Shop, (b) Sales Representative, and (c) Phone. Use Pivot Table(s) to summarise these calculations?

d. Total phone repair expenses and income for the three month period, sub-totalled by each (a) Phone, (b) Sales Representative and (c) Shop. Use appropriate Table(s) to summarise these calculations?

e. Which phone plans provided the (a) most and (b) least income, for the three month period? Use an appropriate table and graph/chart to summarise these observations?

f. Which are the worst phones sold by FFC, by (a) total number of repairs required and (b) time taken for repair, for the three month period? Use an appropriate table and graph/chart to summarise these observations?

g. What are FFC’s best and worst Sales Representatives by total sales? Use an appropriate table and graph/chart to summarise these observations?

h. What would happen to FFC profits if the training service was charged at an hourly rate of $25 per hour instead of the current flat fee? Create a spreadsheet tool that allows Ms Martyn to test profit outcomes for any training fee – create a new worksheet on the assignment workbook for this tool?

Ms. Georgie Martyn, was stated a mobile sale and service shop, which is situated near Ballart area.  Ms. Georgie Martyn’s mobile sale and service company name is Fed Fone Co. (FFC). Ms. Georgie Martyn or Fed Fone Co. (FFC) is situated in three different ranges in Ballart area like one shop is located in High Street; second shop is located in near Mall zone and the last or third shop is located in Suburd zone. The Fed Fone Co. (FFC) or Ms. Georgie Martyn’s shops are open regularly in week or 7 days. Ms. Georgie Martyn is measuring and monitoring each and every sells or service data based using its own dataset or spared sheet. By the using of the Ms. Georgie Martyn or Fed Fone Co. (FFC)’s spared sheet or dataset we can easily measured or calculated the total income, expenses, profit of different shops of Fed Fone Co. (FFC) by the help of dataset analysis. The dataset or spared sheet is analysis by the help of the Microsoft excel programmed (Griffin & Ebert, 2006). By the help of the dataset outcomes or spared sheet outcomes Ms. Georgie Martyn want to analysis the customer’s behavior. Ms. Georgie Martyn is also wanted to improve the sale efficiency as well as services of Fed Fone Co. (FFC). Ms. Georgie Martyn is wanted to consist or build a good customers relationship by the help of its company services. For that reason we analysis the Ms. Georgie Martyn or Fed Fone Co. (FFC) recoded dataset or spared sheet and the outcomes are shown in below in form of total profit, incomes and expenses of  the Ms. Georgie Martyn’ s shop or Fed Fone Co. (FFC).

Total Expenses

a. By the help of the provided dataset or spared sheet of Ms. Georgie Martyn or Fed Fone Co. (FFC) the total income of different branches like High Street zone shop is $211880, shop located in Mall is $175469 and Suburd zone shop is $63686.

Therefore, according to the analysis the Ms. Georgie Martyn or Fed Fone Co. (FFC)’s total income within the three months (1^st January to 30^th March) is High Street zone shop income + Mall located shop income + Suburd zone shop income.

= $(211880+ 175469 + 63686)

= $451035

b. The total expences of the Ms. Georgie Martyn or Fed Fone Co. (FFC) is calculating or measuring by the help of the provided data set or spared sheet. The giving dataset or spared sheet of the business model is analysis by the help of the advance excel. By the help of the provided dataset or spared sheet of Ms. Georgie Martyn or Fed Fone Co. (FFC) the total expenses of different branches like High Street zone shop is $158553, shop located in Mall is $114038 and Suburd zone shop is $38982.

Therefore, according to the analysis the Ms. Georgie Martyn or Fed Fone Co. (FFC)’s total expenses within the three months (1^st January to 30^th March) is High Street zone shop expenses + Mall located shop expenses + Suburd zone shop expenses.

= $(158553+ 114038 + 38982)

= $311573

c. The total profit of the Ms. Georgie Martyn or Fed Fone Co. (FFC) is calculating or measuring by the help of the provided data set or spared sheet. The giving dataset or spared sheet of the business model is analysis by the help of the advance excel (Gunasekaran & Sandhu, 2010). By the help of the provided dataset or spared sheet of Ms. Georgie Martyn or Fed Fone Co. (FFC) the total profit of different branches like High Street zone shop is $53327, shop located in Mall is $61431 and Suburd zone shop is $24704.

Therefore, according to the analysis the Ms. Georgie Martyn or Fed Fone Co. (FFC)’s total profit within the three months (1^st January to 30^th March) is High Street zone shop profit + Mall located shop profit + Suburd zone shop profit.

= $(53327+ 61431 + 24704)

= $139462

The income via repair of the Ms. Georgie Martyn or Fed Fone Co. (FFC) are measured and analysis by the help of the excel analysis. By the help of the provided dataset or spared sheet of Ms. Georgie Martyn or Fed Fone Co. (FFC) the total income via repairing the phone or mobile is $710 as well as total expenses of the three shops of Ms. Georgie Martyn or Fed Fone Co. (FFC) is $1036.

d. Ms. Georgie Martyn or Fed Fone Co. (FFC) has various types of mobile plans and services for customer’s requirements. According to the provided dataset or spared sheet of Ms. Georgie Martyn or Fed Fone Co. (FFC) is shown in below by the help of table, which is analyzing by the help of advance excel.

Plan	No
Telstrar plan 1	249
Telstrar plan 2	140
Optrus plan 1	442
Optrus plan 2	327
Raldi plan	225
Vodrfon plan 1	17
Vodrfon plan 2	8

Total Profit

 
According to the above discussion, the graphical representation of the phone plans of Fed Fone Co. (FFC) is shown in below.

The total income ($452071) via repairing is calculating by the help of the spared sheet, which is shown in below.

Plan	Total Income
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

According to the above graphical representation and excel analysis, Optrus plan 1 is the most provided phone plan and Vodrfon plan 2 is least provided phone plan by Fed Fone Co. (FFC).

e. According to the provided dataset or spared sheet, Aple v17 phone is worst phone in Fed Fone Co. (FFC).

f. The Sale of the Fed Fone Co. (FFC) is directly depends on the service and quality of the phone as well as customers. The shop representatives are the most important part because the shop representatives are communicated with customers and sell their products as per their requirements and solving each and every query. According to the giving spared sheet, the sale no is measured by the help of shop representative, which is shown in below.

Name of representative	Sale no
George	281
John	212
Paul	408
Ringo	221
Stuart	276

According to the above table, the graphical representation of sale no and shop representative is shown in below.

According to the above graphical representation and excel analysis, Paul is best shop representative and Ringo is worst shop representative in Fed Fone Co. (FFC) for last three months (1^st January to 30^th March).

g. The sum of new profit in brown and total profit in blue is shown in below by the help of graphical representation.

h. The total sum of incomes of Fed Fone Co. (FFC) is shown in below.

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

Ms. Georgie Martyn’s Company or Fed Fone Co. (FFC) is a good organization, where the whole business model is analysis by the help of the critical management strategy. By the help of the critical management strategy Ms. Georgie Martyn is easily analysis the whole business model and improving the weak part (Messner, 2007). The best business strategy of Fed Fone Co. (FFC) is consisting by the help of customers dealing and services. If the shop member are in contact with each and every customers or clients of the company than customers feedback are easily get. If Ms. Georgie Martyn’s Company or Fed Fone Co. (FFC) is provided some free phone services for every purchasing of new phone and also collecting feedback after some few months, than all the customers are feel good about the services and always come to the shop.

References

Foulds, L., & West, M. (2007). The productivity of large business information system development.IJBIS, 2(2), 162. doi:10.1504/ijbis.2007.011617

Griffin, R., & Ebert, R. (2006). Business. Upper Saddle River, N.J.: Pearson/Prentice Hall.

Gunasekaran, A., & Sandhu, M. (2010). Handbook on business information systems. Singapore: World Scientific.

Law, W., & Perez, K. (2005). Cross-cultural implementation of information system. Hershey, PA: Idea Group Pub.

Messner, W. (2007). Justifying information system value: Development of a method for measuring customer advisory system effectiveness. Business Information Review, 24(2), 126-134. doi:10.1177/0266382107078865

Pedley, P. (2000). Implementing and project managing a new library management system. Business Information Review, 17(1), 43-49. doi:10.1177/0266382004237458

Sarngadharan, M., & Minimol, M. (2010). Management information system. Mumbai [India]: Himalaya Pub. House.

Yeh, C., Lee, G., & Pai, J. (2012). How information system capability affects eâ€Âbusiness information technology strategy implementation. Business Process Mgmt Journal, 18(2), 197-218. doi:10.1108/14637151211225171