Homework #3 Rent Instructions Link the Rent expense on the Income worksheet to the rent figure listed under Assumptions on the Income worksheet for 2015. Use an equation for 2016 that increases the rent by the rate of inflation listed under Assumptions. Database and Regression Analysis - Application in Income Statements The spreadsheet for this homework (Homework3-Spreadsheet.xls) is posted on Blackboard and contains the raw data for this project. This homework contains two parts. Non-cash Expense Part I. Project the Income Statement The non-cash expense is generally depreciation of fixed assets. We will complete this item in Homework 4 once we have completed the projected Balance Sheets in that exercise. Using the first worksheet "Projection" to project the Income Statement for 2015 and 2016 using the assumptions as listed to the right of the table. Other Operating Expenses Part II. Database and Regression Analysis (see sheets "Dataset" and "Income") You are planning on opening a convenience store called Local Yokels and want to project the Income Statements for the years 2015 and 2016. You now need to return to the Dataset worksheet and perform a regression using Other Operating Expenses (column H) as the dependent variable and Net Sales as the independent (X-) variable. The problem here is, again, that some of the companies have zeroes (blanks). You need to manually replace the blanks with zeroes (or you can use the Replace option of Excel). Now you will be able to run a regression. Place the results in cell N350. Again, you'll want to label this output as Other Operating Expenses to distinguish it from the COGS regression we previously performed. Having done some research on traffic patterns and population density in the surrounding neighborhoods, you've made projections of sales as indicated on the worksheet entitled Income of $825,000 in 2015 and $1,250,000 in 2016. Now connect the Other Oper. Expenses on the Income Statements to this regression. Write the equations for Total Oper. Expenses, Operating Income Taxable Income, Taxes (using 40%) and Net Income. We'll fill in the Interest Expense once we've completed the Balance Sheet in Homework 4 (as we will for Depreciation, or Non-Cash Expense). First, you want to perform an Advanced Filter on the dataset to select only those companies with a Business Description (column B) of "Convenience Store. Put the criteria for this filter in cells AD1:AD2 and set the output of the filter to cell A150 (unique records only). Next, you want to reduce the filtered set to those stores with sales of more than $750,000 but less than $2 million. Repeat the Advanced Filter (be sure to change the List Range to its new position beginning in cell A150) by placing the new criteria of Net Sales (column B) in cells AD151:AE152 (you need to use the AND criteria notation) and setting the output to cell A300. Be sure to format the cells and spreadsheet so that everything looks presentable. Cost of Goods Sold (COGS) Now that you've reduced the original dataset to what you consider to be "comparables to the business you anticipate, you need to run a regression (Data - Data Analysis - Regression) using the COGS of the remaining companies (starting in row 301) as the dependent, or Y-variable, against the independent variable, Net Sales (or X-variable). One company has a blank for COGS so you will need to replace this with a zero before you run the regression. Place the output in cell A350. Label the results as COGS so you'll remember what the regression results represent. You should have a regression that is statistically significant as indicated by the F Ratio. Now use the results of the regression to fill in the projected Cost of Goods Sold on the Income Statements on the Income worksheet. Calculate the projected Gross Profit for each year. Part I. Project the Income Statement for 2015 and 2016 using the assumptions to the right. For COGS, use a regression against sales UNLESS the equation as a whole is not significant at the 5% level. If the regression equation as a whole is NOT significant, use the 2014 COGS as a percent of sales to forecast the 2015 - 2016 COGS. 2015 35,639,500 2016 37,055,800 TOTAL SALES LESS: COST OF GOODS SOLD GROSS PROFIT 2010 25,769,000 12,854,999 12,914,001 2011 26,678,080 12,426,650 14,251,430 2012 29,769,999 13,990,999 15,779,000 2013 28,900,492 13,884,898 15,015,594 2014 33,408,312 16,482,927 16,925,385