The Company makes high-end racing bikes. This year it will produce 12,000 at a cost of $2,500 each.

The production variable costs are, per bike:

• Materials - $325
• Labour - $475
• Other - $130

The fixed costs this year are:

• Property related - $825,000
• Salaries - $1,290,000
• Other - $3,750,000

The company has borrowings of $7.5 million, at an annual interest rate of 5%. It pays tax at 19%.

For the forthcoming year it is predicting the following:

• An increase in sales volume of 7.5% and sales prices of 3%. Variable costs will rise:
  • Materials - 8.25%
  • Labour - 2%
  • Other - 1%
• Fixed costs are expected to rise:
  • Property related - 3.25%
  • Salaries - 2%
  • Other - 1.75%

The company will borrow a further $1 million, at the same interest rate. The tax rate will remain at 19% for the forthcoming year.

This year the receivables day figure was 50 days. Next year the forecast is 40 days.

a. Produce an Excel-based model which shows the current income statement and the forecast position. The model should be capable of being used to sensitise the forecast. Your model should show contribution, operating profit, profit before tax and net profit and estimate the cash received from customers.