Hena plc has a division that manufactures and sells solar panels. Demand for solar panels has picked up recently and the company is looking to increase its output. Hena plc’s division currently manufactures 600,000 solar panels annually and is looking to double this capacity. A new factory has become available at an annual rent of £600,000 per annum payable quarterly in advance. New plant and machinery would cost £1.8 million, payable immediately on delivery on 1 January. This new plant and machinery would have a useful life of ten years and would be depreciated on a straight line basis with £Nil residual value. The directors of Hena plc are now wondering whether they should go ahead with the new solar panel factory. They have produced the following projections upon which to base their budgets.

Hena plc sells each solar panel for £150. Demand for the increased output is expected to be as follows:

• January: 20,000 panels

• February and March: 30,000 panels per month

• April: 40,000 panels

• May to August: 80,000 panels per month

• September: 60,000 panels

• October and November: 40,000 panels per month

• December: 20,000 panels

• All panels are sold to credit customers, 10% of whom pay in the month of sale, 60% in the month after and the remaining 30% two months after the month of sale. Details of production costs are as follows:

• Materials cost is 30% of the selling price of the panels. Materials suppliers are paid in the month after production and sales have taken place.

• Production labour is 20% of the selling price; 70% of this amount is payable in the month of sale and the remainder, representing deductions from production wages for tax and national insurance, is paid to HM Revenue and Customs one month after production and sales have taken place.

• Other variable production costs of 10% of selling price are paid in the month of sale. Fixed costs are estimated to be £50,000 per month and are to be treated as paid in the month in which they were incurred. Hena plc manufactures to order and sells all its production and has no inventories of solar panels at the end of the year.

Required

Using a spreadsheet of your choice, prepare the following statements for the next 12 months:

• A sales budget

• A production costs budget

• A monthly cash budget

• A monthly budgeted statement of profit or loss

• A budgeted statement of financial position at the end of the 12 months

Advise the directors of Hena plc whether they should go ahead with the proposed expansion or not.