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 “The FedFone Co.”.
Learning Objectives:
In the process of this assessment task you will:
• plan, schedule and execute project tasks with a view to improving 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.
Introduction:
The FedFone Co. (FFC) sells mobile phones and accessories at three shops in the Ballarat area (i.e. High Street, Mall, Suburb) seven (7) days a week. The company also organises repairs for phones they sold but which have been damaged or become faulty. These activities are co-ordinated by five (5) FFC Sales Representatives (John, Paul, Ringo, George, Stuart). The company’s founder, Ms Georgie Martyn, like all small business owners, is always interested in finding ways to increase revenues and decrease expenses.
Ms Martyn has hired you as a business analyst and poses some operational-level questions about the performance of her business. She is also eager to hear your thoughts and ideas on how to improve the business and requires you to make several 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.
Ms Martyn has provided you with last year’s sales information in the “itech1005-5005 2014-27 assignment data.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 consultancy’s operations in the first three months of the year. These details included:
• Date (of sale)
• Shop (where sale completed)
• Staff (i.e. Sales Representative name)
• Phone Model
• Phone Plan
• Phone Cover (accessory sold)
• Spare Battery (accessory sold)
• Charger Pack (accessory sold)
• Repair Details (type of problem with phone returned for repair)
• Repair Date (date received from customer for repair)
• Return Date (date returned to customer after repair)
• Software Update (service fee income)
• Training Time (hours spent training customers in phone use – service fee income)
• Insurance (service fee income)
FFC must buy all the stock (i.e. mobile phones and accessories) they then sell to customers.
Phone/Accessory: Cost Price ($A) Sales Price ($A)
HTS model 1 98 150
HTS model 2 185 200
HTS model 3 199 350
HTS universal phone cover 15 27
HTS spare battery 67 100
Sansumg 300 phone 152 300
Sansumg 250 phone 187 210
Sansumg 300 cover 16 25
Sansumg 250 cover 13 25
Sansumg spare battery 90 100
Apel v17 phone 655 690
Apel cover 22 45
Apel charger pack 45 80
Motorolar razzr phone 199 220
Motorolar newstyle phone 245 270
Motorolar phone cover 16 25
Nokkia phone 1111 99 140
Nokkia phone 2222 129 165
Nokkia phone cover 14 25
Nokkia spare battery 80 110
Universal charger pack 27 40
FFC sell a range of mobile phone plans to customers that come from a number of telecommunications companies. FFC are paid a commission by each telecommunications company for selling the different plans as the table underneath indicates:
Phone Plans: Commission Income Received ($A)
Telstrar plan 1 10
Telstrar plan 2 8
Optrus plan 1 8
Optrus plan 2 6
Raldi plan 3
Vodrfon plan 1 5
Vodrfon plan 2 8
When phones are returned for repair, FFC must also pay $14 postage costs to mail the phone to the authorised service centre – this cost is wholly or partially reimbursed by the phone manufacturer according to a scale of fees:
Phone Company: Repair Postage Reimbursement Amount Received ($A)
HTS 18
Sansumg 8
Apel 5
Motorolar 10
Nokkia 15
FFC also provides additional services for which it charges customers fees for service. The additional ‘flat’ (i.e. one-time) fees charged by FFC to customers for their additional services (i.e. as fee income) are:
Additional Service: Service Fee Income Received ($A)
Phone set-up or software update 5
Phone use training 40
Phone insurance brokerage fee 5
All Sales Representatives are paid fixed wages and no additional commissions are paid for sales or services. Wages are a fixed cost to the business and as such are not required in the analysis/reporting of the sales and expenditure for Ms Martyn.
Assessable Tasks:
Ms Martyn needs to have a summary report of operations that will include the following information:
Calculations (All Students):
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.
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.
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.
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.
Ms Martyn requires you to make observations to help her with the following questions.
Observations (All students):
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.
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.
What are FFC’s best and worst Sales Representatives by total sales? Use an appropriate table and graph/chart to summarise these observations.
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.
Recommendations (All Students):
- Should FFC focus on any particular sales activities in future, and why?
- What are your recommendation regarding what other data should be collected to improve decision making for FFC?
- What are your recommendation regarding record-keeping to improve the quality of data collection and management? Apply appropriate data validation rules to columns A, B & M on the spreadsheet to show Ms Martyn how to apply this technique of improving data quality.
Ms Martyn has asked you to provide her with the details of how you have accomplished these tasks. Therefore, you must include the functions and the formulae you’ve used in your calculations and analyses, and not simply report the answers. Create a new worksheet in the assignment workbook for each question, to provide Ms Martyn with these calculations and summations.