Question: I just need to reference numbers for the calculations how to send my question with excel attachment ? Use with Excel The TV Corporation manufactures
I just need to reference numbers for the calculations
how to send my question with excel attachment ?
| Use with Excel | |||||||||||||
| 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 | Basic | Deluxe | Total | ||||||||||
| Number units Sold | 60,000 | 40,000 | 100,000 | ||||||||||
| Total Contribution Margin | $3,480,000 | $3,444,000 | $6,924,000 | ||||||||||
| Budgeted CM per unit | $58.00 | $86.10 | |||||||||||
| Actual Results | |||||||||||||
| Number units sold | 59,850 | 45,150 | 105,000 | ||||||||||
| Total Contribution Margin | $3,650,850 | $3,612,000 | $7,262,850 | ||||||||||
| But, the actual industry volume was | 300,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 | 310,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 worksheet. | Look 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 | |||||||||||||
| f. Compute 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. | |||||||||||||
| h. Compute the sales-quantity variance by type of machine and total | |||||||||||||
| 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 | |||||||||||||
| j. Compute the market-size variance. | 4 | ||||||||||||
| 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. | |||||||||||||
| 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 Problem 2 worksheet as your data for cell references. | ||||||
| I will take off 5 pts. if you have not used cell references from 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. | ||||||
| Also, make sure you are using ABS function, since variances should not be positive and negative. | ||||||
| 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. | $338,850 | 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 | Total | ||||
| Budgeted contribution margin per unit | $58 | $86 | Do not Total | |||
| Actual Number of units sold | Do not Total | |||||
| Flexible -Budget Contribution Margin | ||||||
| c. Calculate the flexible budget variance. | ||||||
| d. Calculate the sales-volume variance. | ||||||
| Check Figure: static budget variance=flexible budget | ||||||
| variance+Sales volume Variance | ||||||
| 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 Units of All products sold | (Actual sales-mix%-Budgeted Sales Mix% | Budgeted Contribution 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 | ||||||
| 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 | ||||||
| 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 these variances on income. | ||||||
Step by Step Solution
There are 3 Steps involved in it
Get step-by-step solutions from verified subject matter experts
Study Help