Budgeting & control

profilemasure.dorsan
Budgeting.xlsx
Home>Business & Finance homework help>Financial markets homework help>Budgeting & control

Sheet1

Units per year 60000
Fixed   Variable
 per Unit
Manufacturing costs:
Direct materials 25
Direct labor 15
Manufacturing overhead 500000 8
Period costs:
Selling expenses 2
Administrative expenses 300000        
Totals 800000 50
FC+TG 1500000 60000
cm/unit
Contribution margin 25
Sales price 75
CM + VC
Breakeven (number of units x 75) - 800000 = 0
Number of units 32000 2400000 Sales breakeven 2400000
per unit %
Sales 4500000 75 100%
VC 3000000 50 67%
CM 1500000 25 33%
FC 800000 13.3333333333 18%
Op inc 700000 11.6666666667 16%
Margin of safety 2100000 Sales - Breakeven
Sales 60 3600000 1
VC 50 3000000
CM 10 600000 17% 80000.00
FC 13 800000
Op inc -3 -200000
Problem 2
Number of units 30000
Budgeted                                Budgeted Percentage
Costs Costs of Costs
per Pair Considered
Variable
Direct materials 630000 21 100%
Direct labor 300,000 10 100
Manufacturing overhead (fixed and variable) 720,000 24 25
Selling and administrative expenses 600000 20 20
Totals 2250000 75
Bugeted costs 2250000
Operating inc 900000
sales 3150000
Sales price 105
b 1
MOH 540000
Adm 480000
1020000
2
Direct mat 21
Direct labour 10
Man ov 6
Selling exp 4
total 41
3
Contr marg 80
4
80x=1020000 12750
Problem 20.3
revenues
1000
750
500
250
500 1000 1500 2000
Parking space hours
100
Fixed cost
Sup sal 24000
wages 78000
Rent 87000
Maint 36000
Total 225000
total costs
contr marg sales - var 0.45
0.9
breakeven Fixed costs/%CM 250000
sales 0.5
var c 0.2
CM 0.3
CM% 0.6
Fixed costs
rent 87000
Sup sal 24000
Vac pay 3000
maint 36000
total new fixwd 150000
total fixed 150000
Target profit 300000
Total CM req 450000
Fixed cost + target/ CM% 750000
20.4
Fixed cos
Occupancy cost 3160
Salaries 3640 4004
Other 1200
Variable costs 6
Sales per unit 10 11
CM 4 5
CM% 40% 45.45%
Fixed c 8000 8364
Break even $ 20000 18402.6402640264
Breakeven units 2000 1672.9672967297
2000
The drawing is in the notes
Volume in units 2200 2600
Sales in $ 22000 26000
Var cost 13200 15600
CM 8800 10400
CM% 40% 40%
Fixed cost 8000 8000
Op inc 800 2400
Problem 20.5
20 machines
600 - 800 per machine
Merchandise 9000
Investment 20 m 36000
Depeciation 600
Sales 0.75
Var cost 0.25
Commision var 0.05
Total var 0.3
Salary fixed 1500
Fixed other 600
Total fixed 2100
Unit CM 0.45
CM% 60%
Break $ 3500
Break un 4666.6666666667
4666.6666666667
Target oper inc 13500 per year
1125 per month
FC+TG 3225
Sales in un 7166.6666666667
Sales in $ 5375
New var cost 0.25 CM 0.5 0.6666666667
New FC 2700
Monthly brk un 5400
Brk $ 4050
Problem 20.6 new situation
Units 18500
Direct labour 15%
Sales per unit 100 1850000 100
Direct mat 10 185000 10
Direct labour 20 370000 23
Manufact ov 30 555000 30
Total var 60 1110000 63
CM 40 740000 37
CM % 40% 0.4 0.37
Fixed cost 390000 390000 40
Op inc 350000
Fixed+Target 740000 20000
CM=40X
100X-63=40x
60x=63
x= 1.5
700000 investment
25000 new capacity
volume 20000 25000
CM 37 per un 740000 925000
Fixed cost 390000 530000
Op inc 350000 395000
PROBLEM 20.7
1000000 eggs to maturity
Clown fish 5500 per batch 10% 35 weeks 1 waterchange 15 feedings
Angel fish 9500 5% 50 weeks 2 waterchanges 20 feedings
feeding 150
water change 1000
heating & light 400
Fixed oVH 80000
Price of clown 4
Price of Angel 10
Angel clown
Units 50000 100000
Sales price 10 4
Total sales 500000 400000
Variable cost
Eggs 9500 5500
Feeding 150000 78750
Waterchange 100000 35000
Heating & Light 20000 14000
Total var 279500 133250
Contr m 220500 266750
Fixed cost 80000 80000
Op inc 140500 186750

