#pro forma income statement
(1) Take note that a large part of the 2010 purchases remains on hand, which amounts to $62,537 while the projected sales for 2011 are targeted at $53,637.
(3) In addition, the entrepreneur should be on the lookout for the latest products but test their viability first, before fully stocking-up on them.
(4) In the event that poor-selling products will still remain unsold, the owner should sell them at a discounted price before the year ends.
(5) The estimate for future stock purchases should be limited by the equivalent cost of goods sold for the 2011 projected sales.
(6) In determining the estimated cost of goods sold, calculate the purchase cost by dividing projected sales at 120%, assuming that the mark up on prices is at 20%. For this purpose we arrived at the amount of $44,698, based on this computation: [$53,637/1.20 or (120%/100%)]
(7) Hence some of the funds for purchases should come from selling the slow-moving goods, and provide only a minimal amount of additional purchases for 2011. However, in actual practice, one can always go beyond the projected amount if the demand for the new product has a good turnover rate.
(8) Remember, these are only guesses but the accounting principle of conservatism should be observed – thus, we project $5,000 only for additional purchases. This may increase but will depend on the viability of the items.
(9)Post $5,000 to cell J19
Please continue on page 2 for more on working with the Excel Pro Forma Income Statement
As you continue to work with the Excel Pro Forma Income Statement, you will also consider:
Cell J20 will automatically add the values in cells J18 and J19; hence there is no amount to post.
Stock Inventory – Year End
(1) Since we made it a point to limit our cost of goods sold to $44,698, our estimated inventory will be the difference between the total goods available for sale ($67,537) and the cost of goods sold ($44,698), which is $22,839.
(2) Stock Inventory at year-end is expected to dwindle down to $22,839 by selling the slower-moving goods at discounted prices and by limiting the purchases.
(3) Post $22,839 to cell J21
Cost of Goods Sold – The amount for cell J22 is automatically generated.
Gross Profit (Loss) – The amount for cell J24 is automatically generated; Net Sales (J15) less Cost of Goods Sold (J22).
(1) You will notice that our historical figures resulted in a net income at near break-even point. This was made possible by working out the operating budget within the boundaries of the gross profit margin.
(2) Our projection, so far, shows that the current estimates for sales and cost of goods sold resulted in a higher gross profit margin of $8,439 compared to that of 2010’s $7,492.
(3) To project the operating costs for 2011, maintaining the operating expenses within the amount of the 2010 historical operating costs will result in a likely chance of realizing a minimal income.
(4) Going beyond the historical costs would result in another break-even year or even a possible net loss.
(5) To drive our point, we will be using the 2010 amounts of operating expenses for our 2011 projections.
(6) Post the individual operating expenses to cells J29 through J37.
The entire 2011 projections will result in the realization of a minimal income. The amount in cell J41 was, again, automatically generated by deducting the value of cell J39 from J24.
For your own business projections, continue with the 2012 to 2014 by basing your next plans of action on the outcomes. However, evaluate the results of the projected income and expenses for 2011 and the years thereafter before doing so. Read the summary about our analysis and suggestions for our business example.
The main problem in our business example is the large amount of slow-moving stock inventory. This is often the pitfall of small-scale entrepreneurs as they are enticed by suppliers and dealers into stocking up on goods without first testing their viability in the business market and location.
However, the business owner will not realize the effects of the non-moving merchandise if he does not make future projections using an Excel pro forma income statement. In our example, the business owner will come to realize that he cannot hope for some notable improvement in next year’s operations because the business is still saddled with poor- to-moderately performing goods. Money remains tied up in those goods; hence the owner will be constrained to sell them at discounted prices, if only to recover their costs.
In the meantime, the entrepreneur may also consider infusing more capital funds in order to stock up on fast-selling items. This time, however, there should be a careful estimate on the quantity and the rate of turnover.
In using this Excel pro forma income statement for future projections, the results can readily show if you can afford to sell on credit or not. Some entrepreneurs think of credit sales as a good marketing move, but without totally evaluating their earning capacity and what makes up their resources. Future projections may only be guesses, but they are often based on real premises.