|
|
Break Even Analysis and Budgeting - Activity
Both these concepts are important in the planning process for any business. They allow the business to assess the possible outcomes of different situations. This Activity will give you the opportunity to use some of these techniques.
Look at the Break Even Analysis and Budgeting Excel spreadsheet. The information provides details of the projected income and expenditure for a firm of plant wholesalers. The company raises young plants and sells them on to garden centres in their region. The budget has been based on an average price charged per plant sold of £2.
Image copyright: Michael Slonecker
Your Task
- Using the Excel spreadsheet, produce a break even chart showing total revenue, variable costs, fixed costs and total costs. From your chart, estimate the break even number of sales.
- Use appropriate functions to calculate the profit or loss made each month and the overall level of profit/loss expected over the year.
- The company's management have asked you to advise them on the likely impact of the following strategies given the estimate of the price elasticity of demand as being -1.5.
- A reduction in price from £2.00 to £1.80 per plant
- An increase in price from £2.00 to £2.50 per plant
For each case, produce the associated break even chart to illustrate your point.
- Advise the company on an appropriate margin of safety offering the reasons for your judgement.
- Now look at Sheet 1b that shows the actual figures for the first 6 months. The company have been hit by lower sales than predicted because of the cold weather and by a rise in the interest rate affecting the cost of a loan they have. Calculate the new break even level of sales.
- Advise the company on a suitable strategy for the remaining 6 months to enable them to meet their original budget targets.
|
