This report is based on the sales tracking and customer relationship analysis of the company, Ballarat Trade Fair Consultancy (BTFC). The main activity of this company is to promote and sell stand space at trade fairs and tickets for visitors to trade fairs in Ballarat area. The company analyses the total profit, total sales and cost incurred in them in different financial years and how this has affected the performances of the sales representatives. The report also analyses the best and the worst customers as well as the best and worst sales representatives. The best and the worst selling experiences of the sales representatives are also analyzed in this report. The best and the worst trade fairs are examined here. The solution for the issues of data redundancy is seen in this report and also the other necessary data that can be collected by BTFC for an improved decision making. The basis of commission for the sales representatives and how it can be improved is seen in this report.
Calculations (All Students):
a. Total sales, sub-totalled by each (a) Trade Fair, (b) Customer, and (c) income type.
(a)Trade fare- 65613
(c) Income type: 388750
b. Total profit, sub-totalled by each (a) Trade Fair, and (b) Customer is 521532 c. Total costs, sub-totalled by each (a) Trade Fair, (b) Customer, and (c) cost type is 5220 ($10 given by the company to sales representatives on time spent by the sales representatives) d. Total hours worked, sub-totalled by each (a) Trade Fair, and (b) Customer is 522 hours. e. Percentage of current (a) sales, and (b) profits attributed to each Trade Fair : On sales is 18.55%
On Profit is 2.02%
f. Costs as a percentage of (a) sales, and (b) profits, sub-totalled for each Sales Representative is 10%
Observations (All students)
h. Who are BTFC’s best and worst customers by total profit?
On the basis of the total sales, the customers who contributed more to the trade fair and its profit is in the year of 2011. The total profit of that year comes to 89249. This figure is the highest when compared to all other years. The wine exhibitors earned more profits in this years, which gives BTFC more profit in terms of the space sold to them by BTFC and the maximum number of visitor tickets sold. The worst customers are from manufacturing sector in the year 2005, as they contributed to a loss of -140. The maximum visitor tickets sold were 12 and the space sold was 75. i. What are BTFC’s best and worst value-added services by total sales?
Exhibitors stand organized are the best value added services in terms of the total sales. Exhibitors hospitality organized is the worst value added services with regard to the total sales.
j. Who are BTFC’s best and worst Sales Representatives by total profit?
With respect to the total profit, the best sales representatives are Alice and Sue, who sold a total number of tickets to the exhibiters amounting to 250 each. The exhibitors for whom they sold the highest number of visitor tickets are government exhibitors and the exhibiting companies are DPR and Centrelunk. However, Sue exhibited a worst performance as a sales representative in selling visitors tickets for clothing customers. The companies for which Sue made less sales are DenimToo, BabiesRUs and Scarves4all.
k. What would happen to profits if the commission rate of pay to Sales Representatives was increased by 50%? If the commission rate of pay to sales representatives was increased, there will be a reduction in the profit margin earned by BTFC. The major differences in the profit margin will be related to the sales representatives who are dealing with the new exhibitors (large), as a 10% of the sales amount will be given as commission for the sales representatives. The least affected will be on the profits from returning exhibitors as the sales representative’s commission on this will be 2.5 percent of the sales amount.
Observations (ITECH5005 Students Only):
l. What are the worst Sales Representative’s best- and worst-selling services (by total profit)?
The worst sales representative with regard to visitors ticket sold was Sue. The best selling experience of Sue was for government exhibitors and the company for which Sue sold the most visitor tickets (250 numbers) are for Centrluck. The worst selling services of Sue was for clothing exhibitors for the companies DenimToo, BabiesRUs and Scarves4all, who whom Sue sold only 2 visitors tickets for each. Sue’s best selling services related to the total space sold (sq.m) was for government exhibitor, Centreluck. Worst selling services related to space sold was for clothing and food exhibitors. m. Who are the best Sales Representative’s best and worst customers (by total profit)?
The best customers of Alice and Sue are DPR and Centruluck. The worst customers of Sue are . The worst customers of Sue are scarves4all and DenimToo. The worst customers of Alice are dessups from clothing. n. Who are the best Sales Representative’s best and worst Trade Fairs (by total profit)?
Alice and Sue are the best sales representatives on an overall basis, considering the sales of both visitors tickets and space sold. The maximum number of combined sales that Alice and Sue made was 325 each. The worst customers of Sue are from clothing and food exhibitors. The worst customers of Alice are Wine exhibitors. p. Should BTFC focus on any particular Trade Fair(s) or sales activities now and/or in future, and why?
BTFC can focus on Builders and the sales activities related to them. This is mainly because the profit derived from Builders are comparatively less low in almost all the years as a result of the low sales performances exhibited by them. q. What are your recommendations for resolving any data redundancy issues observed in the workbook?
The data redundancy in the workbook can be avoided using the cell validations. Data validation will help in avoiding any duplicate entry of data in excels.
r. What are your recommendation regarding what other data should be collected to improve decision making for BTFC?
For improvements in the decision making, BTFC can also consider the gross and net revenue generated by the company, other related incomes and gains, losses and expenses. The performance of the competitors can also be taken in to consideration for a better decision making. s. What are your recommendation regarding record-keeping to improve the quality of data collection and management? In order to improve the quality of the data collection, one of the most effective electronic record keeping is the accounting software programs. This helps in the easy recording of incomes, expenses, payments, assets and stock details etc. It also helps in the easy generation of invoices etc.
t. What changes should BTFC make regarding any currently employed Sales Representative(s), or to the fees paid to the Sales Representatives, and why? Recognizing the efforts of the sales representatives is a vital factor in improving the morale and performance of them. When the sales representatives perform the tasks completely, the management has to ensure they are appreciated at least orally. This increases their confidence in job performance. If there is no appreciation given to employees, then they may lower their performance level resulting in loss of interest and low morale. The management has to know whether the sales representatives have sufficient skill set to perform and complete the sales.
If they lack any particular skill, proper training on those key performance areas are to be given to them. This will help the employees to know how to sell effectively and it can be carried out in a better way. Thus, any deficits in the skills are to be eliminated for the overall smooth functioning of BTFC. Otherwise, the employees will feel de-motivated towards their work. This will lead to low sales performance. It is better to create the commission system based on the level of sales performance of the sales representatives. If the person is making highest overall sales, he has to be given the highest commission. The person making lowest overall sales has to be given with the lowest commission.