A B C D G H Accounts Receivable and Inventory Now we will work on expressing balance sheet accounts in alternative forms, that may be more useful. Specifically we will compare A/R when expressed as a \% of sales, in terms of A/R turnover, and in terms of the A/R collection period. Note that, while all three formulas give us the same answer, we will find some formulas more useful than others. Sales $10,000 13,500 35.0% Accounts receivable Accounts receivable Accounts receivable \begin{tabular}{rrrr} 2001 & 20027 & Factor & Forecast Link \\ \hline 298 & 405 & 3.0% as \% of sales \end{tabular} 40533.30 in terms of A/R turnover 74020.00 in terms of A/R collection period Notice that if we change the percentage increase in sales (E49) all three formulas result in an equal change in A/R. 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 A/R 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 A/R turnover you will prefer to use the second formula in row 53 . Now go to the balance sheet and express Inventory and A/R in terms of days. Be sure to set iterations to 100 to solve circular reference. Tools Options Calculations Check iterations and set equal to 100 A B \begin{tabular}{l|l} C & D \end{tabular} E Simple Sales Inc., Income Statement For year ending December 31 ($000 's) 20112012 Factor Forecast Link Net sales \$ 10,000$12,500,25.0% increase equals % increase in sales Cost of goods sold Gross profit 25.0% as a percentage of sales General, selling, and admin. e Net interest expense (522) 25.0% as a percentage of sales 10.0% Current year's long-term debt balance x interest rate Income before income taxes Provission for income taxes Net Income 30.0% taxable income 30% tax rate Dividends ( Payout =40%) 56 3646 A B C D E G H I Notes: Read these notes. Now that you have completed your Simply Sales pro forma using the \% of sales method, we are going to consider a slight modification of your pro forma 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 several links to make our pro forma easier to use. Specifically we will modify the factors and links to COGS, SG\&A, accounts receivable, and inventory. Keep reading. Cost of goods sold and SG\&A: Typically we express expenses as a \% of sales. For instance, in this case COGS as a percentage of sales in 2011 is 90%($9,000/$10,000), and SG\&A as a percentage of sales is 7%($700/$10,000). This means to calculate COGS for 20027 we could multiply sales by 90%. COGS = Sales 90% and to calculate SG\&A we could multiply sales by 7%. SG\&A = Sales 7% Of course we will use cell references rather than numbers in our formulas. This is demonstrated following example. Sales Cost of goods sold Gross profit General, selling, and admin. expense Now go to the income statement tab and change the factors and links for COGS and SG\&A to express them as a \% of sales. This should give you the same 2011 account balances as before, but we will find expressing them in these terms is much more useful. A B C D G H Accounts Receivable and Inventory Now we will work on expressing balance sheet accounts in alternative forms, that may be more useful. Specifically we will compare A/R when expressed as a \% of sales, in terms of A/R turnover, and in terms of the A/R collection period. Note that, while all three formulas give us the same answer, we will find some formulas more useful than others. Sales $10,000 13,500 35.0% Accounts receivable Accounts receivable Accounts receivable \begin{tabular}{rrrr} 2001 & 20027 & Factor & Forecast Link \\ \hline 298 & 405 & 3.0% as \% of sales \end{tabular} 40533.30 in terms of A/R turnover 74020.00 in terms of A/R collection period Notice that if we change the percentage increase in sales (E49) all three formulas result in an equal change in A/R. 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 A/R 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 A/R turnover you will prefer to use the second formula in row 53 . Now go to the balance sheet and express Inventory and A/R in terms of days. Be sure to set iterations to 100 to solve circular reference. Tools Options Calculations Check iterations and set equal to 100 A B \begin{tabular}{l|l} C & D \end{tabular} E Simple Sales Inc., Income Statement For year ending December 31 ($000 's) 20112012 Factor Forecast Link Net sales \$ 10,000$12,500,25.0% increase equals % increase in sales Cost of goods sold Gross profit 25.0% as a percentage of sales General, selling, and admin. e Net interest expense (522) 25.0% as a percentage of sales 10.0% Current year's long-term debt balance x interest rate Income before income taxes Provission for income taxes Net Income 30.0% taxable income 30% tax rate Dividends ( Payout =40%) 56 3646 A B C D E G H I Notes: Read these notes. Now that you have completed your Simply Sales pro forma using the \% of sales method, we are going to consider a slight modification of your pro forma 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 several links to make our pro forma easier to use. Specifically we will modify the factors and links to COGS, SG\&A, accounts receivable, and inventory. Keep reading. Cost of goods sold and SG\&A: Typically we express expenses as a \% of sales. For instance, in this case COGS as a percentage of sales in 2011 is 90%($9,000/$10,000), and SG\&A as a percentage of sales is 7%($700/$10,000). This means to calculate COGS for 20027 we could multiply sales by 90%. COGS = Sales 90% and to calculate SG\&A we could multiply sales by 7%. SG\&A = Sales 7% Of course we will use cell references rather than numbers in our formulas. This is demonstrated following example. Sales Cost of goods sold Gross profit General, selling, and admin. expense Now go to the income statement tab and change the factors and links for COGS and SG\&A to express them as a \% of sales. This should give you the same 2011 account balances as before, but we will find expressing them in these terms is much more useful