How would I put the below details into an excel spreadsheet?

Question

Year 1 - £350,000 target with a 30% margin, 100 clients

Year 2 - £700,000 target with a 27% margin, 250 clients 

Year 3 - £1,000,000 target with a 25% margin, 300 clients

I am selling to private clients, brokers, retail and on-trade too and there needs to be consistent income month on month, with gradual build.

Ideally there needs to be a monthly split and repeat sales performance from each customer

 

Work off average private client price of £30 ex vat, average broker price £35 ex vat , average on-trade £13 ex vat, average retail of £17 ex vat.

Thanks.

Answer 1

You would want to build out the monthly cashflows for each year. Put the years in the column headings, so 36 columns of data, 1 column of row headings. Add the expected cashflow without the margin for that month. Then build up from there as you go down the sheet, by adding the margin for each month and for each customer.

After that you would put in the costs, the splits, the vat etc. on a row by row basis for each month.

Total toward the end and you are done.

It's a bit much to describe in this format, but you could either schedule time with a tutor or look up some financial modeling courses on the interwebs. There are plenty :)

Good luck.