You are required to develop a financial information system based on a fictional company of your choice. The company will be a small business run as a sole trading enterprise. The company sells three products to its customers using five self-employed sales people, each representing a specific area of the United Kingdom.

The company has limited computing resources and you are required to develop the system using Excel 2010. The company’s most urgent need is to have information concerning the profitability of the products they sell and the performance of their salespeople.

In addition to the 5 sales people the company employs 2 administrators (salary £18,000 per annum) who process orders, deal with customer enquiries, support the MD and so forth, a finance clerk (salary £21,000 per annum) and a Managing Director (salary £75,000 per annum) who spends 20% of his time on this particular business. The other costs of running the business will need to be determined but you can assume that the business is run on buying in the products that it sells externally, and it  has negotiated a “sale or return” agreement with its wholesalers so this cost is directly related to the product selling price.

The company would like to use the system for forecasting and analysis purposes and therefore expects it to be constructed in such a way that data can be varied and results calculated automatically.

The company’s price list is based on a mark-up that is adequate to cover all of its costs in a reasonable trading year; you must determine the mark-up and explain why you have chosen it. The sales people receive a small basic monthly salary of £500 plus sales bonus of 20% (on the profit of each completed sale) paid one month in arrears.

Deliverables:

You are required to:

1. a) Write a brief description of the company and the business in which it is engaged. You may choose any business idea that you wish but you should discuss it with your tutor first. (Max 250 words).

1. b) Provide data for six months of trading. What assumptions will you be making when doing this? Your spreadsheet must include the expected costs of the business as well as the revenues. Ensure that you have a reasonable process in place for assessing the profitability of each of the 5 sales people.
2. c) Explain your rationale for the mark-up (selling price of product minus costs of processing the product) that you have determined.
3. Management wish to look at alternative payment structures – reproduce your spreadsheets demonstrating the impact on the company’s finances if:
   1. Salary is £1000 and sales bonus is 10%
   2. Salary is £0 and sales bonus is 40%
   3. Salary is £250, sales bonus is 20% plus an additional 20% bonus for the most successful salesman

Write a conclusion as to the salary/bonus structure that you believe is most appropriate to the company based on the above.

Place your Order Now!