From the course: Financial Modeling and Forecasting Financial Statements

Forecast sales-based expenses

From the course: Financial Modeling and Forecasting Financial Statements

Start my 1-month free trial

Forecast sales-based expenses

“

In this exercise, we will construct a forecasted income statement and balance sheet for Hahn Company for year two. The year one starting point is the same as with the Derald Company example that we did earlier, but the initial forecasts and preliminary decisions made by Hahn Company's management are a bit different. Those initial assumptions are as follows. Sales are forecasted to increase 50% in year two. property plant and equipment will increase from $300 to $700. Loans payable will increase from $300 to $400. The gross profit percentage will increase from 30% to 32%. Other operating expenses as a percentage of sales will increase from 18.5% to 20%. The income tax rate will increase from 42.9% all the way up to 60%. That's our assumption to start with. Dividends will double from $15 to $30. Remember that these are preliminary forecasts and decisions. The point of financial modeling and forecasting is to do a disciplined analysis of the implications of these forecasts and decisions. Now let's start with the income statement. Here is the forecasted income statement for year two given these preliminary assumptions. Recall that the expected level of depreciation is tied to the expected level of property, plant, and equipment. With property, plant, and equipment forecasted to increase from $300 to $700, depreciation expense is forecasted to increase from $15 to $35. Now the expected level of interest expense is tied to the expected level of loans. With loans forecasted to increase from $300 to $400, interest expense is forecasted to increase from $30 to $40. Okay, let's imagine that we are the company's chief operating officer, the COO. The accountants have brought me this forecasted income statement for next year. Do I have any questions? Yeah, I have lots of questions. Let's focus on the questions related strictly to the income statement. We'll talk about property, plant, and equipment, and depreciation expense, and loans and interest expense later. Let's start right at the top. What makes you, the accountants, think that we can increase our sales next year by 50% over this year? I wanna see the detailed plan. I wanna see that you financial modelers have received input with specifics from the marketing people, from the sales people and from the distribution people. And I want to be assured that this is a forecast that has been critically evaluated, that people have pushed back to make sure this isn't just some optimistic hope or dream. Next, how about this increase in gross profit percentage from 30% to 32%? What in the world makes you think that is possible? Let's assume that this increase in margin is to be achieved through price increases rather than reductions in production costs. So as the COO, my question is this, has there been a change in our competitive environment to make this possible? Do we have evidence from customer surveys that we can increase our prices to increase our margins? How are our competitors going to respond? Finally, why will our other operating expenses increase as a percentage of sales? I can see why they would increase, but why are they increasing faster than sales are increasing? Aren't there some efficiencies, some economies of scale? Aren't some of these costs fixed so they won't go up even if sales increase? Okay, you can see that the COO would have many questions for the accountants. Financial modeling and forecasting is not just a relaxing and fun spreadsheet game. Each of the forecasting assumptions must be researched, supported, justified, and explained.

Contents