To analyse a set of data, and write a brief report, identifying and explaining your insights into the operation of "The FedFone Co.".
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.
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.
i. Which was the best day of the week (e.g. Monday-Sunday) for FFC over the three month period for profit? Use an appropriate table and graph/chart to summarise these calculations.
j. Chart/graph changes to daily profitability over the three months. Use an appropriate graph/chart to summarise these changes.
k. Should FFC focus on any particular sales activities in future, and why?
l. What are your recommendation regarding what other data should be collected to improve decision making for FFC?
m. 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.
n. What changes should FFC make regarding any (a) currently employed Sales Representative(s), or (b) Shops, and why?