Homework answers / question archive / Assume today's date is 1/1/2018

Assume today's date is 1/1/2018

Accounting

Share With

Assume today's date is 1/1/2018. The CLD Forecast tab contains a projected income statement and balance sheet for Cloud Peak Energy (ticker symbol: CLD), as well as the most recent year's data for those two statements.

Note a difference in this week's assignment from previous weeks: there are no blue-shaded cells for your answers. This is because part of this week's material is about formatting the worksheet and creating new rows and columns, so knowing where to put your answers (and sometimes how to create the cells which will hold those answers) are the skills you are demonstrating in this assignment. Always use formulas with cell references when possible, except for model inputs. 18 points of this week's assignment are for content and 12 points are for formatting.

• Required this week: all formulas/functions should be typed in the first forecasted column in such a way that they can be copied across to the rest of the forecasted years. Where appropriate, set formulas that will first check for a hard-coded dollar amount input and if it is not there will then calculate the forecast number based on assumptions (as demonstrated in this week's online material).
• Be sure to apply the Input cell style to all appropriate cells, whether the directions expressly state it or not (some will, some won't).
• Expenses are to be input/displayed as positive numbers.
• Be sure that percentages are displayed as percentages.
• Note: some of the assumptions you are putting in are different from the ones used to calculate the numbers appearing initially, so don't be put off by the inconsistency.  

This assignment is worth a total of 30 points.

1. (2 points) Create a new worksheet called Assumptions that is a copy of the CLD Forecast worksheet. Situate it between the Questions worksheet and the CLD Forecast worksheet.

In the Assumptions worksheet:

2. (4 points) Rearrange the spreadsheet so that there are three rows assigned to Revenue assumptions and populate them as was demonstrated in the Week 6 online material (except you do not have enough data to calculate the historical growth rate). Fill in the following assumed growth rates for years 2018-2022, respectively:

              -7.91%, -3.09%, -1.57%, 0.05%, 1.08%

3. (4 points) Leave an empty row after Revenue. Rearrange the spreadsheet so that there are three rows assigned to Cost of Revenue assumptions and populate them as was demonstrated in the Week 6 online material. Fill in 80.82% as the percent of revenue assumption for all forecast years.
4. (2 points) Adjust the Gross Profit formula so it still calculates Gross Profit.
5. (4 points) Leave an empty row after Gross Profit. Rearrange the spreadsheet so that there are three rows assigned to Sales, General & Administrative assumptions and populate them as was demonstrated in the Week 6 online material. For the percent of revenue assumptions, fill in 5.1% for 2018, 3.9% for 2019 and 3.5% for the remaining years.
6. (2 points) Leave an empty row after Sales, General & Administrative. Format the Depreciation & Amortization row with the Input cell style where appropriate. Leave the dollar values as hard-coded assumptions.
7. (4 points) Leave an empty row after Depreciation and amortization. Do not put spacer rows between the different types of interest expense. Input the following interest rate assumptions for each forecast year:

              Interest: Excess cash (from balancing): 1.7%

              Interest: Revolver (from balancing): 3.2%

              Interest expense: 4.1%

8. (2 points) Leave an empty row after Interest Expense. Format the Other Operating Expenses row with the Input cell style where appropriate. Leave the dollar values as hard-coded assumptions.
9. (1 point) Delete Income before income taxes and remove the top border.
10. (2 points) Leave an empty row after Other operating expenses. For Provision for income taxes, input the assumed tax rate of 26% in each forecast year.
11. (2 points) Leave an empty row after Provision for income taxes. Format the Other income row with the Input cell style where appropriate. Leave the dollar values as hard-coded assumptions.
12. (1 point) Delete Net Income and remove all borders and formatting from that row.