Need help with what exel formula to use for the balance sheet qs 5 &6. For the % of sales in the BS, do i just divide by sales to customer(93,775) in the Income Statement or no?
I also have the same trouble with the IS qs 3&4 regarding the percentage of sales and projected sales.
It'll be great if you provide all the exel formulas for each questions. Thank you.
Balance Sheet Tab: 1. Copy cels A2-C38 from the consolidated Balance Sheets - USD 1S) Sin Millions" and paste them in the yellow cel (cell A2). 2. Manually input the 2019 balance sheet values into the blue boxes in column D. 3. Using Excel formulas, compute the 2019 2020 average and 2020 & 2021 average for each line item. Put your answer in the corresponding blue bor. 4. Using Excel formulas, compute the common size balance sheet for 2019, 2020, and 2021 for each line tem. Put your answer in the corresponding blue box S. Using Excel formulas, you will be creating a projected balance sheet for December 2022 using the Percent of Sales method in the blue boxes in column M, you will be computing the percent of sales values. In the blue boxes in colunin, you will be computing the balance sheet projections for December 2002. In the green boxes in column O, you will be computing the total amount of a balance sheettem (ex. total assets, total abilities, etc.). In cell 035, you will be computing retained earnings. Instructions for computing retained earnings are written next to this cell 6. Using Excel formulas, compute the external financing needed amount. Put your answer in the corresponding green box - there is something that does NOT affect the balance sheet projections, write "No Change in the corresponding line Hom's Percent of Sales box in column Mand change the color of the corresponding line item's cells in columns Mandoto yellow Income Statement Tab 1. copy cells A1-D22 from the Consoldated Statements of Earrings- USD (S1shares in Milton, Sin Millons" and paste them in the yellow cell cell A6). 2. Using Excel formulas, compute the common site income statement for 2019, 2020, and 2021 for each line item. Put your answer in the corresponding blue box Excel formulus, you will be creating a procted income statement for 2022. You will be projecting sales using the growth rate that you need to obtain from Yahoo Finance and input into cel Ma, in the blue bones in column you will be computing the percent of sales values in the blue boxes in colant M, you will be computing the income statement projections for 2022. in the green boxes incolumin M. you will be computing the final amount of an income statement item for gross profit net income, etc.), in cell Mo, you will be computing the income taxes using the tax rate that provided for you and is highlighted in orange 4. Using Excel formulus, you will be computing the dividenda paid cell 182) and payout ratio fcell 33). The value for dividends paid in this section will be different than the value that you calculated for dividends paid in your projected income statement " there is something that DOES NOT affect the income statement projections, write 'No Change in the corresponding line item's Percent of Sales' box in column and change the color of the corresponding line item's cells in columns and M to yellow 14 LE int HALL SELE TILL DO TE HL 11 1 . STE TE TER PH VE IL COMER Canila-si 0 T G20 10/09 D Growth Rae Tax Rate Common Sireneme Statement Projected income Statement Percent of Sales Projections Dec 2022 12 Months Ended Jan 09.2002 I 09.2001 Dec 21, 2007 2021 2000 2019 5 5 12.05 3000 SS 34 50 599.775 20.30 61.920 24 14714 . 2 Grosso Consolidated Statements of Earrings- USD 151 shares in Milos Sin Million come Abtract Ses to customers cost of products solid Gross profit Serinlendiri terses 3 Research and development expense cesech and development interest expense, niet of orion Cape Note 4 one none expert estructuring Note 20 camins Before provision for these income 4117 2200 12.119 11 221 25 290 32 SS 20 IN ON CN N 34 1 19 58 183 489 252 22.7N 1.898 219 201 247 16,437 1,183 318 2.525 266 11.336 39 ON 215 3 100 220 200 20 3 Iwone before wek| Income Taxes Netcome 28 223 520,78 514754 son income Note Nekate $15,333 $52 35.50 57.93 3781 $5.31 $5. 3 tasie in dieser Dilute findes per Average shares standing totes 1 and 19 Basic in the Diluted in shares 2.331 2674 2.600 2.670.7 25433 2014 Dividends Paid Add to Bandar Divided Payout the - Dividends to shareholder om Statement of Cash Balance Sheet Tab: 1. Copy cels A2-C38 from the consolidated Balance Sheets - USD 1S) Sin Millions" and paste them in the yellow cel (cell A2). 2. Manually input the 2019 balance sheet values into the blue boxes in column D. 3. Using Excel formulas, compute the 2019 2020 average and 2020 & 2021 average for each line item. Put your answer in the corresponding blue bor. 4. Using Excel formulas, compute the common size balance sheet for 2019, 2020, and 2021 for each line tem. Put your answer in the corresponding blue box S. Using Excel formulas, you will be creating a projected balance sheet for December 2022 using the Percent of Sales method in the blue boxes in column M, you will be computing the percent of sales values. In the blue boxes in colunin, you will be computing the balance sheet projections for December 2002. In the green boxes in column O, you will be computing the total amount of a balance sheettem (ex. total assets, total abilities, etc.). In cell 035, you will be computing retained earnings. Instructions for computing retained earnings are written next to this cell 6. Using Excel formulas, compute the external financing needed amount. Put your answer in the corresponding green box - there is something that does NOT affect the balance sheet projections, write "No Change in the corresponding line Hom's Percent of Sales box in column Mand change the color of the corresponding line item's cells in columns Mandoto yellow Income Statement Tab 1. copy cells A1-D22 from the Consoldated Statements of Earrings- USD (S1shares in Milton, Sin Millons" and paste them in the yellow cell cell A6). 2. Using Excel formulas, compute the common site income statement for 2019, 2020, and 2021 for each line item. Put your answer in the corresponding blue box Excel formulus, you will be creating a procted income statement for 2022. You will be projecting sales using the growth rate that you need to obtain from Yahoo Finance and input into cel Ma, in the blue bones in column you will be computing the percent of sales values in the blue boxes in colant M, you will be computing the income statement projections for 2022. in the green boxes incolumin M. you will be computing the final amount of an income statement item for gross profit net income, etc.), in cell Mo, you will be computing the income taxes using the tax rate that provided for you and is highlighted in orange 4. Using Excel formulus, you will be computing the dividenda paid cell 182) and payout ratio fcell 33). The value for dividends paid in this section will be different than the value that you calculated for dividends paid in your projected income statement " there is something that DOES NOT affect the income statement projections, write 'No Change in the corresponding line item's Percent of Sales' box in column and change the color of the corresponding line item's cells in columns and M to yellow 14 LE int HALL SELE TILL DO TE HL 11 1 . STE TE TER PH VE IL COMER Canila-si 0 T G20 10/09 D Growth Rae Tax Rate Common Sireneme Statement Projected income Statement Percent of Sales Projections Dec 2022 12 Months Ended Jan 09.2002 I 09.2001 Dec 21, 2007 2021 2000 2019 5 5 12.05 3000 SS 34 50 599.775 20.30 61.920 24 14714 . 2 Grosso Consolidated Statements of Earrings- USD 151 shares in Milos Sin Million come Abtract Ses to customers cost of products solid Gross profit Serinlendiri terses 3 Research and development expense cesech and development interest expense, niet of orion Cape Note 4 one none expert estructuring Note 20 camins Before provision for these income 4117 2200 12.119 11 221 25 290 32 SS 20 IN ON CN N 34 1 19 58 183 489 252 22.7N 1.898 219 201 247 16,437 1,183 318 2.525 266 11.336 39 ON 215 3 100 220 200 20 3 Iwone before wek| Income Taxes Netcome 28 223 520,78 514754 son income Note Nekate $15,333 $52 35.50 57.93 3781 $5.31 $5. 3 tasie in dieser Dilute findes per Average shares standing totes 1 and 19 Basic in the Diluted in shares 2.331 2674 2.600 2.670.7 25433 2014 Dividends Paid Add to Bandar Divided Payout the - Dividends to shareholder om Statement of Cash
