Flexiable budgets and performance analysis

profilepulzx2d4
81554_1416975_Ch.9ExcelwithHWCanvasFlexBudget.xlsx
Home>Business & Finance homework help>Accounting homework help>Flexiable budgets and performance analysis

Ch.9 Excel

STATIC Budget 1
For the Period Ended June 30
Planning òSell Price Each
Budget $ 75.00 Reminder Y = a + bX
Wages and salaries
Number of units (Q) 500 Characteristics bX
Fixed Variable each Basis
Revenue $ 37,500 $ - 0 $ 75 units sold Y = a + b X
Expenses: ê ê ê ê
Wages and salaries $ 20,000 $ 5,000 $ 30 units sold $ 20,000 $ 5,000 500 $30
By Gasoline and supplies 4,500 $ - 0 $ 9 units sold $ 4,500 $ - 0 500 $9
Natural Equipment maintenance 1,500 $ - 0 $ 3 units sold $ 1,500 $ - 0 500 $3
Expense Office and shop utilities 1,000 $ 1,000 $ - 0
in Office and shop rent 2,000 $ 2,000 $ - 0
this Equipment Depreciation 2,500 $ 2,500 $ - 0
Example Insurance 1,000 $ 1,000 $ - 0
Total expenses 32,500
Net operating income $ 5,000
ACTUAL 1
For the Period Ended June 30
Actual
Results
Number of units Driver 550 Actual Not
$78.18 $75.00
Revenue $ 43,000 SP Each
Expenses:
Wages and salaries $ 23,500 given from Financials
Gasoline and supplies 5,100 given from Financials
Equipment maintenance 1,300 given from Financials
Office and shop utilities 950 given from Financials
Office and shop rent 2,000 given from Financials
Equipment Depreciation 2,500 given from Financials
Insurance 1,200 given from Financials
Total expenses 36,550
Net operating income $ 6,450
Variance from Budget 1
Favorable Sales [units or price each], Revenue Increased
Expenses Costs decrease
Unfavorable: Sales decrease
Expenses/costs increase
Actual V. Static 1
Total
For the Period Ended June 30 Differences
Planning Actual
Budget Results Variances
F=favorable
U= Unfav
Number of units (Q) 500 550 50 F
Revenue $ 37,500 $ 43,000 $ 5,500 F
Expenses:
Wages and salaries $ 20,000 $ 23,500 $ 3,500 U
Gasoline and supplies 4,500 5,100 600 U
Equipment maintenance 1,500 1,300 200 F
Office and shop utilities 1,000 950 50 F
Office and shop rent 2,000 2,000 - 0
Equipment Depreciation 2,500 2,500 - 0
Insurance 1,000 1,200 200 U
Total expenses 32,500 36,550 4,050 U
Net operating income $ 5,000 $ 6,450 $ 1,450 F
DO ALL "Unfavorable" indicate poor performance
NO 1
PPT
STATIC v. FLEX Budget
For the Period Ended June 30 2
Single Driver = Units Sold STATIC FLEX'd
STATIC Planning Flexible
Planning Budget Budget
sell each
Number of units (Q) $ 75 500 550 Budget Characteristics
Fixed Variable Each Basis Actual Budget Flex'd
Revenue $ 37,500 $ 41,250 $0 $75 units sold 550 X $75 = 41,250
Expenses:
Wages and salaries $ 20,000 $ 21,500 $5,000 $30 units sold
Gasoline and supplies 4,500 4,950 $0 $9 units sold 550 X $9 = 4,950 Var. only
Equipment maintenance 1,500 1,650 $0 $3 units sold 550 X $3 = 1,650
No variable Office and shop utilities 1,000 1,000 $1,000 $0 Y = a + b X
No variable Office and shop rent 2,000 2,000 $2,000 $0 $ 20,000 $ 5,000 500 $30 Fxd.
No variable Equipment Depreciation 2,500 2,500 $2,500 $0 Flex'd 550 &
No variable Insurance 1,000 1,000 $1,000 $0 $ 21,500 $ 5,000 $ 16,500 flexed Variable
Total expenses 32,500 34,600 y a bX
Net operating income $ 5,000 $ 6,650 $ 1,650 ←←Δ due to Volume =
STATIC v. FLEX Budget
For the Period Ended June 30 3
Single Driver = Units Sold Fav/(Unfav)
Planning Flexible Activity
Budget Budget or Volume
Variance
Number of units (Q) 500 550 50 Fav
Revenue $ 37,500 $ 41,250 $ 3,750 Fav Driver 550 100% var.
Expenses: Variable each Qty. bX a
Wages and salaries $ 20,000 $ 21,500 $ (1,500) Unfav $30 550 16,500 $ 5,000 Mxd. Fxd. & Var.
Gasoline and supplies 4,500 4,950 $ (450) Unfav $9 550 4,950 $ - 0 100%
Equipment maintenance 1,500 1,650 $ (150) Unfav $3 550 1,650 $ - 0 Var.
Office and shop utilities 1,000 1,000 $ - 0 -- 100% Fxd.
Office and shop rent 2,000 2,000 $ - 0 -- 100% Fxd.
Equipment Depreciation 2,500 2,500 $ - 0 -- 100% Fxd.
Insurance 1,000 1,000 $ - 0 -- 100% Fxd.
Total expenses 32,500 34,600 (2,100) Unfav
Net operating income $ 5,000 $ 6,650 $ 1,650 Fav
Single Driver = Units Sold Due to
STATIC v. FLEX Budget 3 Volume
For the Period Ended June 30 F/(Unfav)
% change
Planning Flexible Activity Change should be based on units
Budget Budget or Volume F/(Unfav)
Variance % change
Number of units (Q) 500 550 10.0% F
Revenue $ 37,500 $ 41,250 $ 3,750 10.0% F 100% Variable
Expenses: Reminder Y = a + bX Fixed Variable Each
Wages and salaries $ 20,000 $ 21,500 $ (1,500) -7.5% U Fxd. & Variable $ 5,000 $ 30
Gasoline and supplies 4,500 4,950 $ (450) -10.0% U 100% variable $ - 0 $ 9
Equipment maintenance 1,500 1,650 $ (150) -10.0% U 100% variable $ - 0 $ 3
Office and shop utilities 1,000 1,000 $ - 0 0.0%
Office and shop rent 2,000 2,000 $ - 0 0.0%
Equipment Depreciation 2,500 2,500 $ - 0 0.0%
Insurance 1,000 1,000 $ - 0 0.0%
Total expenses 32,500 34,600 (2,100) -6.5% U
Net operating income $ 5,000 $ 6,650 $ 1,650 33.0% F
Due to Volume Revenue 10.0% Up = Fav 3 Single Driver = Units Sold
Net operating income 33.0% Up = Fav
PPT 4 Single Driver = Units Sold Actual minus Flex'd
Revenue Variance Added Excel 4 Management focus for spending
STATIC v. FLEX Budget Non-Con. Volume Controllable
For the Period Ended June 30 Static F/(Unfav) Prior Step F/(Unfav) Budget
4 Planning Activity Flexible Spending Data Given to Actual 550
Budget or Volume Budget Revenue Actual Variance All Controllable 3.18
Variance Variance 1,749
Number of units (Q) 500 550 Controllable 550
Revenue $ 37,500 $ 3,750 $ 41,250 $ 1,750 $ 43,000 $ 5,500 F F
Expenses: 0
Wages and salaries $ 20,000 $ (1,500) $ 21,500 $ (2,000) $ 23,500 (3,500) U U
Gasoline and supplies 4,500 $ (450) 4,950 $ (150) 5,100 (600) U U
Equipment maintenance 1,500 $ (150) 1,650 $ 350 1,300 200 F F
Office and shop utilities 1,000 $ - 0 1,000 $ 50 950 50 F F
Office and shop rent 2,000 $ - 0 2,000 $ - 0 2,000 0
Equipment Depreciation 2,500 $ - 0 2,500 $ - 0 2,500 0
Insurance 1,000 $ - 0 1,000 $ (200) 1,200 (200) U U
Total expenses 32,500 (2,100) 34,600 (1,950) 36,550 (4,050) U U
Net operating income $ 5,000 $ 1,650 $ 6,650 $ (200) $ 6,450 1,450 F U
Revenue $s Variable Summary Variable Price // 550 Fav.
Static 37,500 Units $s Each each Volume Spending 500 50
Volume [or Activity] 3,750 50 75 Revenue $ 75 $ 3,750 $ 1,750
Price/other 1,750 Expenses $ 42 (2,100) (1,950)
Actual 43,000 Income $ 33 1,650 (200)
1,450
Wages and salaries $s Variable
Static 20,000 Units $s Each Fixed Single Driver = Units Sold
Activity (1,500) 50 30 $ 5,000
Price/other (2,000)
Actual 23,500 4
PPT
ClassCo Manufacturing
STATIC Budget Y=a+bX Static 5A1 Multiple Drivers
Budget
Sales Qty. 3,000 Hours 12,000 Driver 4.00 Hrs.Each
Sell each $ 340 Units 3,000 Driver
Sales Units sold 1,020,000 STATIC Budget
TWO Variable 67% 33%
Expense Driver Each Fixed Fixed Variable % Fxd.
100% V Direct labor DL Hours $ 14.00 0 168,000 0 168,000 0% 12,000 $ 14.00 $ 168,000
100% V Material & Supplies Units $ 22.00 0 66,000 0 66,000 0% 3,000 $ 22.00 $ 66,000
Y=a+bX Line Supervision Units $ 3.00 110,000 119,000 = $110,000 + $3 X 3000 units 110,000 9,000 92% Y=a+bX
100% F Deprecation N/A $ - 0 250,000 250,000 250,000 0 100%
Y=a+bX Rework & repair DL Hours $ 2.50 20,000 50,000 = $20,000 + $2.5 X 12000 hrs. 20,000 30,000 40% 12,000 $ 2.50 20,000
Y=a+bX Testing Units $ 4.00 80,000 92,000 = $80,000 + $4 X 3000 units 80,000 12,000 87%
Y=a+bX Admin. N/A $ - 0 120,000 120,000 120,000 0 100%
580,000 285,000 67% 3,000
Total 580,000 865,000 Sum F/V 865,000 340
Operating Income 155,000 Sales 1,020,000
ClassCo Manufacturing Multiple Drivers Line supervision Materials & supplies Direct Labor
FLEX Budget 5C3 Driver Units 3,300 Driver Units. 3,300 Driver Hrs. 14,000
Actual Actual From Actual Below per unit $ 3.00 per unit $ 22.00 per unit $ 14.00
Actual Hrs. 14,000 9,900 72,600 196,000
Actual UnitsSold 3,345 Act. Units made 3,300 MADE=Production Fxd. 110,000 Fxd. 0 Fxd. 0
Flex'd 119,900 Flex'd 72,600 Flex'd 196,000
Sales $ 1,137,300
TWO Variable 64% 36% Rework & Repair
Expense Driver Each Fixed Fixed Variable Variable Driver Hrs. 14,000
Direct labor DL Hours $ 14.00 0 196,000 Flexible budget 0 196,000 14,000 $ 14.00 per unit $ 2.50
Material & Supplies Units $ 22.00 0 72,600 Flexible budget 0 72,600 14,000 $ 22.00 35,000
Line Supervision Units $ 3.00 110,000 119,900 Flexible budget 110,000 9,900 3,300 $ 3.00 Fxd. 20,000
Deprecation N/A $ - 0 250,000 250,000 Flexible budget 250,000 0 N/A N/A Flex'd 55,000
Rework & repair DL Hours $ 2.50 20,000 55,000 Flexible budget 20,000 35,000 14,000 $ 2.50
Testing Units $ 4.00 80,000 93,200 Flexible budget 80,000 13,200 3,300 4 Testing
Admin. N/A $ - 0 120,000 120,000 Flexible budget 120,000 0 N/A N/A Driver units 3,300
Total: 580,000 906,700 580,000 326,700 per unit $ 4.00
Sum F/V 906,700 13,200
Operating Income 230,600 Fxd. 80,000
Flex'd 93,200
Sales
Sold Qty. @ Budget SP each
1,137,300
Act. Q. 3,345
Bud.SP ea. $ 340.00 Volume
Budget 1,020,000 117,300
Actual sales 1,145,300 8,000
Spending/Performance/Price
ClassCo Manufacturing
Actual Actual 5C2
Units 3345 Actual Hrs. 14,000
Act. Units made 3,300 made S
Sales 1,145,300
Expense
Direct labor 204,000
Material & Supplies 69,000
Line Supervision 131,000 Multiple Drivers
Deprecation 248,500
Rework & repair 47,000
Testing 95,000
Admin. 128,000
Total 922,500
Operating Income 222,800
Column #1 Column #2 Column #3 Column #4 Column #5
STATIC v. FLEX Budget Non-Con. Volume 5D4 Controllable Performance Total `
For the Period Ended June 30 F/(Unfav) from above Budget
Multiple Drivers Planning Activity Flexible F/(Unfav) to Actual
Budget or Volume Budget Spending Actual Variance
Variance Variance
DL Hrs 12,000 14,000 14,000
Units 3,000 3,300 3,300
Sales 1,020,000 117,300 1,137,300 8,000 1,145,300 125,300 0
Expense
DL Hours Direct labor 168,000 (28,000) 196,000 (8,000) 204,000 (36,000) 0
Units Material & Supplies 66,000 (6,600) 72,600 3,600 69,000 (3,000) 0
Units Line Supervision 119,000 (900) 119,900 (11,100) 131,000 (12,000) 0
N/A Deprecation 250,000 0 250,000 1,500 248,500 1,500 0
DL Hours Rework & repair 50,000 (5,000) 55,000 8,000 47,000 3,000 0
Units Testing 92,000 (1,200) 93,200 (1,800) 95,000 (3,000) 0
N/A Admin. 120,000 0 120,000 (8,000) 128,000 (8,000) 0 Volume 75,600
Total 865,000 (41,700) 906,700 (15,800) 922,500 (57,500) 0 Performance (7,800)
Total 67,800
Operating Income 155,000 75,600 230,600 (7,800) 222,800 67,800 0
PPT

Quantity of units sold is the driver is this example

More Revenue is Favorable Less Revenue is Unfavorable More Expense/Cost is Unfavorable Less Expense/Cost is Favorable

A

B

B-A

D

A-D

1

2

3

4

5

A

A

B

B

C

C

D

D

E

E

Y

Y

FROM ACTUAL BELOW X

X

ACTUAL

1

Ch.9 HW

Chapter 9: Flex Budget -- Homework
BASE CASE =Static Budget=Plan
Units 1200 two columns of variable
Variable Expense Fixed
$s $Per Unit Sold per DL $ Fixed$s
Sales$ 720,000 $ 600.00
BASE CASE =Static Budget=Plan
$ 70.00 1200
Direct materials 180,000 $ 150.00 0 0 per unit Units
Direct labor 84,000 $ 70.00 0 0 Fxd. Variable
a + b x bx = Manufacturing overhead Manufacturing overhead
Manufacturing overhead 267,000 $ 60.00 1.25 90,000 90,000 + $ 1.25 84,000 $ 105,000
Selling Expense 81,000 $ 30.00 0 45,000 $ 60.00 1,200 $ 72,000
Administrative 66,000 $ 5.00 0 60,000 sum fixed & variable 267,000
2 Drivers: Units And DL$s
Operating Income 42,000
Actual Units 1100
DL$ =
$s $ 70.00
Sales$ 654,000 per unit
Direct materials 169,000
Direct labor 71,000
Manufacturing overhead 250,000
Selling Expense 79,000
Administrative 63,000
Operating Income 22,000
To Do
A. Prepare flexible budget analysis with 5 valued columns of
Base [Plan or Budget}
Volume variance
Flexible budget
Spending variance
Actual
B. What is the Breakeven # of units for the Base
show computations

Sheet3