Excel Exercise #1 Generating Pro Forma financial statements using Percentage of Sales Apperwach Due Friday, September 17, topen For this assignment you will use the real financial statements of an unnamed company to create proformu next yar" financial information based on assumptions for growth and managerial decision on how to allocate caring and finance shotfall 1. Create financial information indicators and ratios For each year of financial reported in the stir worksheet, cakulate the following net working capital (NWC profit margin divident and total quyout ratin total awet current and cash ratio debt ratio debt toeguity ratio retum-on (ROA and sales growth Compute each ramlet an additional rows below the income statement Donat handelen numbers into the cells Instead, the cells should contain a formula that points to dls from the 1/S or B/Sitems abowe. Format cach cell so that they have 2 decimal places only, and the correct formatting anal labeling dollars percent etc.) Rates should be in the format with 2 decimal places returns, growth rates and profit margin should be in percent. Pe Wwted for wind farming and anodig ben bir firmalar 11. Calculate Next Year's (+1) short and long-term debt (debt as a plug) Suppose that sales growth fruen the current year to the text year (+1) in 15. studhat 1. all acts on the balance sheet grow at that rate 2. all income statement items grow at that rate, 3. accounts payable grow at that rate, Brot all other liabilities and scholder equity item excepat short and long-term debt and retained casting, stay constantin, door change from Year 010 Year + 1 sand 1. The Company will maintain the Year quick ratio for Year +1. 5. The Company will traintain the Year O dividend and total paperut ratio in Vras +1 QI. What is the new level of stained carvings in this search? Q2. What external short and long-term debt financing E.FN is needed for the company next year , debtasaplog 03. How does the amount of external financing needed compare to the amount of addition sales ! What external short and long-term debt financing Leeded for the company next year le, debt as a plug! 03. How does the amount of external financing needed compare to the amount of adclition sales revenue Answer these questions by constructing the Year + financials in the pa kwa worksheet. Some hinta Items that you will need to calculate by inputting formulas using accounting relationships are highlighted green. Do not hard code al numbers into these ools. Always point to values centained in other cells All other pro forma financial statement items cither grow at 15% per assumptions above 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 zero Then calculate the Year +1 projection by adding the change to the Year Olevel. Do not hard.code the growth inte into any.cell. Point to a single and separate growth cell, highlighted yellow, so that changing the number there will automatically uplate the pro format statements Next, calculate the new level of retained camings for Year +1. First, use year Odividend payout ratio to calculate year I dividends Next, we year total payout ratio to calculate year I common sockrepurchased hint -- subtract dividend pay out from total payout). Finally, get Year 1 retained earnings by calculating the addition to retained camings and aduling it to Year retained camnings hint -- adclition to RE = NI cash dividende stock purchases) Now, wic the Year O quick ratio to calculate short-term debt needed in Year 1: First, figure out what the current liabilities need to be to maintain the Year 0 quick ratio, Then, calculate what short-term debx and current portion of long-term portion of long-term deb needs to be. This should all be programmed as a formula into the green colored cell for Year +1 Quick Ratio = current assets inventorics / Current Lanbilities Finally, calculate long-term delt as a plug that makes A = 1+ SE To do this for the row"EFN Check" enter a formula = total assets - total 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 in now equal to zero Be careful. Make sure that your total liabilities and total equity cells us formulas that som the appropriate elements of your pro forma financial statement die, do now assume that they grow at 15%! Include at the bottom of your spreadsheet, include calculations for o Quick ratio 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 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 3 v C C 132 fx 0 Yer1 Change BALANCE SHEET ANTS Ch Short Terms Accueil Net Year TANA Sale 15% All gows by 15% ote Natt Totswd angles het 133 768 26 481 2 662 6751 16 146 132 981 22 431 2181 5 103 30 209 6023 45 228 6076 250 312 1862 4736 7442 258 845 LABILITIES Stub Othe Lane 8 617 5573 Oh 81 935 7 390 11 657 36698 83 870 5486 17 500 162 01 QUITY 36 535 176 130 an rita' 71 223 13682 293 -1 255 82710 25884 69 315 17769 15 -1198 87 711 250 112 shop 110 175 2016 954 96 016 26765 7855 32 620 1775 871 BRE 2154 2214 17 2323 OS SE 40 Ratios Pro forma Read Extension Excel Exercise #1 Generating Pro Forma financial statements using Percentage of Sales Apperwach Due Friday, September 17, topen For this assignment you will use the real financial statements of an unnamed company to create proformu next yar" financial information based on assumptions for growth and managerial decision on how to allocate caring and finance shotfall 1. Create financial information indicators and ratios For each year of financial reported in the stir worksheet, cakulate the following net working capital (NWC profit margin divident and total quyout ratin total awet current and cash ratio debt ratio debt toeguity ratio retum-on (ROA and sales growth Compute each ramlet an additional rows below the income statement Donat handelen numbers into the cells Instead, the cells should contain a formula that points to dls from the 1/S or B/Sitems abowe. Format cach cell so that they have 2 decimal places only, and the correct formatting anal labeling dollars percent etc.) Rates should be in the format with 2 decimal places returns, growth rates and profit margin should be in percent. Pe Wwted for wind farming and anodig ben bir firmalar 11. Calculate Next Year's (+1) short and long-term debt (debt as a plug) Suppose that sales growth fruen the current year to the text year (+1) in 15. studhat 1. all acts on the balance sheet grow at that rate 2. all income statement items grow at that rate, 3. accounts payable grow at that rate, Brot all other liabilities and scholder equity item excepat short and long-term debt and retained casting, stay constantin, door change from Year 010 Year + 1 sand 1. The Company will maintain the Year quick ratio for Year +1. 5. The Company will traintain the Year O dividend and total paperut ratio in Vras +1 QI. What is the new level of stained carvings in this search? Q2. What external short and long-term debt financing E.FN is needed for the company next year , debtasaplog 03. How does the amount of external financing needed compare to the amount of addition sales ! What external short and long-term debt financing Leeded for the company next year le, debt as a plug! 03. How does the amount of external financing needed compare to the amount of adclition sales revenue Answer these questions by constructing the Year + financials in the pa kwa worksheet. Some hinta Items that you will need to calculate by inputting formulas using accounting relationships are highlighted green. Do not hard code al numbers into these ools. Always point to values centained in other cells All other pro forma financial statement items cither grow at 15% per assumptions above 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 zero Then calculate the Year +1 projection by adding the change to the Year Olevel. Do not hard.code the growth inte into any.cell. Point to a single and separate growth cell, highlighted yellow, so that changing the number there will automatically uplate the pro format statements Next, calculate the new level of retained camings for Year +1. First, use year Odividend payout ratio to calculate year I dividends Next, we year total payout ratio to calculate year I common sockrepurchased hint -- subtract dividend pay out from total payout). Finally, get Year 1 retained earnings by calculating the addition to retained camings and aduling it to Year retained camnings hint -- adclition to RE = NI cash dividende stock purchases) Now, wic the Year O quick ratio to calculate short-term debt needed in Year 1: First, figure out what the current liabilities need to be to maintain the Year 0 quick ratio, Then, calculate what short-term debx and current portion of long-term portion of long-term deb needs to be. This should all be programmed as a formula into the green colored cell for Year +1 Quick Ratio = current assets inventorics / Current Lanbilities Finally, calculate long-term delt as a plug that makes A = 1+ SE To do this for the row"EFN Check" enter a formula = total assets - total 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 in now equal to zero Be careful. Make sure that your total liabilities and total equity cells us formulas that som the appropriate elements of your pro forma financial statement die, do now assume that they grow at 15%! Include at the bottom of your spreadsheet, include calculations for o Quick ratio 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 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 3 v C C 132 fx 0 Yer1 Change BALANCE SHEET ANTS Ch Short Terms Accueil Net Year TANA Sale 15% All gows by 15% ote Natt Totswd angles het 133 768 26 481 2 662 6751 16 146 132 981 22 431 2181 5 103 30 209 6023 45 228 6076 250 312 1862 4736 7442 258 845 LABILITIES Stub Othe Lane 8 617 5573 Oh 81 935 7 390 11 657 36698 83 870 5486 17 500 162 01 QUITY 36 535 176 130 an rita' 71 223 13682 293 -1 255 82710 25884 69 315 17769 15 -1198 87 711 250 112 shop 110 175 2016 954 96 016 26765 7855 32 620 1775 871 BRE 2154 2214 17 2323 OS SE 40 Ratios Pro forma Read Extension