Now that you have completed your Simply Sales pro forma using the ts of sales method, we are going to consider a slight modification of your pro torma to make it more user friendly. Instead of calculating all 2012 numbers as an increase from 2011 based on direct links to changes in sales. We will modify severat links to make our pro forma easier to use Specifically we will modify the tactors and links to COGS, SG8A. accounts tecevable, and iruentory, Keep reading. Cost of goods sold and SGRA: Typically we express expenses as a of of sales. For instance, in this case CoGS as a percentage of sales in 2011 is 90%($9,000/510,000), and $68A as a percentage of sales is 7%($700/510,000) This means to calculate COGS for 20027 we could multply sales br 90%. COGS=Sales90% and to calculate SG8A we could multuply sales by 7% SGRA=Sales7% Of course we will use cell references rather than numbers in our formulas. This is demonstrated following example Now go to the income statement tab and change the factors and links for COGS and SG8A to express them as a % of sales. This should give zou the same 20027 account balances as before, but we will find expressing them in these terms is much more useful Accounts Receivable and lnventory Now we will work on expressing balance sheet accounts in altemative forms, that may be more useful. Specifically we will compare AR when expressed as a % of sales, in terms of AR turnover, and in terms of the AR collection period. Note that, while all three formulas give us the same answer. we wil find some formulas more useful than others ow go to the income statement tab and change the factors and links for OGS and SG\&A to express them as a \% of sales. This should give you the same 20027 account alances as before, but we will find expressing them in these terms is much more useful. ccounts Receivable and Inventory low we will work on expressing balance sheet accounts in alternative forms, that may be more useful. ipecifically we will compare AR when expressed as a % of sales, in terms of AR turnover, and in terms f the AR collection period. Note that, while all three formulas give us the same answer, we will find ome formulas more useful than others. Notice that if we change the percentage increase in sales (E49) all three formulas result in an equal change in ARR. However, one advantage of spreadsheets is that they enable us to examine alternative scenarios. For example, we might want to examine the impact on cash flows of lengthening our collection period to 20 days in hopes of increasing sales by 35%. While all three formulations for ARR can do this, the third formulation is the easiest to use. Change cell E49 to 35% and cell E54 to 20 . That it, it is done. Now try to change the factors in cells E52 and E53 to get the same Accounts Receivable balance of 740 . I prefer to think in terms of the collection period. If you find it easier to think in terms of ARR turnover you will prefer to use the second formula in row 53 . Simple Sales Inc., Balance Sheet for year ending December 31 20112012 Fuctor Forecast Link Liabilities \& Shareholders' Fquity Accounts payable Accrued wages Total current liabilities Long term debt Total Liabilities Common Stock Retained earnings Total shareholders' equity Total liabilities and shareholders' equity Now that you have completed your Simply Sales pro forma using the ts of sales method, we are going to consider a slight modification of your pro torma to make it more user friendly. Instead of calculating all 2012 numbers as an increase from 2011 based on direct links to changes in sales. We will modify severat links to make our pro forma easier to use Specifically we will modify the tactors and links to COGS, SG8A. accounts tecevable, and iruentory, Keep reading. Cost of goods sold and SGRA: Typically we express expenses as a of of sales. For instance, in this case CoGS as a percentage of sales in 2011 is 90%($9,000/510,000), and $68A as a percentage of sales is 7%($700/510,000) This means to calculate COGS for 20027 we could multply sales br 90%. COGS=Sales90% and to calculate SG8A we could multuply sales by 7% SGRA=Sales7% Of course we will use cell references rather than numbers in our formulas. This is demonstrated following example Now go to the income statement tab and change the factors and links for COGS and SG8A to express them as a % of sales. This should give zou the same 20027 account balances as before, but we will find expressing them in these terms is much more useful Accounts Receivable and lnventory Now we will work on expressing balance sheet accounts in altemative forms, that may be more useful. Specifically we will compare AR when expressed as a % of sales, in terms of AR turnover, and in terms of the AR collection period. Note that, while all three formulas give us the same answer. we wil find some formulas more useful than others ow go to the income statement tab and change the factors and links for OGS and SG\&A to express them as a \% of sales. This should give you the same 20027 account alances as before, but we will find expressing them in these terms is much more useful. ccounts Receivable and Inventory low we will work on expressing balance sheet accounts in alternative forms, that may be more useful. ipecifically we will compare AR when expressed as a % of sales, in terms of AR turnover, and in terms f the AR collection period. Note that, while all three formulas give us the same answer, we will find ome formulas more useful than others. Notice that if we change the percentage increase in sales (E49) all three formulas result in an equal change in ARR. However, one advantage of spreadsheets is that they enable us to examine alternative scenarios. For example, we might want to examine the impact on cash flows of lengthening our collection period to 20 days in hopes of increasing sales by 35%. While all three formulations for ARR can do this, the third formulation is the easiest to use. Change cell E49 to 35% and cell E54 to 20 . That it, it is done. Now try to change the factors in cells E52 and E53 to get the same Accounts Receivable balance of 740 . I prefer to think in terms of the collection period. If you find it easier to think in terms of ARR turnover you will prefer to use the second formula in row 53 . Simple Sales Inc., Balance Sheet for year ending December 31 20112012 Fuctor Forecast Link Liabilities \& Shareholders' Fquity Accounts payable Accrued wages Total current liabilities Long term debt Total Liabilities Common Stock Retained earnings Total shareholders' equity Total liabilities and shareholders' equity