Please review document and complete, do not hesitate to ask for additional info if needed

Project 2 Due June 28th by 6 PM CT submit in step 2 of this assignment area. You will be graded based on the accuracy of your answer and the usage of excel, which includes using cell references from this page and the IF and SUMIF functions. Save your file using your first initial and last name. If you have questions, please post them to the discussion board. Make sure you review pages 569-577. Also, I have a lot of support material in the Chapter 14 folder in the week 2 assignment. The author's problem for self-study starting on page 576 is very good. The TV Corporation manufactures 2 types of TVs. The Basic TV and the Deluxe TV. Budgeted and actual annual operating data are as follows: Static Budget Number units Sold Total Contribution Margin Budgeted CM per unit Basic Deluxe Total 60,000 40,000 100,000 $10,560,000 $3,440,000 $14,000,000 $176.00 $86.00 Actual Results Number units sold Total Contribution Margin 59,850 $10,713,150 45,150 105,000 $3,612,000 $14,325,150 But, the actual industry volume was 319,000 Units Prior to the beginning of the year, a consulting firm estimated the total volume for volume of the Basic and Deluxe industry category to be 318,000 Units Required: Calculate the following information and variances on the worksheet entitled analysis. I have tried to give some helpful hints. Use the contribution margin approach in the following sales variance analysis that follows. a. Calculate the Static-budget variance. I have tried to set up a model to use on the analysis worksheetLook at Exhibit 14-11 p.571 b. Calculate the contribution margin for the flexible budget. c. Calculate the flexible budget variance. d. Calculate the sales-volume variance. e. Compute the actual sales-mix (look at the calculations on page 572-for actual sales mix of wholesale channel 100,000/150,000=66.67%) f. Compute the budgeted sales-mix (look at the calculations on page 570 - for budgeted wholesale channel 93,000/155,000=60%) g . Compute the sales-mix variance for each product and then the total sales-mix variance like the table shown on page 572. I have set up the table for you to fill in. h. Compute the sales-quantity variance by type of machine and total (review pages 572-573--all products will either be a F or U-- you cannot have both if total actual quantity is greater than total budgeted units the sales-quantity will always be F and the opposite will occur when actual is less than budgeted). i. Compute the market-share variance Reference page 574 j. Compute the market-size variance. Reference page 574 k. Comment on the results of the above variance analysis. Make sure your comments identify specific variances and the impact of these variances on income. Name: Type name here Do not forget to use the IF function to determine if the variance is favorable or unfavorable. I used a formula approach like the author did and I expect everyone to use cell references and use the Project 2 as your data for cell references. I will take off 5 pts. if you have not used cell references and the problem 2 worksheet as your reference for the calculations. I will take off 3 pts. for not using IF statements or 1/2 each. When you are adding multiple variances, either use the SUMIF function or a nested IF. Worth 1/2 each. Also, make sure you are using ABS function, since variances should not be positive and negative. Worth 1/2 pts. Each or total of 2 pts. Calculations are worth 1/2 pt. each. I have color coded some areas that should match, since you can use these variances to check your work since they should equal each other. Comments in "K" are worth 1 pt. You need to clearly identify multiple sales variance you have calculated and the impact in income. a. Calculate the Static-budget variance. $325,150 F Static Budgeted Variance =Actual total contribution margin less Static total Contribution margin Look at level 1 in Panel C in exhibit 14-11. b. Calculate the contribution margin for the flexible budget. Basic Budgeted contribution margin per unit Actual Number of units sold Flexible -Budget Contribution Margin Deluxe Guidance: Please look at my cell referencing, since I wanted to get you off on the right track. Total Leave Blank Leave Blank Guidance: You need to compute the Budgeted Contribution per unit, which is the same contribution amount used in the static budget. You could also just reference the budgeted CM given on the Project 2 worksheet Guidance: FB Variance is Actual CM-Flexible Budgeted CM, since Actual is less than budgeted the variance is unfavorable. c. Calculate the flexible budget variance. Guidance: Sales-Volume Variance is Flexible Budget less Static Budget. This variance is F since the Flexible Budget is greater than static budget. d. Calculate the sales-volume variance. Check Figure: static budget variance=flexible budget variance+Sales volume Variance Guidance: Used nested IF since just 2 variances combining, so thought this was easier, but can use SUMIF if you want. Compute the sales-mix variance by type of Product and total. First Basic Deluxe e. Calculate the Actual sales mix: f. Calculate the Budgeted sales mix: g . Compute the sales-mix variance for each product and then the total sales-mix variance like the table shown on page 572. I have set up the table for you to fill in. (Actual sales-mix%- Budgeted Actual Units of All Budgeted Sales Mix Contribution products sold % Margin per unit Sales-Mix Variance Basic Deluxe g. Total Sales mix variance Compute the sales-quantity variance by type of product and total. Basic Deluxe h. Total Sales quantity variance Guidance: The sales quantity variance will always be F for all products when actual units sold are greater than budgeted units. Check Figure: Sales-volume variance=Sales mix variance+Sales Quantity Variance Calculate the Actual market share: Calculate the Budgeted market share: Calculate the budgeted contribution margin per composite unit of budgeted mix Lastly: I. Market-share Variance Guidance: The CM per composite unit is the average budgeted CM, so take static budget/budgeted units. j. Compute the market-size variance. Check Figure: Sales-quantity variance=Market-share variance+Market size Variance k. Comment on the results of the above variance analysis. Make sure your comments identify specific variances and the impact of Guidance: For the market share variance look at page 524. The market variances are looking at the market industry, so you should not break down these variances on income. into products. Project 2 Due June 28th by 6 PM CT submit in step 2 of this assignment area. You will be graded based on the accuracy of your answer and the usage of excel, which includes using cell references from this page and the IF and SUMIF functions. Save your file using your first initial and last name. If you have questions, please post them to the discussion board. Make sure you review pages 569-577. Also, I have a lot of support material in the Chapter 14 folder in the week 2 assignment. The author's problem for self-study starting on page 576 is very good. The TV Corporation manufactures 2 types of TVs. The Basic TV and the Deluxe TV. Budgeted and actual annual operating data are as follows: Static Budget Number units Sold Total Contribution Margin Budgeted CM per unit Basic Deluxe Total 60,000 40,000 100,000 $10,560,000 $3,440,000 $14,000,000 $176.00 $86.00 Actual Results Number units sold Total Contribution Margin 59,850 $10,713,150 45,150 105,000 $3,612,000 $14,325,150 But, the actual industry volume was 319,000 Units Prior to the beginning of the year, a consulting firm estimated the total volume for volume of the Basic and Deluxe industry category to be 318,000 Units Required: Calculate the following information and variances on the worksheet entitled analysis. I have tried to give some helpful hints. Use the contribution margin approach in the following sales variance analysis that follows. a. Calculate the Static-budget variance. I have tried to set up a model to use on the analysis worksheetLook at Exhibit 14-11 p.571 b. Calculate the contribution margin for the flexible budget. c. Calculate the flexible budget variance. d. Calculate the sales-volume variance. e. Compute the actual sales-mix (look at the calculations on page 572-for actual sales mix of wholesale channel 100,000/150,000=66.67%) f. Compute the budgeted sales-mix (look at the calculations on page 570 - for budgeted wholesale channel 93,000/155,000=60%) g . Compute the sales-mix variance for each product and then the total sales-mix variance like the table shown on page 572. I have set up the table for you to fill in. h. Compute the sales-quantity variance by type of machine and total (review pages 572-573--all products will either be a F or U-- you cannot have both if total actual quantity is greater than total budgeted units the sales-quantity will always be F and the opposite will occur when actual is less than budgeted). i. Compute the market-share variance Reference page 574 j. Compute the market-size variance. Reference page 574 k. Comment on the results of the above variance analysis. Make sure your comments identify specific variances and the impact of these variances on income. Name: Type name here Do not forget to use the IF function to determine if the variance is favorable or unfavorable. I used a formula approach like the author did and I expect everyone to use cell references and use the Project 2 as your data for cell references. I will take off 5 pts. if you have not used cell references and the problem 2 worksheet as your reference for the calculations. I will take off 3 pts. for not using IF statements or 1/2 each. When you are adding multiple variances, either use the SUMIF function or a nested IF. Worth 1/2 each. Also, make sure you are using ABS function, since variances should not be positive and negative. Worth 1/2 pts. Each or total of 2 pts. Calculations are worth 1/2 pt. each. I have color coded some areas that should match, since you can use these variances to check your work since they should equal each other. Comments in "K" are worth 1 pt. You need to clearly identify multiple sales variance you have calculated and the impact in income. a. Calculate the Static-budget variance. $325,150 F Static Budgeted Variance =Actual total contribution margin less Static total Contribution margin Look at level 1 in Panel C in exhibit 14-11. b. Calculate the contribution margin for the flexible budget. Basic Deluxe Budgeted contribution margin per unit $176 Actual Number of units sold 59,850 Flexible -Budget Contribution Margin $10,533,600 Guidance: Please look at my cell referencing, since I wanted to get you off on the right track. Total $86 Leave Blank 45,150 Leave Blank $3,882,900 $14,416,500 c. Calculate the flexible budget variance. $179,550 ($270,900) d. Calculate the sales-volume variance. ($26,400) $442,900 Check Figure: static budget variance=flexible budget variance+Sales volume Variance $153,150 $172,000 Guidance: FB Variance is Actual CM-Flexible Budgeted Guidance: CM, since Actual is less than budgeted the FB Variance is Actual CM-Flexiblevariance Budgeted is unfavorable. ($91,350) CM, since Actual is less than budgeted the variance is unfavorable. Guidance: Guidance: Sales-Volume Variance is Flexible Sales-Volume Variance is Flexible Budget Budget less Static Budget. This less Static Budget. This variance is F variance is F since the Flexible Budget since the Flexible Budget is greater than is greater than static budget. static budget. Compute the sales-mix variance by type of Product and total. First Basic Deluxe e. Calculate the Actual sales mix: 59850 45150 f. Calculate the Budgeted sales mix: 63000 42000 g . Compute the sales-mix variance for each product and then the total sales-mix variance like the table shown on page 572. I have set up the table for you to fill in. (Actual sales-mix%- Budgeted Actual Units of All Budgeted Sales Mix Contribution products sold % Margin per unit Sales-Mix Variance Basic 59,850 -3150 176 (554,400) Deluxe 45,150 3150 86 270,900 Guidance: Please look at my cell referencing, g. Total Sales mix variance (283,500) U since I wanted to get you off on the right track. Compute the sales-quantity variance by type of product and total. Basic ($26,400) Deluxe $442,900 h. Total Sales quantity variance $416,500 F Guidance: Please look at my cell referencing, Guidance: since I wanted to get you off on the The sales quantity variance will always be right track. F for all products when actual units sold are greater than budgeted units. Check Figure: Sales-volume variance=Sales mix variance+Sales Quantity Variance Calculate the Actual market share: Calculate the Budgeted market share: Calculate the budgeted contribution margin per composite unit of budgeted mix Lastly: I. Market-share Variance 32.92% 31.45% $140 $655,975 Guidance: The CM per composite unit is the average budgeted CM, so take static budget/budgeted units. j. Compute the market-size variance. $44,025 Check Figure: Sales-quantity variance=Market-share variance+Market size Variance Guidance: Please look at my cell referencing, $700,000 F since I wanted to get you off on the k. Comment on the results of the above variance analysis. Make sure your comments identify specific variancesright andtrack. the impact of these variances on income. Flexible budget variance is unfavorable $91,350 and sales volume variance is favorable $416,500 indicating that actual volume is higher than budgeted volume of sales. Sales mix variance is unfavorable $283,500 indicates that product having higher margin was proportionately lower than product having lower margin in sales mix. Market size and share variance is favorable due to increase in actual sales higher than proportionate increase in total volume of market