One of your friends, Emma, has found out you are studying accountancy and that you have acquired basic spreadsheet skills. She has decided to start a business of printing bespoke logos on to tennis racquets but needs to produce a business plan for the bank manager in order to raise a loan. Emma has already formed a private limited company and has introduced £5,000 in to the business bank account. Of this £5,000, £1,000 is to be the initial share capital and the balance of £4,000 is a short-term interest free loan from her. It is her intention to start trading as from 1 January 2022.

You have agreed to produce the financial projections for her based on the information provided by Emma.

REQUIRED:

1.Using Excel, construct a concise financial model that includes, as a minimum, the projected cash flow, profit and loss and balance sheet figures for the first year of trading i.e. until 31 December 2022. The statements should show quarterly figures with annual totals, where appropriate.

2.Using either Word or Excel, write a short report to Emma to include the following:

a.Using your model produce financial ratios, which may be useful to Emma bearing in mind the reason for the projections. The report should explain why you have selected these ratios and the relevance of the trends to Emma. Include any other comments you consider would be relevant to Emma.

b.Emma has expressed an interest in using the model to see the financial impact of changing some of the assumptions. Write a concise user guide (report) on how she would use the model to see the impact of changing the gross margin and other assumptions. Do remember Emma has limited Excel knowledge.

c.Explain to Emma the advantages and disadvantages of using Excel to produce the financial projections as compared to producing them manually using “pen and ink”

Information provided by Emma:


1.The first quarter’s sales are expected to be £3,000 doubling every quarter during the year.

2. The gross profit margin on sales is expected to be 40%.

3. Of each quarter’s sales, A% are cash sales.

4. Bad debts are on credit sales, and are anticipated to be B% each quarter.

5. 50% of the credit sales are paid in the quarter in which the sale is made. The remaining debtors, after bad debts, are collected in the following quarter.

6. Sufficient stock is purchased to cover the next quarter’s sales e.g. the stock for the sales in the fourth quarter are purchased in the third quarter. In the first quarter assume the first two quarter’s purchases are made and assume the final quarter’s purchases are £30,000 (the sales for the first quarter of 2023 are unknown).

7. Purchases are paid for in the quarter bought (i.e. there are no trade creditors).

8. Gross staff salaries are £2,100 per quarter and paid in the quarter incurred.

9. Distribution costs are 10% of sales and are paid in the quarter of the sale.

10. Bank interest charged is 2% per quarter calculated on the balance at the end of the previous quarter. It is payable in the following quarter i.e. the quarter in which it is charged. No interest is receivable on credit (positive) balances.

11.The company buys machinery costing £10,000 in the first quarter.

12.A van costing £8,000 is purchased in the third quarter.

13.The company depreciation policy is to depreciate assets at C% per annum on a straight-line basis.

14.The company incurs a quarterly telephone bill of £525, which is usually paid in the quarter it is incurred. However, the fourth quarter’s bill is £600 and will not be paid until January 2023.

15.Ignore the effect of corporation tax, VAT, PAYE and inflation.

16.Include any other expenses Emma has obviously omitted from the above information.