Information system assignment
zxy0120
instructions.docxREAD THE ENTIRE PROBLEM BEFORE BEGINNING TO DEVELOP THE SPREADSHEET.
Producing correct spreadsheets that implement the specifications in the assignment is, of course, your most important task. But style also matters; you should follow the various good design practices that were introduced in the course and you should format/present the spreadsheets nicely. So, you do NOT have to spend a lot of time or effort making the spreadsheets fancy, but you SHOULD spend some time making sure that the formatting is nice—e.g., consistent and easy to read and understand. Be sure to follow all instructions.
Overview
Complete a seven-year INCOME STATEMENT for the period 2013 through 2019. Use the structure shown at the end of this assignment. Proceed as follows:
1. Take the 2013, 2014, and 2015 values DIRECTLY from the first three columns of the INCOME STATEMENT on page 10 of your Financial Analysis. Do NOT perform any calculations. Just enter the data.
2. Starting with 2016 and beyond, for the following line-items (a thru d below), assume a constant PERCENTAGE growth from one year to the next that is equal to the Average Change you calculated in the rightmost column of the INCOME STATEMENT. In other words, use the average change as the growth rate from 2015 to 2016, from 2016 to 2017, and so forth.
a. Net Sales/Sales Revenue
b. Selling, General, and Administrative (SG&A)
c. Depreciation and Amortization
d. Other Expenses
3. Starting with 2016 and beyond, assume that Advertising will change by the same dollar amount (not the same percentage) from one year to the next. That amount is equal to the Average Annual Change (in dollars) between 2013 and 2015. Note this is NOT the number you reported in the rightmost column of the INCOME STATEMENT, so you will need to calculate it.
4. Starting with 2015 and beyond, assume that Rent Expense will be unchanged (that is, constant) from one year to the next, so the values in 2016 through 2019 will be the same as the 2015 value.
5. Assume that the Cost of Goods Sold (CGS) as a percentage of Net Sales/Sales Revenue (that is, the ratio of CGS to Net Sales) will be constant in years 2015 through 2019 and equal to the percentage in 2015. You will need to calculate that percentage (ratio).
6. Assume that the tax rate will be constant in years 2015 through 2019 and equal to the tax rate in 2015. You will need to calculate that value (that is, the tax expense as a percentage of the EBT).
7. Note that your formulas should allow for the possibility that your company may lose money in any given year (even if that is not the case with the current data).
8. Be sure to note somewhere on the spreadsheet that all figures are in millions.
9. Format financial data with commas (but no decimal places), using dollar signs only for the Net Sales/Sales Revenue, Gross Profit, Total Expenses, Operating Income/Earnings Before Taxes, and Net Income lines. Format growth rates as percentages. Properly format all columns and numbers.
10. Place all growth rates and other input variables at the top left corner of the worksheet. Use formulas and/or functions to perform all necessary calculations.
11. When creating the spreadsheet, be sure to copy cell formulas rather than entering similar formulas many times (for example, you can use the autofill handle to copy cell formulas from year to year).
12. Use Excel to place a footer on your spreadsheet with your last name and section (e.g., Jones—INSY 2299 RWQ—where you substitute your last name for Jones and your section for RWQ).
Be sure to follow these instructions carefully!
