Use the financial statements available via download from Learn which contain three years of income statements and balance sheets for Zieber company. Once you type in the base set of assumptions in part 1 below, all other cells in the spreadsheet should be computed using formulas rather than by just typing in numbers. If you find yourself typing in a number on the spreadsheet, think about how to enter it using a formula.
- On the first sheet “Base Case” of the spreadsheet, forecast AFN using the assumption that sales will grow by 5%, dividends will grow by 6%, the tax rate is 40%, the interest rate on short-term debt is 9%, and the interest rate on long-term debt is 11%. Additionally assume the ratios of expenses to sales, depreciation to fixed assets, cash to sales, accounts receivable to sales, inventories to sales, accounts payable to sales, and accruals to sales will be the same in 2014 as the average of these ratios over the past three years. If AFN is positive, assume that Zieber will issue new short-term debt at the end of the year. If AFN is negative, assume Zieber will pay a special dividend.
- On the second sheet “Modification” of the spreadsheet, forecast AFN using all of the same assumptions in the Base Case (assume that short-term debt will be issued at the end of the year), and apply one of the following modifications:
Modification 1: Sales growth will be 8%; the ratio of accounts payable/sales will be 9%; the interest rate on short-term debt will drop to 3% and the rate for long-term debt will be 5%.
Modification 2: Sales growth will fall to 2%, but dividend growth will be 12%. However, operating expenses/sales will fall to 80%.
Modification 3: Sales growth and dividend growth will be 2% each, and the ratio of fixed assets to sales will be 39%.
Modification 4: The ratio of operating expenses/sales will be 88%. The interest rate on short-term debt will drop to 4% and the rate for long-term debt will be 6%.
Modification 5: Sales growth will be 7%, and dividend growth will be 10%. The ratio of accounts payable/sales will be 11%.
If your last name begins with A-E, Modification 1.
If your last name begins with F-J, Modification 2.
If your last name begins with K-O, Modification 3.
If your last name begins with P-T, Modification 4.
If your last name begins with U-Z, Modification 5.
- On the final sheet “Ratios” of the spreadsheet, compute and show the ratios listed on the sheet for the base case and modification. Then provide a discussion in the highlighted box about the trends in each ratio category over time (2011 -2013) and in the differences between the base case and modification.
Is this the question you were looking for? If so, place your order here to get started!
