How can I use the high-low method in excel to identify fixed and variable costs?

Question

I have 4 columns of data; Month, Production and Sales in Units, Production Costs, and Selling and Admin. Costs. How can I sort this data in Excel to identify the fixed and variable cost components for both production costs and selling and administrative costs?

Also, how can I forecast income before taxes for the company based on estimates that production and sales in 2005 will be 1,400 units?

Thank you for any help.

Answer 1

Fixed costs are costs you incurred regardless. For example, Christmas holidays. Office rents and staff salaries have to be paid even there are zero sales.

Variable costs are costs incurred in production. eg Ingredients used for food sales. No sales, no ingredients needed. Hence 'variable costs'

Excel cannot read into what the company is doing and come out the correct answers tp which are fixed and which are variable. It can clue you to costs that repeats itself every month, like rental that remains the same. These are usually fixed costs.

For forecasts all you need is actuals for 2004. Divide previous sales with [income before taxes]. If it is 10%, then we can assume that, everytime being equal, income before tax for 2005 is 10% of 1,400 unit sales.

Put this out and give to your boss. He will bang your head for the wrong answers but will tell you the answers he is looking for. Put the number he suggested.