, 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).

a.Compute the variance amount.Favorable variances should be shown with parentheses.

b.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.

a.Calculate the budget amounts for each of the cost components based on the cost formula amounts.

b.Compute the variance amount.Favorable variances should be shown with parentheses.

c.Indicate whether the variance is Favorable - F or Unfavorable - U.

3)Answer the two questions at the bottom.

'

AutoSave Off v Budgeting_Project_Part 2_Flex_Budget.xlsx - Excel O Search nathan thompkins NT X File Home Insert Page Layout Formulas Data Review View Help Share Comments A1 X V fx Before-the-Fact Flexible Budget A B C D E F G H J K L M N O P Budget 2 12 points Production Level in Units Cost Formula 8,000 12,000 16,000 UT 6 Direct Materials $ 360,000 7 Direct Labor 60,000 00 9 Variable Overhead: 10 Supplies 132,000 11 Power 72,000 12 Repairs 36,000 13 Total Variable Overhead 240,000 14 15 Fixed Overhead: 16 Depreciation 18,000 17 Salaries 115,000 18 Rent 53,000 19 Total Fixed Overhead 186,000 20 21 Total Product Cost $ 846,000 22 Unit Product Cost S 70.50 23 24 25 26 Before Flex Budget Performance Report + Ready + 35% Type here to search W X 8:12 PM 4/15/2021Budgeting_ProJect_Part_2_Fle Budgetxlsx , Excel , Nathan thornpklns File Home Insert Page Layout Formulas Data Review View Help 13 Share 5' Comments R18 v s x v fx v A A 1 B 1 c D ,E,F1 G 1H I J K 'LiM N OiP 01A 1 7 Performance Report, Actual Results vs. Budget 27 14 points L Performance Report based on Static Budget Performance Report based on Actual Level of Activity 1L Actual Results Static Budget Variance F/ U Actual Results Flexible Budget Variance Wu 5 788 of Units Produced 11,000 12,000 Cost Formula 11,000 11,000 6 L Direct Materials $ 338,000 $ 338,000 87 Direct Labor 55,000 55,000 9 , 107Variable Overhead: 1L Supplies 125,000 125,000 1; Power 68,000 68,000 13 Repairs 28,000 28,000 1i Total Variable Overhead 221,000 221,000 157 167 Fixed Overhead: 177 Depreciation 21,000 21,000 11 Salaries 113,000 113,000 19 Rent 53,000 53,000 207 Total Fixed Overhead 187,000 187,000 21 22 Total Product Costs $ 801,000 $ 801,000 23 24 I Questions: 25 1. Did the company do a good job oi controlling costs? On what do you base your answer? 29 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? 4 > | Before Flex Budget Performance Report 6-) E 4 r Ir| , 4'474r 85% 8:11 PM 0' A E a :10) 4/15/2021 5) i Ready I ,0 Type here to search o 19.2