Months expense income shares 1 145 125 -4.4 2 80 85 -3.0 3 105 100 -2.0 4 80 95 1.1 5 155 120 1.5 6 85 100 3.5 7 95 140 5. 2 8 90 115 5.4 9 93 105 6.9 10 170 150 9.4 11 100 130 12. O 12 82 110 16.9 Q3. (40 pts) Multiple Regression. A startup software company has indicated its expenses and income for the first 12 months as follows. The income consists of several items such as donations from crowd sourcing, government subsidized payments and some sales returns from codes that have not yet been packaged as products. The difference between the income and expenses are being distributed to the shareholders somewhat proportionally as shares. Accounting for the shares is not easy, because the number of shareholders is not stable since this is a startup company. On the months when income is less than expenses, the shareholders make contributions, so that the company survives the monthly losses. Under such an unpredictable situation, one of the shareholders have generated his/her own share chart, based on data from the company and his/her own monthly shares. Answer the questions below based on the data provided in Excel worksheet 23-data. a) 5 pts Run a multiple regression with the excel solver to estimate the shares of the shareholder (save in worksheet 23-a). Write the equation of the linear model in the provided Word template. b) 5 pts How significant is this model? On the template Word document, report generally based on the important parameters and also for each independent random variable. Copy and paste evidence from the regression report of excel. c) 10 pts Generate 2 separate scatter plots with shares on the y axis and each independent random variable (i.e. income and expense) on the x axis. Copy the graphs to the Word template. d) 10 pts Do you observe that one of the random variables need a log transform? If yes which one? Indicate on the word template. In a new worksheet named Q3-d, Log transform the random variable which would benefit transformation, and rearrange your variables. (i.e if the variable is x, to transform it use: log(x,10) in excel). The company has larger donations on months 10, 11, 12 as the year is ending because donations can be deducted from tax. Add a new column as a dummy variable to indicate this. Copy this new data table from excel to the word template below to receive credit for this part. e) 5 pts Run a multiple regression with the excel solver to estimate the shares of the shareholder (save in worksheet 23-e). Write the equation of the new linear model in the provided Word template. f) 5 pts How significant is this new model? On the template Word document, report generally based on the important parameters and also for each independent random variable. Copy and paste evidence from the regression report of excel