Sheet2

Sales units Q1 Q2 Q3 Q4 Total
M 7776000 11289600 11016000 741600 30823200
P 7698240 1140250 10795680 763848 20398018
T 15474240 12429850 21811680 1505448 51221218
B inv 1000000 900000 800000 600000 1000000
Des inv 900000 800000 600000 1000000 1000000
Prod needed 15374240 12329850 21611680 1905448 51221218
Days per Q 65 70 65 18.75 218.75 100 sqm/min
Production cap 18720000 20160000 18720000 5400000 63000000 144000 sqm/day per machine
U/O capacity -3345760 -7830150 2891680 -3494552 -11778782 288000 sqm/day per plant
10.0405555556 required additional days
Overtime C 0 0 37178.7428571428 0 37178.7428571428 30000 per person
540000 total direct labor cost
5250 hours of work of the plant
Sales M 3499200 5080320 4957200 333720 13870440 102.8571428571 cost per hour
Sales P 3079296 456100 4318272 305539.2 8159207.2 154.2857142857 cost per overtime hour
Total Sales 6578496 5536420 9275472 639259.2 22029647.2
MCOGS 3013200 4374720 4268700 287370 11943990
PCOGS 2831027.76 419326.9375 3970111.32 280905.102 7501371.1195
TCOGS 5844227.76 4794046.9375 8238811.32 568275.102 19445361.1195
DL C 135000 135000 135000 135000 540000
DL Overtime 0 0 37178.7428571428 0 37178.7428571428
Foreman 12500 12500 12500 12500 50000
Rent 15000 15000 15000 15000 60000
Utilities 1500 1500 1500 1500 6000
Utilities 76871.2 61649.25 108058.4 9527.24 256106.09
0
Cust service 8375 8375 8375 8375 33500
Agent 328924.8 276821 463773.6 31962.96 1101482.36
Admin 12500 12500 12500 12500 50000
EBIT 143597.24 219027.8125 242774.937142857 -155381.102 450018.887642856

Sheet3

