Wagner Printers performs all types of printing, including custom work, such as advertising displays, and standard work, such as business cards. Market prices exist for standard work, and Wagner Printers must match or better these prices to get the business. The key issue is whether the existing market price covers the cost associated with doing the work. On the other hand, most of the custom work must be priced individually. Because all custom work is done on a job order basis, Wagner routinely keeps track of all the direct labour and direct materials costs associated with each job. However, the overhead for each job must be estimated. The overhead is applied to each job using a predetermined (normalized) rate based on estimated overhead and labour hours. In the past, the normalized rate for overhead has been computed by using the historical average of overhead per direct labour hour. Wagner has become increasingly concerned about this practice for two reasons. First, it has not produced accurate forecasts of overhead in the past. Second, technology has changed the printing process, so that the labour content of jobs has been decreasing, and the normalized rate of overhead per direct labour hour has steadily been increasing. The file (Wagner printers) shows the overhead data that Wagner has collected for its shop for the past 52 weeks. The file also shows the total labour hours and machine hours in each week. Assume that the normalized rate for overhead that will be used in the upcoming week is about $90 per direct labour hour. 1. Use correlation matrix and examine the relationship between labour hours, machine hours and overhead cost. Explain briefly. Use regression analysis in Excel (use 95% confidence level) and find a multiple regression equation to estimate overhead cost. Assess the model, you should explain the validity of the model, the fit of the model and assessing the significance of coefficients. (You do not need to create the residual plots) 2. Wagner is now preparing a bid for an important order that may involve a considerable amount of repeat business. The estimated requirements for this new project are shown in the table below:

New job

Total Direct labour cost $150

Direct material cost. $750

Labour hours. 15hrs

Machine hours. 8hrs

Using the existing approach to cost estimation, Wagner used below formula and has estimated the total cost for this job as $2,250 =150+750+ (90*15). Use the regression model from part 1, what is the total cost for this job? Compare your total cost with Wagners value and explain in few sentences what does it mean for the company. Attention: Since the data in the file is for total jobs per week, to find the overhead cost for this job in the regression equation, replace 1.5% of the intercept value. Use coefficients of Labour hours and Machine hours as usual.