Level 2 Evaluating Expansion Financing Options for Granite City Books Granite City Books is planning a $2.5 million expansion of its facilities. It needs to evaluate its options for financing the expansion. The companys bank might not allow it to obtain more long-term debt financing if its debt-to-equity ratio gets too high. Alternatively, com- mon stockholders might be displeased if their ownership rights become diluted by issuing a substantial amount of preferred stock, or even additional common stock if its not issued on a pro rata basis. A workbook has been started that contains a basic Balance Sheet, sol- vency, and capital structure ratio data. Your task is to create scenarios for each financing alternative and prepare reports that Granite City Books bank and management can use to compare the various financing alternatives. Complete the following:

1. Open the workbook named Granite.xlsx located in the Chapter 8 folder, and save it as Granite City Expansion.xlsx in the same location.

2. In the Options worksheet, apply appropriate names to the worksheets scenario changing cells as described in Table 8.5.

Table 8.5: Descriptions of the changing cells

Changing Cell	Description
F9	Change in assets
F12	Change in long-term debt
F14	Change in the dollar amount of common stock issued
F15	Change in the dollar amount of preferred stock issued

3. Create four scenarios in the Options worksheet using the scenario names and chang- ing cell values shown in Table 8.6.

Table 8.6: Data for the four scenarios

Changing Cell	Long-Term Debt Financing	Common Stock Financing	Preferred Stock Financing	Balanced Financing
F9	2,500	2,500	2,500	2,500
F12	2,500	0	0	1,000
F14	0	2,500	0	750
F15	0	0	2,500	750

4. Based on the information in Table 8.7, apply appropriate names to the worksheets result cells.

Table 8.7: Descriptions of the result cells

Cell	Description
F13	Change in total liabilities
F17	Change in total equity
G21	Debt-to-equity ratio
G24	Long-term-debt-to-common-equity ratio
G25	Preferred stock ratio

5. Create a professional-looking scenario summary report that shows all the result cells listed in Table 8.7.

6. Create a professional-looking scenario PivotTable that shows the last three result cells listed in Table 8.7. Also generate a PivotChart based on the PivotTable.

7.Save and close the Granite City Expansion.xlsx workbook

A B C G H I J D E F Granite City Books Financing Options for Expansion As of December 31, 2013 (000's omitted) 2011 Changes 2012 % Change Summary Balance Sheet Total assets $ 2,225.00 $ 2,500.00 $ 4,725.00 112.36% $ 278.00 $ 550.00 828.00 1,000.00 (27.00) $ 251.00 2,500.00 3,050.00 2,473.00 3,301.00 1,000.00 -9.71% 454.55% 298.67% 0.00% 1 Current liabilities 2 Long-term liabilities 3 Total liabilities 4 Common stock 5 Preferred stock 5 Retained earnings 7 Total stockholders' equity 3 Total liabilities & stockholders' equity 397.00 1,397.00 $ 2,225.00 27.00 27.00 $ 2,500.00 424.00 1,424.00 $ 4,725.00 6.80% 1.93% 112.36% Ratio Analysis 1 Debt-to-equity ratio 2 Long-term-debt to equity ratio 3 Debt-to-common-equity ratio 4 Long-term-debt to common equity 5 Common-to-preferred equity 59.27% 39.37% 82.80% 55.00% 0.00% 231.81% 214.19% 330.10% 305.00% 0.00% US W A B C G H I J D E F Granite City Books Financing Options for Expansion As of December 31, 2013 (000's omitted) 2011 Changes 2012 % Change Summary Balance Sheet Total assets $ 2,225.00 $ 2,500.00 $ 4,725.00 112.36% $ 278.00 $ 550.00 828.00 1,000.00 (27.00) $ 251.00 2,500.00 3,050.00 2,473.00 3,301.00 1,000.00 -9.71% 454.55% 298.67% 0.00% 1 Current liabilities 2 Long-term liabilities 3 Total liabilities 4 Common stock 5 Preferred stock 5 Retained earnings 7 Total stockholders' equity 3 Total liabilities & stockholders' equity 397.00 1,397.00 $ 2,225.00 27.00 27.00 $ 2,500.00 424.00 1,424.00 $ 4,725.00 6.80% 1.93% 112.36% Ratio Analysis 1 Debt-to-equity ratio 2 Long-term-debt to equity ratio 3 Debt-to-common-equity ratio 4 Long-term-debt to common equity 5 Common-to-preferred equity 59.27% 39.37% 82.80% 55.00% 0.00% 231.81% 214.19% 330.10% 305.00% 0.00% US W