Excel Exercise #1 Generating Pro Forma financial statements using Percentage of Sales Approach Due: Friday, September 17, 10 pm For this assignment you will use the real financial statements of an unnamed company to create proforma "next year' financial information based on assumptions of sales growth and managerial decisions on how to allocate earnings and finance shortfalle 1. Create financial information indicators and ratios For each year of financial reported in the star worksheet, cakulate the following: . nut working capital NWC) profit margin dividend and total payout ratios total asset turnover Tent and cash ratios chat ratio debt-to-ceputy ratio Temat ROA and sales growth Compute each number as additional rows below the income statement. Do not hard code any numbers into the cells. Instead, the cells should contain a formula that points to cells from the 1/Sor /Sitems above. Format cach cell so that they have 2 decimal places only, and the correct formatting and labeling e-collars, percent etc.). Ratics should be in number format with 2 decimal places; returns, growth rates, and profit margir sunuld be in percent. Prints entwed for www formatting and handling om informal II. Calculate Next Year's (+1) short and long-term debt (debt as a plug) Supper that sales growth from the current year (0) to the next year (+1) ix 13and that: 1. all assets on the balance sheet grow at that rates 2. all income statement items grow at that rate: 3. accounts payable grow at that rate, but all other liabilities and shareholder exity items except short and long-term debt and retained earnings) stay constant i.e., don't change fron Year Oto Year + 1, and 4. The Company wil maintain the Year quick ratio So Year! 5. The Company will maintain the Year Odividend and total payout ratio in Year + Q1. What is the new level of retained earrings in this scenario? Q2. What external short and long-term debt financing (EF) is needed for the company text year (i.c., debt as a plug? 03. How does the amount of external financing needed compare to the amount of addition sales revenue? year (t.e. debt as a plug? Q3. How does the amount of external financing needed compare to the amount of addition sales revenue? Answer these questions by constructing the Year +1 financials in the profowwe worksheet. Some hints Items that you will need to calculate by inputting formulas using accounting relationships are highlighted green. Do not hand code any numbers into these colis. Always point to values contained in other cells. . All other proforma financial statement items either grow at 15% per assumption abwe, or stay constant Start by calculating the Year +1 projections for all income statement and balance sheet items that either grow by 15% or stay constant. Do this in two steps First calculate the change based on whether the financial item is subject to growth if it isn't then the change should be 2010 Then calculate the Year +1 projection by adding the change to the Year O level Do not hard.code the growth rate into any cellPoint to a single and separate growth cell, highlighted yellow, so that changing the number there will automatically update the pro formal statements. Next, calculate the new level of chained carvings for Year +1. First, use year O dividend payout ratio to calculate your I dividends Next, use year o total payout ratio to calculate your common stock repurchased hint - subtract dividend payout from total payout). Finally, get Year 1 retained earnings by calculating the addition to retained camings and adkling it to Year O retained eaming hint - addition to RE = NI- cash dividends - stock repurchases Now, use the Year O quick ratio to calculate short-term debt needed in Year I. First, figure out what the current liabilities need to be to maintain the Year o quick ratio, Then, calculate what "short-term dels and current portion of long-term portion of long-term debe needs to be. This should all be programmed as a formula into the green colored cell for Year +1. Quick Ratio = (current assets inventories / Current Liabilities Finally, calculate long-term debe as a plug that makes A=L+SE To do this, for the row "EFN Check" enter a formula total assets - total Habilities total equity. This is the debt as a the plug Enter that value into the change column okong-term debt and show that EPN is now equal to zero o Be careful. Make sure that your total liabilities and total equity celles formulas that sum the appropriate elements of your pro forma financial statement i, do Bow assume that they grow at 15%!). Include at the bottom of your preadsheet, include calculations for Quick ratio Total payout . liabilities - total equity. This is the debt as a the plug Enter that value into the change column of long-term debt and show that EFN is now equal to zero Be careful. Make sure that your total liabilities and total equity cells use formulas that sum the appropriate elements of your pro fortna financial statement i.e., do now assume that they grow at 15% Include at the bottom of your spreadsheet, include calculations for o Quick ratio Total payout o Debt ratio Debt to Equity ratio A final note, for simplicity we assumed that common stock paid-in capital does not change, but in fact it would change with shares repurchased or issued. But the cost method of adjustment is recursive and complicated and wwwld render the exercise unnecessarily difficult if toe tried to account for it. III. Extension - Company suspends dividends and repurchases Assume that the company bows to political pressures and decides not to repurchase any stock What is the new level of retained carnings, ST debt, and LT debt? . For this analysis, create a new work sheet and name it extension Copy and paste all the contents of the pro forma worksheet into the new worksheet. Modify the new worksheet by setting repurchases equal to zero Calculate the short-term and long-term debt that makes A = L + SE, ensuring that all previous assumptions still hold regarding the quick ratio and growth rates, At the bottom of your spreadsheet, provide a brief, plain English explanation of what happened to long-term debt and the debt ratio. Turn in An excel file with three worksheets labeled (1ration, (2pro forma, and 3) extension. Use the excel file that I give you as the template. Modify the worksheets ratios and pro format to reflect the questions asked. Create a third work sheet name extension and follow instructions per above. Do not include in your excel file any extraneous work or additional worksheets. There are no bonus points for extra work. To the contrary, anything that distracts from a clean and simple submission will result in a mark down. Use the grading rubric as a guide for what you should be focused on v C C = = = 332 fx D Chang BALANCE SHEET ASSETS Ch Short Terms Accueil.Net Year TANA Save 15% ARWSow by 15% on Naat & lint Totswd og bles het 133 768 26 481 2 662 6751 16 146 1862 41 735 132 981 22 431 2181 5 103 30 289 6023 45 228 6076 250 312 7442 LABILITIES 258 848 Stront Othe Lane Oh 3 617 5573 44 298 81 935 -2 36 535 176 130 7 390 11 657 16 698 83 870 5486 17500 162 601 QUITY an rita' 71 223 13682 293 1255 82710 25884 69 315 17769 15 -1198 87 711 250 112 110 175 2016 954 96015 26765 7855 3260 1775 871 BRE 2054 2214 17 18 2222 PEP ES 40 Ratio Read Pro forma Extension 22:30 LTED FIN357 Excel Exercise 1 (assigned).... Ratios Pro forma Extension 1211221 361 2231 12 652 10 30 136 36 230313 BALANCE SHEET(williams) ASSETS Cash & Short Terme Account Recewables, Net Invis Other Current Net Property. Plant & Equipment Total dance Iningle Other Act Total Acre LIABILITIES Account Paye ST Dch & Current Port LT Debt Oberenties Long-Term Date Deferred me Ofisi Total EQUITY Compact Retained Eating erine Gail Markets Other Appropriate arv Total Equity Late Sheryl 512 5573 4293 1915 5 3515 17.500 176130 ws 11 es -4395 111 STI T99 258548 2012 INCOME STATEMENT 5 YO Yo 110179 29918 7855 110 20 ANTS 2770 234 234 1387 1 130 19 1658 OOGSTRA Depreciation & Ametic Expenie SORA Expose EBIT Uspore No Operating Income Non-Operating interest in Incrust per Income Nel Conne. Husk can see Come purchased Addition to retained in NWC ProMargin Did you Total payeet ratio auctor 12.00 1100 - 10 dichte dh- ROA sales the D Dashboard Calendar To Do Notifications Inbox Excel Exercise #1 Generating Pro Forma financial statements using Percentage of Sales Approach Due: Friday, September 17, 10 pm For this assignment you will use the real financial statements of an unnamed company to create proforma "next year' financial information based on assumptions of sales growth and managerial decisions on how to allocate earnings and finance shortfalle 1. Create financial information indicators and ratios For each year of financial reported in the star worksheet, cakulate the following: . nut working capital NWC) profit margin dividend and total payout ratios total asset turnover Tent and cash ratios chat ratio debt-to-ceputy ratio Temat ROA and sales growth Compute each number as additional rows below the income statement. Do not hard code any numbers into the cells. Instead, the cells should contain a formula that points to cells from the 1/Sor /Sitems above. Format cach cell so that they have 2 decimal places only, and the correct formatting and labeling e-collars, percent etc.). Ratics should be in number format with 2 decimal places; returns, growth rates, and profit margir sunuld be in percent. Prints entwed for www formatting and handling om informal II. Calculate Next Year's (+1) short and long-term debt (debt as a plug) Supper that sales growth from the current year (0) to the next year (+1) ix 13and that: 1. all assets on the balance sheet grow at that rates 2. all income statement items grow at that rate: 3. accounts payable grow at that rate, but all other liabilities and shareholder exity items except short and long-term debt and retained earnings) stay constant i.e., don't change fron Year Oto Year + 1, and 4. The Company wil maintain the Year quick ratio So Year! 5. The Company will maintain the Year Odividend and total payout ratio in Year + Q1. What is the new level of retained earrings in this scenario? Q2. What external short and long-term debt financing (EF) is needed for the company text year (i.c., debt as a plug? 03. How does the amount of external financing needed compare to the amount of addition sales revenue? year (t.e. debt as a plug? Q3. How does the amount of external financing needed compare to the amount of addition sales revenue? Answer these questions by constructing the Year +1 financials in the profowwe worksheet. Some hints Items that you will need to calculate by inputting formulas using accounting relationships are highlighted green. Do not hand code any numbers into these colis. Always point to values contained in other cells. . All other proforma financial statement items either grow at 15% per assumption abwe, or stay constant Start by calculating the Year +1 projections for all income statement and balance sheet items that either grow by 15% or stay constant. Do this in two steps First calculate the change based on whether the financial item is subject to growth if it isn't then the change should be 2010 Then calculate the Year +1 projection by adding the change to the Year O level Do not hard.code the growth rate into any cellPoint to a single and separate growth cell, highlighted yellow, so that changing the number there will automatically update the pro formal statements. Next, calculate the new level of chained carvings for Year +1. First, use year O dividend payout ratio to calculate your I dividends Next, use year o total payout ratio to calculate your common stock repurchased hint - subtract dividend payout from total payout). Finally, get Year 1 retained earnings by calculating the addition to retained camings and adkling it to Year O retained eaming hint - addition to RE = NI- cash dividends - stock repurchases Now, use the Year O quick ratio to calculate short-term debt needed in Year I. First, figure out what the current liabilities need to be to maintain the Year o quick ratio, Then, calculate what "short-term dels and current portion of long-term portion of long-term debe needs to be. This should all be programmed as a formula into the green colored cell for Year +1. Quick Ratio = (current assets inventories / Current Liabilities Finally, calculate long-term debe as a plug that makes A=L+SE To do this, for the row "EFN Check" enter a formula total assets - total Habilities total equity. This is the debt as a the plug Enter that value into the change column okong-term debt and show that EPN is now equal to zero o Be careful. Make sure that your total liabilities and total equity celles formulas that sum the appropriate elements of your pro forma financial statement i, do Bow assume that they grow at 15%!). Include at the bottom of your preadsheet, include calculations for Quick ratio Total payout . liabilities - total equity. This is the debt as a the plug Enter that value into the change column of long-term debt and show that EFN is now equal to zero Be careful. Make sure that your total liabilities and total equity cells use formulas that sum the appropriate elements of your pro fortna financial statement i.e., do now assume that they grow at 15% Include at the bottom of your spreadsheet, include calculations for o Quick ratio Total payout o Debt ratio Debt to Equity ratio A final note, for simplicity we assumed that common stock paid-in capital does not change, but in fact it would change with shares repurchased or issued. But the cost method of adjustment is recursive and complicated and wwwld render the exercise unnecessarily difficult if toe tried to account for it. III. Extension - Company suspends dividends and repurchases Assume that the company bows to political pressures and decides not to repurchase any stock What is the new level of retained carnings, ST debt, and LT debt? . For this analysis, create a new work sheet and name it extension Copy and paste all the contents of the pro forma worksheet into the new worksheet. Modify the new worksheet by setting repurchases equal to zero Calculate the short-term and long-term debt that makes A = L + SE, ensuring that all previous assumptions still hold regarding the quick ratio and growth rates, At the bottom of your spreadsheet, provide a brief, plain English explanation of what happened to long-term debt and the debt ratio. Turn in An excel file with three worksheets labeled (1ration, (2pro forma, and 3) extension. Use the excel file that I give you as the template. Modify the worksheets ratios and pro format to reflect the questions asked. Create a third work sheet name extension and follow instructions per above. Do not include in your excel file any extraneous work or additional worksheets. There are no bonus points for extra work. To the contrary, anything that distracts from a clean and simple submission will result in a mark down. Use the grading rubric as a guide for what you should be focused on v C C = = = 332 fx D Chang BALANCE SHEET ASSETS Ch Short Terms Accueil.Net Year TANA Save 15% ARWSow by 15% on Naat & lint Totswd og bles het 133 768 26 481 2 662 6751 16 146 1862 41 735 132 981 22 431 2181 5 103 30 289 6023 45 228 6076 250 312 7442 LABILITIES 258 848 Stront Othe Lane Oh 3 617 5573 44 298 81 935 -2 36 535 176 130 7 390 11 657 16 698 83 870 5486 17500 162 601 QUITY an rita' 71 223 13682 293 1255 82710 25884 69 315 17769 15 -1198 87 711 250 112 110 175 2016 954 96015 26765 7855 3260 1775 871 BRE 2054 2214 17 18 2222 PEP ES 40 Ratio Read Pro forma Extension 22:30 LTED FIN357 Excel Exercise 1 (assigned).... Ratios Pro forma Extension 1211221 361 2231 12 652 10 30 136 36 230313 BALANCE SHEET(williams) ASSETS Cash & Short Terme Account Recewables, Net Invis Other Current Net Property. Plant & Equipment Total dance Iningle Other Act Total Acre LIABILITIES Account Paye ST Dch & Current Port LT Debt Oberenties Long-Term Date Deferred me Ofisi Total EQUITY Compact Retained Eating erine Gail Markets Other Appropriate arv Total Equity Late Sheryl 512 5573 4293 1915 5 3515 17.500 176130 ws 11 es -4395 111 STI T99 258548 2012 INCOME STATEMENT 5 YO Yo 110179 29918 7855 110 20 ANTS 2770 234 234 1387 1 130 19 1658 OOGSTRA Depreciation & Ametic Expenie SORA Expose EBIT Uspore No Operating Income Non-Operating interest in Incrust per Income Nel Conne. Husk can see Come purchased Addition to retained in NWC ProMargin Did you Total payeet ratio auctor 12.00 1100 - 10 dichte dh- ROA sales the D Dashboard Calendar To Do Notifications Inbox