Use the financial statements available via download from Blackboard 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 30%, the interest rate on short-term debt is 7%, and the interest rate on long-term debt is 9%. 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 2016 as the average of these ratios over the past three years. + 1. For the base case, if AFN is positive, assume that 45% of funds will come from new short-term debt at the end of the year, 35% of the funds will come from new long-term debt, 15% will come from new common stock, and 5% of funds from selling marketable securities. If AFN is negative, assume Zieber will pay 60% of it as a special dividend, pay off short-term debt and long term debt by 5% of AFN amount each, repurchase common stock in the amount of 10% of AFN and purchase marketable securities with the remaining 20%. For the modification, keep the same conditions. Therefore, all boxes that are shaded blue MUST have an lF statement. I should be able to change any of the numbers in cells G4:G7 or K4:K8 and the model should still function correctly. Once the AFN is calculated and you are trying to determine the new notes payable, long-term debt, common stock marketable securities, or the special dividend, use the =IF() function within Excel. For an example, see cell J36 on the Base Case sheet. 2. On the second sheet "Modification" of the spreadsheet, apply one of the following modifications to the base case set of assumptions: Modification 1: Sales growth will be 8% but dividend growth will fall to 3%; the ratio of accounts payable/sales will be 11%,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 38%. +' Use the financial statements available via download from Blackboard 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 30%, the interest rate on short-term debt is 7%, and the interest rate on long-term debt is 9%. 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 2016 as the average of these ratios over the past three years. + 1. For the base case, if AFN is positive, assume that 45% of funds will come from new short-term debt at the end of the year, 35% of the funds will come from new long-term debt, 15% will come from new common stock, and 5% of funds from selling marketable securities. If AFN is negative, assume Zieber will pay 60% of it as a special dividend, pay off short-term debt and long term debt by 5% of AFN amount each, repurchase common stock in the amount of 10% of AFN and purchase marketable securities with the remaining 20%. For the modification, keep the same conditions. Therefore, all boxes that are shaded blue MUST have an lF statement. I should be able to change any of the numbers in cells G4:G7 or K4:K8 and the model should still function correctly. Once the AFN is calculated and you are trying to determine the new notes payable, long-term debt, common stock marketable securities, or the special dividend, use the =IF() function within Excel. For an example, see cell J36 on the Base Case sheet. 2. On the second sheet "Modification" of the spreadsheet, apply one of the following modifications to the base case set of assumptions: Modification 1: Sales growth will be 8% but dividend growth will fall to 3%; the ratio of accounts payable/sales will be 11%,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 38%. +