Lost of problems 5
23.2
Units Price
Sales (budgeted) 300000 18000000
Finished goods inv, January 1 60000 2400000
Finished goods inv March, 32 20000
Cost of finished goods manufactured (42$)
a) Planned production of finished goods in units
Budgeted sales 300000
Add ending inv 20000
Total av for sales 320000
Less beg inv 60000
Total plant production 260000
b) Cost of finished goods manufactured 10920000
Number of units 260000
Cost 42
c) Finished goods inventory march 31st 840000
Number of units 20000
Cost 42
d) COGS 12480000
FG INV Jan 1st 2400000
Cost of finished goods manuf 10920000
Less FG INV March 31st 840000
23.3
sept oct nov dec
sales in units 50000 70000 90000 60000
Sales $ 500000 700000 900000 600000
Sales collection for the 1st payment 70% 350000 490000 630000 420000
Sales collection 2nd payment 25% 125000 175000 225000
Sales collection 3rd payment 3 % 15000 21000
Sale of equipment 9000
Total cash in 350000 615000 829000 666000
Inventory units 9000 11000 8000
Inventory value 54000 66000 48000
Purchases units 72000 87000
Purchases$ 432000 522000
Purchases payment 216000 261100
Purchases payment next month 216000
Operating expenses 225000
Fixed 60000
Depreciation 40000
Other 125000
Pay out operating expenses 185000
Note payable 102250
Total cash out 0 216000 764350
Net cash 350000 399000 64650
Cash Balance Nov 1st 37200
Cash balance end nov 101850 Mistake somewhere100 more!!
23.4
a Cash receipts for the quarter
Sales of Q 700000
collect 70% 490000
Accounts receivable 250000
Total receipts 740000
b Payments of current payables for the q
New current payables 442000
Beginning current payables 90000
Totl Current payables 532000
Ending current payables 105000
Payments of current payables for the q 427000
c 18000 we need to generate this amount of the prepament for the quarter because the beg and end are expected to be the same
d Initial cash 10000
Collections 740000
Current payables 427000
Prepayments 18000
Debt servie payment 260000
Tax 50000
Ending cash balance -5000
e Borrowing for the quarter 15000
f the debt ratio is too high
23.5
Jan Feb Mar Apr May Jun
Sales 80000 60000 90000 40000 100000
Purchases 50000 40000 55000 25000 60000
Collection 1 40% 32000 24000 36000 16000 40000 0
Collection 2 50% 40000 30000 45000 20000 50000
Collection 3 10% 8000 6000 9000 4000 10000
Subtotal collections 32000 64000 74000 67000 69000 54000 10000
a
Payment 1 Purch 37500 30000 41250 18750 45000 0 0
Payment 2 Purch 12500 10000 13750 6250 15000 0
Subtotal purchases 37500 42500 51250 32500 51250 15000 0
Depreciation (We are not paying dep!!!) 3000 3000 3000 3000 3000 3000 3000
Fixed Selling & Admin 7000 7000 7000 7000 7000 7000 7000
Var Selling & Admin 4000 3000 4500 2000 5000 0 0
Payment of Selling & Admin 11000 10000 11500 9000 12000 7000 7000
Interest expense 3000 3000 3000 3000 3000 3000 3000
Repayment of principle (doesn't go to income st) 1000 1000 1000 1000 1000 1000 1000
Payments of debt service 4000 4000 4000 4000 4000 4000 4000
EBT 13000 4000 17500 0 22000 -13000 -13000
Tax expense 25% 3250 1000 4375 0 5500 -3250 -3250
Net Income 9750 3000 13125 0 16500 -9750 -9750
Tax payment 8625
b
Cash balance 1st May 30000
Cash in 69000
Cash out purchases 51250
Selling & Admin payment 12000
Debt service payment 4000
Tax payment 0
Eding cash balance 31750
23.6
Jul Aug Sep Oct
Sales 340000 360000 300000 220000
COGS 221000 234000 195000 143000
Gross Margine 119000 126000 105000 77000
% Gross Margine 0.35 0.35 0.35 0.35
Collection 1 75% 255000 270000 225000
Collection 2 24% 160000 40000 81600 86400
a 1 --> Subtotal Collections 160000 295000 351600 311400
Operating expenses 35000 35000 35000 35000
Depreciation 5000 5000 5000 5000
Op exp variable 34000 36000 30000 22000
a3 --> Payment of op exp 69000 71000 65000
Inventories beg 80000 234000 195000 143000
Inventories end 234000 195000 143000 0
a 2 --> Purchases 375000 195000 143000 0
Payment of purchases 375000 195000 143000
a 4 --> Cash payments on acc pay + op exp 160000 444000 266000 208000
b Begg cash 18000 233000 84000 169600
Cash in AR 160000 295000 351600 311400
Loan from the bank 240000 0 0 0
Cash out 160000 444000 266000 208000
Equipment 25000 0 0 0
Ending cash 233000 84000 169600 273000
Exersise dev var Deviation Dev as % % deviations per unit dev
Budget Budget % sales Buget per unit Actual Actual % sales Actual per unit Actual -Budget
Sales units 5000 1 5050 1 50 1.0%
Sales 1100000 100.0% 220 1050000 100.0% 208 -50000 -4.5% 0.0% -12
COGS 600000 54.5% 120 580000 55.2% 115 -20000 -3.3% 0.7% -5
Direct lab 200000 18.2% 40 220000 21.0% 44 20000 10.0% 2.8% 4
Gross Margin 300000 27.3% 60 250000 23.8% 50 -50000 -16.7% -3.5% -10
Depreciation 50000 4.5% 10 50000 4.8% 10 0 0.0% 0.2% -0
Marketing exp 60000 5.5% 12 59000 5.6% 12 -1000 -1.7% 0.2% -0
Administration 90000 8.2% 18 90000 8.6% 18 0 0.0% 0.4% -0
Total exp 200000 18.2% 40 199000 19.0% 39 -1000 -0.5% 0.8% -1
Operating income 100000 9.1% 20 51000 4.9% 10 -49000 -49.0% -4.2% -10
Sales dev -50000
Unit component 11000 Difference in units * Budget price
Price component -61000 Difference in price * Budget price
-50000 to check: sum of components equalsales dev
Problem 2 (Cash) on week 12 664class
Jan Feb Mar Apr May Jun
Starting cash 5000 14500 54995 115944 179927 284882
Direct customers 20000 20000 21000 23000 56443 40567
Distributer collections 1 25000 40494.5 45000 50000 100000 62839
Dist 2 10000 12500 20247.25 22500 25000 50000
Dist 3 10000 20000 12500 20247.25 22500 25000
Loans in 35000 35000 70000 50001 20000 30000
Total cash in 100000 127994.5 168747.25 165748.25 223943 208406
Payment to local suppliers 30000 25000 12000 13000 20000 40000
Payment to import suppliers 30000 30000 56000 50000 60000 40000
Direct Personnel cost 10000 10000 10000 10000 10000 10000
Indirect 5000 5000 5000 5000 5000 5000
Saled dep 10000 10000 10000 10000 10000 10000
Admin 2000 2000 2000 2000 2000 2000
Interest 500 500 500 500 500 500
Taxes 225 225
* Look how to do it!!! Comissions 3000 5000 3550 5199.45 7322.15 7028.35
Loans out 8523 6065 4166
Total cash out 90500 87500 107798 101764.45 118988.15 114753.35
Total cash month 9500 40494.5 60949.25 63983.8 104954.85 93652.65
Total cash accumulated 14500 54994.5 115944.25 179927.8 284881.85 378534.65
Loan 35000 70000 131477 175413 191247 221247

Sheet4

Income st
Convert in st into cash moovements
Starting cash position
Ending cash position
Borrow/repay loans
nov dec jan feb mar apr may jun
Sales in units 1000 2000 3000 4000 5000 6000 5000 4000 sales $ CM int exp Tax
Net sales 3000 6000 9000 12000 15000 18000 15000 12000 3 30% 6% 25%
COGS 2100 4200 6300 8400 10500 12600 10500 8400
CM 900 1800 2700 3600 4500 5400 4500 3600 Cost/unit 70% 2.1
Dep exp 200 200 200 200 200 200 200 200
G&A exp 100 100 100 100 100 100 100 100 pay in the month
Operating inc 600 1500 2400 3300 4200 5100 4200 3300
Interest exp 0 11.675 18.07 12.135 10.595 12.67 0 0
EBT 600 1488.325 2381.93 3287.865 4189.405 5087.33 4200 3300
Tax exp 150 372.08125 595.4825 821.96625 1047.35125 1271.8325 1050 825 Ending inv 90% of sales next month
Net Income 450 1116.24375 1786.4475 2465.89875 3142.05375 3815.4975 3150 2475
Net income accum 1786.4475 4252.34625 7394.4 11209.8975 14359.8975 16834.8975
Customers
Beg inv units 100 1800 2700 3600 4500 5400 4500 3600 cash 1 month later 2 month later
End inv units 1800 2700 3600 4500 5400 4500 3600 0 20% 40% 40%
Beg inv euro 210 3780 5670 7560 9450 11340 9450 7560
End inv euro 3780 5670 7560 9450 11340 9450 7560 0 Suppliers
Purchases units 2700 2900 3900 4900 5900 5100 4100 400 cash 3 month
Purchases $ 5670 6090 8190 10290 12390 10710 8610 840 50% 50%
Starting cash 0 0 0.24375 0.17375 0.03875 -0.35625 2302.97375 8952.97375
Collection 1 600 1200 1800 2400 3000 3600 3000 2400
Collection 2 1200 2400 3600 4800 6000 7200 6000
Collection 3 1200 2400 3600 4800 6000 7200
Loans in 2335 1279 415
Total cash in 2935 3679 5400 8400 11815 14400 16200 15600
Payment 1 2835 3045 4095 5145 6195 5355 4305 420
Payment 2 2835 3045 4095 5145 6195
G&A 100 100 100 100 100 100 100 100
Interest expense 0 11.675 18.07 12.135 10.595 12.67 0 0
Tax exp 522.08125 2464.8 3146.8325 Taxes are accumulated and paid end of each quarter
Loans out 1187 308 2534
Total cash out 2935 3678.75625 5400.07 8400.135 11815.395 12096.67 9550 9861.8325
Cash movement 0 0 -0 -0 -0 2303.33 6650 5738.1675
Cash balance 0 0 0 0 -0 2303 8953 14691 credit line 20000
Debt 2335 3614 2427 2119 2534 0 0 0