Income Statement ($ in Millions) Best buy Income statement Feb. 02, 2019

Revenue 42879.00

Operating income Cost of goods sold 32918.00

Gross profit 9961.00

Selling, general and administrative expenses 8015.00

Restructuring charges 46.00

Earnings before interests and taxes 1900.00

Other income (expense): Gain on sale of investments 12.00

Investment income and other 49.00

Interest expense -73.00

Earnings before taxes 1888.00

Income tax expense 424.00

Net earnings from continuing operations 1464.00

Gain from discontinued operations (Note 3), net of tax expense of $0, $0 and $7, respectively 0.00 Net earnings 1464.00 Basic 276.40 Diluted 281.40

Make correction, if any, on the graded assignment 3 and rename this assignment file as [your last name]_5_ dataAnalysis.xlxs. This assignment will have you create a version of the selected companys income statement to use and will have you demonstrate your familiarity with A. formula auditing. B. Goal Seek C. Data Table D. Scenario Manager A. Formula Auditing Duplicate the new income statement of the selected firm on a new worksheet. Eliminate any per share calculations at the bottom if any and delete the columns for the earlier two years. Keep only the column for the most recent year. Rename the Tab IncomeStatement(Auditing) Simplify this income statement by combining accounts if necessary, so that the entire income statement is visible on the screen. Do not discard any values; combine them. Use the net income including noncontrolling interest or net income right after the provision for taxes as the net income item for analysis in the subsequent tasks for this assignment. Add a second column for percentages. In that column calculate the costs and expenses, including cost of goods sold, selling and administrative expenses, etc, as a percentage of sales. Calculate the taxes as a percentage of EBT. Leave the interest expense as a constant. Create a third column called With formulas. Recreate the income statement numbers by copying the net sales value (dont use a cell reference; you will type in different numbers later) and using a cell reference for the interest expense. All the other values are determined by formulas using the percentages or are summation formulas for the numbers above. Include appropriate underlines and double underline. If you have created this column correctly, the values should be the same as in the original first column. Be sure to use relative and absolute cell references appropriately, so that copying does not require modification of the formulas in the resulting copy. Create a fourth and fifth column by copying the With formulas column. If you have created the latter column correctly with appropriate relative and absolute cell references (these will be graded), the fourth and fifth columns should look like the first and third. Type in new values for the net sales in these two new columns. The values in the columns, except the interest expense, should all change. Use one value higher than the actual net sales and one value lower. Create a Word report document, named appropriately, with appropriate titles, etc. Copy the table and paste it to the Word document as picture. Trace Dependents: Put the cursor on the Net sales in the first column. Then use Formulas/Formula Auditing/Trace Dependents repeatedly until no more arrows appear. Paste this screen image into the Word document. Use Formulas/Formula Auditing/Remove Arrows to remove the arrows. This auditing works best when examining the results after each step. Trace Precedents: Put the cursor on the cell containing the net income in the With formulas column. Then use Formulas/Formula Auditing/Trace Precedents repeatedly until no more arrows appear. Paste this screen image into the Word document. B. Goal Seek In the second part of the assignment you will conduct a what-if exercise on the selected companys income statement and use the Excel tool called Goal Seek. 1. Make a copy of the selected companys income statement used for the previous section, e.g. section A Auditing. Rename the Tab IncomeStatement(GoalSeek). Label the two columns on the right as What-if and Goal Seek. 3. For the What-if column, change the sales to some number of your choice. The other numbers in the column should change. This is just like you did in the previous section. This will show what the net income would be for a given level of sales. 4. For the Goal Seek column, select Data/Data Tools/What-if Analysis/Goal Seek. In the dialogue box that appears enter the cell reference for Net Income in the far right column for the Set cell, the value to which you want to set the net income (your choice of some round number) in the To value box, and the cell reference for Net Sales in the far right column in the By changing cell box. Before clicking on OK capture the screen image of this Goal Seek dialogue box using Alt/PrtScr (or PrtScr) and paste it into the Word report document. Then click on OK. A new income statement is generated and another dialogue box appears to announce that a solution has been found. Before clicking on OK capture the screen image of this Goal Seek Status dialogue box, and paste it into the Word report document. Then click on OK. The dialogue box disappears, and the new income statement remains. This new income statement shows what level of sales would be needed to reach a given level of net income. Note that the technique assumes that the percentage used in the formulas do not change with different levels of sales, a dubious assumption. 5. Paste a copy of the income statement as an image or a picture into the Word report document. Include the border row and column headings, so the first Goal Seek dialogue box can be understood. Below is the Goal Seek work performed on a selected companys income statement. First dialogue box: Second dialogue box: C. Data Table In the third part of assignment you will conduct a sensitivity analysis on your companys income statement using the Excel data analysis tool called data tables. Modify the income statement from section A by changing the column heading of With Formula to Data Table Formulas and formatting the percents to six decimal places. The three variables are ones you will use in creating data tables for the sensitivity analysis: the PercentCGS from the column of %s , InterestExp from the column of data table formula, and NetIncome from the column of data table formula column. One-Input Data Table. The first data table will calculate how sensitive the net income is to changes in the percent that cost of goods sold is of sales. First, on the same worksheet, create a column of values for the PercentCGS. The initial value for this variable should be smaller than the default value on the income statement. The original value for this PercentCGS should be included in the column series. Change the values by 0.01 (or reasonable value that fits your data set ) but have the values include any digits after that. For example, if the initial value for this variable is 0.574113, then an beginning value for the column series could be 0.524113 and increased by 0.01, the second value will then be 0.534113 and so on. There should be at least 10 data points in this column series. I had demonstrated a way to create a column of values in the associated video. Typing number one by one to create the column is not a good practice. At the top of the next column over and one row higher put in a cell reference to the net income cell. The value of the net income should display. Format this as italic with commas and put a box around it. Highlight the two columns including the cell reference to the net income. Go to Data/What-If Analysis/Data table. In the dialogue box that appears indicate that the Column input cell: refers to the PercentCGS cell. Leave the Row input cell: blank. Click on OK. Excel calculates the value of the net income at all the different inputted values of the PercentCGS. Format the values of the net income to the nearest dollar with commas but no dollar signs. Add appropriate headings to the data table and shade the column of input values as shown here: Note that at .574113 the calculated net income is very close to the net income found in the income statement. This serves as a check feature. Make that number bold red. Two-Input Data Table. The second data table will calculate how sensitive the net income is to changes in the percent that cost of goods sold is of sales and to changes in the interest expense. First on the same worksheet, copy the column of values used in the one-input data table. At the head of the next ten columns put in a series of values that straddle the interest expense. I also showed the process to create a row of values. Pick an appropriate range, not too wide. In the upper left corner of the data table put in a cell reference to the net income cell in the income statement. Format it as before. Highlight all the columns of data. Go to Data/What-If Analysis/Data table. In the dialogue box that appears indicate that the Row input cell: refers to the InterestExp cell and that the Column input cell: refers to the PercentCGS cell. Copy and paste this box to the Word document before hitting OK. Click on OK. Excel calculates the value of the net income at all the combinations of different inputted values of the PercentCGS and InterestExp. Format the values of the net income to the nearest dollar with commas but no dollar signs. Add appropriate headings to the data table and shade the column and row of input values. Note that at .574413 for PercentCGS and 96,434 for InterestExp the calculated net income is very close to the net income found in the income statement. This serves as a check feature. Make that number bold red. The calculated cells contain text that reads like TABLE(G16,E10)}. D. Scenario Manager In the associated lecture video, I conduct a scenario manager analysis for a simple time value of money problem. Your task in this section will be following this process to complete this assignment. In the fourth part of this assignment you will conduct a what-if exercise on your companys income statement using the Excel data analysis tool called Scenario Manager. It also includes naming cells. Cell names are needed to make the summary output more readily understandable. Make a copy of the income statement from worksheet IncomeStatement(GoalSeek) and delete the last two columns (What-If and Goal Seek). Rename the With Formulas column as Scenario Manager. Rename this tab IncomeStatement(Scenario). To name cells go to Formulas/Define Name/Define Name and put the name into the first box. Names of cells cannot contain blanks. You can use underlines as in Net_Sales. Your categories and names may have to differ from mine companys. Consult with me. Go to Data/What-if Analysis/Scenario Manager. That brings up the following dialogue box: Click on Add. Give the scenario the name Boom. Using the Control key click on the cells corresponding to NetSales, PercentCGS, and InterestExp. Click on OK. That brings up another dialogue box. Enter different values for the three values that are already there. Make the new values based on an expectation of booming economy. Click on OK. Click on Add and repeat the process to create a scenario for a recessionary economy. Click on Summary. That brings up another dialogue box. Select cells corresponding to EBIT and NetIncome. That gives: Click on OK. A summary table is generated on a new worksheet. The summary table should show your original numbers (Current Values) and the results of your two scenarios. In this part of your word document, a) Add Scenario dialogue box,so I can see what cells you chose as the Changing cells. Make the image big enough that the cell numbers are easy to read. b) Three Scenario Values screens: the original values and the two added scenarios: Examples as below: c) Scenario Summary dialogue box screen: d) Scenario Summary table: Add your scenario Summary table. Name the excel file [your name]_5_dataAnalysis.xlsx and word document [your name]_dataAnalysis.docx. Submit both the spreadsheet file and the Word document report to the D2L dropbox after performing the exercises indicated above.