In the file Budgeting Project Part 2 Flex Budget, complete the information on the two tabs.

First Tab: Before Flex Budget (12 points)

The company is doing their planning for the coming year and has developed a production cost budget for 12,000 units, and they also want to figure out a budget for other potential production levels.

Required:

1. Compute the cost formula for each of the cost elements.
2. Calculate the before-the-fact flexible budget amounts for 8,000 units and 16,000 units (which are both in the relevant range of output). Use excel formulas based on the cost formulas to determine the budgeted amounts.

Second Tab: Performance Report (24 points)

Assume that at the beginning of the year, the company settles on the production cost budget for 12,000 units as the most likely scenario and includes that in their master budget.

At the end of the year, actual units produced for the year were 11,000. Actual production costs are as shown on the spreadsheet.

Required:

1. Prepare a performance report comparing actual results to the static budget (budget at 12,000 units).
   1. Compute the variance amount. Favorable variances should be shown with parentheses.
   2. Indicate whether the variance is Favorable F or Unfavorable U.
2. Prepare a performance report comparing actual results to a budget at the actual level of activity.
   1. Calculate the budget amounts for each of the cost components based on the cost formula amounts.
   2. Compute the variance amount. Favorable variances should be shown with parentheses.
   3. Indicate whether the variance is Favorable F or Unfavorable U.
3. Answer the two questions at the bottom.

