Open the Excel spreadsheet provided. Prepare a vertical analysis of Bridgford’s income statement. Enter the percentages in columns C, E, G, I and K. For additional information regarding vertical analysis, please review the following.
2. Prepare a projected income statement assuming sales increase by 3% in 2020 and 6% in 2021.
- Average your vertical analysis percentages for each income statement account and enter the results in column L, rows 5 – 11. Example: Add columns C, E, G, I and K and divide by 5. (64.07+60.58+63.17+67.57+67.34)/5=64.55%. Column L, row 5 = 64.55%.
- Enter your projected sales number in columns M & N, row 4.
- Columns M and N: Multiply projected sales by the average percentage for each income statement account.
3. Ratio Computations. Use Excel formulas to calculate financial ratios listed with the income statement and balance sheet tabs. Calculate ratios for seven years (i.e. 2015-2021). Do not prepare ratios that require data from the 2020 and 2021 balance sheet. In this case, “N/A” is entered in the appropriate cell. For additional information regarding ratios, please review the following.