Ms Holt needs to have a summary report of operations that will include the following information:
a. total sales, sub-totalled by each (a) Trade Fair, (b) Sales Representative, and (c) income type. Use Pivot Table(s) and appropriate graph(s)/chart(s) to summarise these calculations.
b. total profit, sub-totalled by each (a) Trade Fair, and (b) Sales Representative. Use Pivot Table(s) and appropriate graph(s)/chart(s) to summarise these calculations.
c. total expenses, sub-totalled by each (a) Trade Fair, (b) Sales Representative, and (c) expense type. Use Pivot Table(s) and appropriate graph(s)/chart(s) to summarise these calculations.
d. total hours worked, sub-totalled by each (a) Trade Fair, and (b) Sales Representative. Use Pivot Table(s) and appropriate graph(s)/chart(s) to summarise these calculations.
e. changes to profitability over the past decade, including 2014 figures, sub-totalled by each Trade Fair. Use an appropriate graph/chart to summarise these changes.
Ms Holt requires you to make observations to help her with the following questions.
f. Who are BTFC's five best and five worst customers by total sales? Use an appropriate table and graph/chart to summarise these observations.
g. What are BTFC's best and worst value-added services by total profit? Use an appropriate table and graph/chart to summarise these observations.
h. What are BTFC's best and worst Trade Fairs by total profit? Use an appropriate table and graph/chart to summarise these observations.
i. What would happen to profits if the rate of pay to Sales Representatives for hours worked on sales was increased by 40%? Use an appropriate table and graph/chart to summarise these calculations.
j. What are the best Sales Representative's best- and worst-selling services (by total profit)? Use an appropriate table and graph/chart to summarise these calculations.
k. What would happen to profits if the amount of space required to be sold to change the scale of commission paid was changed from 22 sq.m. to (a) 25 sq.m. and (b) 20 sq.m. Use an appropriate table and graph/chart to summarise these calculations.
Ms Holt also requires you to make recommendations concerning the following business-level questions.
l. Should BTFC focus on any particular (a) customer(s), (b) Trade Fairs, and (c) particular sales activities in future, and why?
m. What are your recommendations for resolving any data redundancy issues observed in the workbook?
n. What are your recommendation regarding what other data should be collected to improve decision making for BTFC?
o. What are your recommendation regarding record-keeping to improve the quality of data collection and management?
p. What changes should BTFC make regarding any currently employed Sales Representative(s), or to the fees paid to the Sales Representatives, and why?