AutoSave On H HCl Budgeting Project_Part 2 Flex_Budget.xlsx - Excel Search nathan thompkins NT File Home Insert Page Layout Formulas Data Review View Help Share Comments R18 x E G H M N 0 B C D 1 Performance Report, Actual Results vs. Budget 7 24 points 3 Pertormance Report based on Static Budert 4 Actual Results Static Budget Variance 5 # of Units Produced 11,000 12,000 F/U Performance Report based on Actual level of Activity Actual Results Flexible Budget Variance 11,000 11,000 F/U Cost Formula $ $ 338,000 55,000 338,000 55,000 7 Direct Materials 8 Direct Labor 9 10 Variable Overhead: Supplies 12 Power 13 Repairs 14 Total Variable Overhead 125,000 68,000 28,000 221,000 125,000 68,000 28,000 221,000 21,000 113,000 53,000 187,000 16 Fixed Overhead: Depreciation 21,000 18 Salaries 113,000 19 Rent 53,000 20 Total Fixed Overhead 187,000 21 22 Total Product Costs $ 801,000 23 24 Questions: 25 1. Did the company do a good job of controlling costs? On what do you base your answer? 26 22 801,000 79 2. How much of the total variance vs. the static budget is due to lower activity and how much is due to good/poor cost control? 31 Before Flex Budget Performance Report + Ready 1 a5% H Type here to search O EI W 100) 8:11 PM 4/15/2021 AutoSave CH Hw Budgeting Project_Part_2 Flex_Budget.xlsx - Excel Search nathan thompkins NT File Home Insert Page Layout Formulas Data Review View Help Share Comments A1 X fy Before-the-Fact Flexible Budget D E F . 1 J K L N Production Level in Units 8,000 12,000 16,000 $360,000 60,000 132,000 72,000 36,000 240,000 A B 1 Budget 2 12 points 3 4 Cost Formula 5 6 Direct Materials 7 Direct Labor 8 9 Variable Overhead: 10 Supplies 14 11 Power 12 12 Repairs 13 Total Variable Overhead 14 15 15 Fixed Overhead: 16 16 Depreciation 17 Salaries 18 Rent 19 Total Fixed Overhead 20 21 Total Product Cost 22 Unit Product Cost 23 24 25 26 Before Flex Budget Performance Report Ready 18,000 115,000 53,000 186,000 $ 846,000 $ 70.50 + a5% H Type here to search EI W 21:0) 8:12 PM 4/15/2021 AutoSave On H HCl Budgeting Project_Part 2 Flex_Budget.xlsx - Excel Search nathan thompkins NT File Home Insert Page Layout Formulas Data Review View Help Share Comments R18 x E G H M N 0 B C D 1 Performance Report, Actual Results vs. Budget 7 24 points 3 Pertormance Report based on Static Budert 4 Actual Results Static Budget Variance 5 # of Units Produced 11,000 12,000 F/U Performance Report based on Actual level of Activity Actual Results Flexible Budget Variance 11,000 11,000 F/U Cost Formula $ $ 338,000 55,000 338,000 55,000 7 Direct Materials 8 Direct Labor 9 10 Variable Overhead: Supplies 12 Power 13 Repairs 14 Total Variable Overhead 125,000 68,000 28,000 221,000 125,000 68,000 28,000 221,000 21,000 113,000 53,000 187,000 16 Fixed Overhead: Depreciation 21,000 18 Salaries 113,000 19 Rent 53,000 20 Total Fixed Overhead 187,000 21 22 Total Product Costs $ 801,000 23 24 Questions: 25 1. Did the company do a good job of controlling costs? On what do you base your answer? 26 22 801,000 79 2. How much of the total variance vs. the static budget is due to lower activity and how much is due to good/poor cost control? 31 Before Flex Budget Performance Report + Ready 1 a5% H Type here to search O EI W 100) 8:11 PM 4/15/2021 AutoSave On H HCl Budgeting Project_Part 2 Flex_Budget.xlsx - Excel Search nathan thompkins NT File Home Insert Page Layout Formulas Data Review View Help Share Comments R18 x E G H M N 0 B C D 1 Performance Report, Actual Results vs. Budget 7 24 points 3 Pertormance Report based on Static Budert 4 Actual Results Static Budget Variance 5 # of Units Produced 11,000 12,000 F/U Performance Report based on Actual level of Activity Actual Results Flexible Budget Variance 11,000 11,000 F/U Cost Formula $ $ 338,000 55,000 338,000 55,000 7 Direct Materials 8 Direct Labor 9 10 Variable Overhead: Supplies 12 Power 13 Repairs 14 Total Variable Overhead 125,000 68,000 28,000 221,000 125,000 68,000 28,000 221,000 21,000 113,000 53,000 187,000 16 Fixed Overhead: Depreciation 21,000 18 Salaries 113,000 19 Rent 53,000 20 Total Fixed Overhead 187,000 21 22 Total Product Costs $ 801,000 23 24 Questions: 25 1. Did the company do a good job of controlling costs? On what do you base your answer? 26 22 801,000 79 2. How much of the total variance vs. the static budget is due to lower activity and how much is due to good/poor cost control? 31 Before Flex Budget Performance Report + Ready 1 a5% H Type here to search O EI W 100) 8:11 PM 4/15/2021 AutoSave CH Hw Budgeting Project_Part_2 Flex_Budget.xlsx - Excel Search nathan thompkins NT File Home Insert Page Layout Formulas Data Review View Help Share Comments A1 X fy Before-the-Fact Flexible Budget D E F . 1 J K L N Production Level in Units 8,000 12,000 16,000 $360,000 60,000 132,000 72,000 36,000 240,000 A B 1 Budget 2 12 points 3 4 Cost Formula 5 6 Direct Materials 7 Direct Labor 8 9 Variable Overhead: 10 Supplies 14 11 Power 12 12 Repairs 13 Total Variable Overhead 14 15 15 Fixed Overhead: 16 16 Depreciation 17 Salaries 18 Rent 19 Total Fixed Overhead 20 21 Total Product Cost 22 Unit Product Cost 23 24 25 26 Before Flex Budget Performance Report Ready 18,000 115,000 53,000 186,000 $ 846,000 $ 70.50 + a5% H Type here to search EI W 21:0) 8:12 PM 4/15/2021 AutoSave On H HCl Budgeting Project_Part 2 Flex_Budget.xlsx - Excel Search nathan thompkins NT File Home Insert Page Layout Formulas Data Review View Help Share Comments R18 x E G H M N 0 B C D 1 Performance Report, Actual Results vs. Budget 7 24 points 3 Pertormance Report based on Static Budert 4 Actual Results Static Budget Variance 5 # of Units Produced 11,000 12,000 F/U Performance Report based on Actual level of Activity Actual Results Flexible Budget Variance 11,000 11,000 F/U Cost Formula $ $ 338,000 55,000 338,000 55,000 7 Direct Materials 8 Direct Labor 9 10 Variable Overhead: Supplies 12 Power 13 Repairs 14 Total Variable Overhead 125,000 68,000 28,000 221,000 125,000 68,000 28,000 221,000 21,000 113,000 53,000 187,000 16 Fixed Overhead: Depreciation 21,000 18 Salaries 113,000 19 Rent 53,000 20 Total Fixed Overhead 187,000 21 22 Total Product Costs $ 801,000 23 24 Questions: 25 1. Did the company do a good job of controlling costs? On what do you base your answer? 26 22 801,000 79 2. How much of the total variance vs. the static budget is due to lower activity and how much is due to good/poor cost control? 31 Before Flex Budget Performance Report + Ready 1 a5% H Type here to search O EI W 100) 8:11 PM 4/15/2021