accounting for managers

profilelindas
EmbeddingSpreadsheets.docx
Home>Business & Finance homework help>Accounting homework help>accounting for managers

Embedding Spreadsheets

Please note that the Turnitin Link for the INDIVIDUAL AFM assignment (identified as Leicester) is available.

Note that you cannot submit two pieces of work so if you have workings in excel, you need to embed these in a Word document.

You could simply do this by cutting and pasting or if you want a functioning spreadsheet in Word you need to do as follows:

You can create a spreadsheet file and then embed sections of it into your word document at the relevant place. If you wish to do it this way, read on!

The following extract from a Microsoft Excel (.xls) file has been ‘copied and pasted’ into this document.

By double clicking on the above object (or ‘right clicking’ the mouse and selecting ‘Worksheet Object’ and ‘Open’), you will see that it will function as a spreadsheet!

To use this particular function yourself, you must have your word.doc open at the same time as you are building the spreadsheet model.

Within the spreadsheet page you should first highlight the area of the spreadsheet you wish to copy. This is done by ‘left clicking’ the mouse and dragging it until the required area is covered.

Then use the ‘copy’ function.

Next, within the word document at the location where you wish to place the spreadsheet:

1. On the Home tab, in the Clipboard group, click the arrow under Paste, and then click Paste Special.

Word Ribbon image

2. In the As list, select Microsoft Office Excel object .

3. Click Ok or Paste to insert an embedded object.

It is important that you use ‘paste special’ otherwise the embedded section will be text only (not a spreadsheet).

Manufacturing costs per unit

Material 10.00

Variable labour and overhead 16.00

Fixed overhead per month210,000.00

Depreciation included in Fixed overhead54,000

20082009

DecJanFebMar

Budgeted Sales14,00016,00022,00017,000

Raw material units 114,00016,00022,00017,000

Closing Stock required:

Finished goods25%4,0005,5004,2505,000

Manufacturing budget (units)

Sales in month14,00016,00022,00017,000

Opening stock finished goods3,5004,0005,5004,250

10,50012,00016,50012,750

Closing stock required4,0005,5004,2505,000

Manufacture14,50017,50020,75017,750

Raw materials closing stock required10%1,7502,0751,7752,100

Limiting factors

P Q R
SPU 85 105 105
VCU
DM 5 per kg 10 5 15
DL 4 per hr 15 23 19
VO 9 13 11
TVCU 34 41 45
Unit Contribution 51 64 60
Kilograms required 2 1 3
Contribution per kg of material 25.5 64 20
Rank 2 1 3
A B
SPU 35 25
VC 23 15
Unit Contribution 12 10
Hours to make 3 2
Contribution per hour 4 5
Rank 2 1
A B C
Sales units 500 300 400 available
Direct Hours per unit 0.5 1.5 2
Total hours required / available 250 450 800 1500 1200
SPU 10 20 24
VCU
DM 0 0 0
DL 5 0 0 0
VO 6.5 12 15
TVCU 6.5 12 15
Unit Contribution 3.5 8 9
Hours required 0.5 1.5 2
Contribution per hour 7 5.3333333333 4.5
Rank 1 2 3 Hours used
hours units
Produce A 250 500 1750 250
Produce B 450 300 2400 700
Produce C 500 250 2250
6400

Make or buy example

A B C Needed Available
Demand 100 200 300
Labour cost needed 2000 14000 24000 40000 30000
SP 100 200 300
VC 60 80 200
Contribution per unit 40 120 100
Labour cost 20 70 80
Cont per £1 of labour 2.00 1.71 1.25
Use own labour in following priority 1 2 3
Contractor price 50 70 210
Contribution from contractor 50 130 90
Contribution per £1 of labour gained 2.50 1.86 1.13
by using contractor
Use contractor in following order 3 2 1
Cont per £ of labour - make 2.00 1.71 1.25
Cont per £ of labour - buy 2.50 1.86 1.13
Differential -0.50 -0.14 0.13
Labour used Total Units Contribution
Make A 2000 2000 100 4000
Make B 14000 16000 200 24000
Make C 14000 30000 175 17500
But C 125 11250
56750

Make or buy

A B C D
Units 2000 3000 4000 5000
Directly Attributable Fixed costs 2000 7000 7000 9000
VCU
DM 5 6 3 5
DL 10 11 6 8
VO 3 2 2 3
TVCU 18 19 11 16
DAFC per unit 1.00 2.33 1.75 1.80
Relevant cost for decision 19.00 21.33 12.75 17.80
Buy-in price 10 23 17 18
Cost of Making 38000 64000 51000 89000
Cost of Buying 20000 69000 68000 90000
Differential costs 18000 -5000 -17000 -1000
A B C
TVCU 2.5 8 5
DAFC per unit 0 0 0
Relevant cost for decision 2.50 8.00 5.00
Buy-in price 4 7 5.5
Differential -1.50 1.00 -0.50
A B
Demand 9000 13000
Kg required 3.5 8
Total 31500 104000 135500
Available 90000
VCU 11 16
VC per kg 3.14 2.00
Contractor price 18 26
Per kg 5.14 3.25
Excess of contractor per kg 2.00 1.25
Better to make A - saves more buying in cost simplistic option
Buy B 338000
Make A 31500 99000
437000
Best option
Make A 99000
Make B 58500 7312.5 117000
Buy B 5687.5 147875
363875
A B
Demand 1000 1000
Mc hours required 2 3
Total 2000 3000 5000
Available 3500
VCU 20 25
VC per hour 10.00 8.33
Contractor price 31 47
Per hour 15.50 15.67
Excess of contractor per hour 5.50 7.33
Better to make B - saves more buying in cost
units hours
Make B 1000 3000 25000
Make A 250 500 5000
Buy A 750 23250
53250
Fixed cost A 10 10000
B 15 15000 25000
78250

Bevel

Space constraint
W X Y Z
SP 25 30 35 30
DL 5 6 8 10
Brass 1 1 2 4
Nails 3 6 5 3
Glue 1 1 2 3
VO 2 2 2 2
Total VC 12 16 19 22
Contribution 13 14 16 8
FO 12 12 12 12
Demand 10000 7000 6000 8000
Total FO 120000 84000 72000 96000 372000
Do not drop Z as it makes contribution to fixed costs 20000
Priority order 3 2 1 4 Contribution
Y 6000 6000 96000
X 7000 13000 98000
W 7000 20000 91000
Z 285000
Fixed overhead 372000
Profit \ (Loss) -87000
Nails constraint
W X Y Z
SP 25 30 35 30
DL 5 6 8 10
Brass 1 1 2 4
Nails £ 3 6 5 3
Glue 1 1 2 3
VO 2 2 2 2
Total VC 12 16 19 22
Contribution 13 14 16 8
FO 12 12 12 12
Demand 10000 7000 6000 8000
Total FO 120000 84000 72000 96000 372000
Nails 30 per Kg
Kgs of nails 0.100 0.200 0.167 0.100
Available
Kgs of nails in total 1000 1400 1000 800 4200 3150
Contribution per KG of nails 130 70 96 80
Priority order 1 4 2 3
Profitabilty Statement Kgs Total Contbn Contribution
Kgs Per Kg
W 1000 1000 130 130000 1750
Y 1000 2000 96 96000
Z 800 2800 80 64000
X 350 3150 70 24500 24500
314500
Fixed overhead 372000
Profit \ Loss (-) -57,500
Products Contbn Contribution
per
Product
W 10000 13 130000
Y 6000 16 96000
Z 8000 8 64000
X 1750 14 24500
314500
Fixed overhead 372000
Profit \ Loss (-) -57,500

Drop Product

x1 x2 x3 x4
Sales 450 550 200 260
VC 300 250 210 220
150 300 -10 40
SFC 20 15 5 10
130 285 -15 30 430
GFC 60 105 25 50 240
430
70 180 -40 -20

Absorption

Total X Y Z Store Maintenance
Indirect Wages 87560 13586 9190 14674 29650 20460
Materials 26900 11400 13700 1200 600
Rent and Rates 10700
Buildings Insurance 2600
Power 9600
Heat and Light 7400
Machine depreciation 20200
Machine Value 402000 201000 179000 22000
Machine value % 50.00% 44.53% 5.47%
DL hours 35000 8000 6200 20800
DL hours % 22.86% 17.71% 59.43%
MC hours 25200 7200 18000
Mc Hours % 28.57% 71.43%
Area sq ft 45000 10000 12000 15000 6000 2000
Area % 22.22% 26.67% 33.33% 13.33% 4.44%
Power kwh used 100 55 40 3 2
Power kwh % 55.00% 40.00% 3.00% 0.00% 2.00%
First allocation
Total X Y Z Store Maint'ce Basis
Indirect Wages 87560 13586 9190 14674 29650 20460
Materials 26900 11400 13700 1200 600
Rent and Rates 10700 2378 2853 3567 1427 475 Area
Buildings Insurance 2600 578 693 867 347 115 Area
Power 9600 5280 3840 288 0 192 Kwh
Heat and Light 7400 1644 1973 2467 987 329 Area
Depreciation 20200 10100 8995 1105 Mc value
Totals 164960 44966 41244 24168 33011 21571
Basis
Reapportion store 0 7545 5848 19618 -33011 dl hours
Reapportion Maintenance 0 6163 15408 -21571 mc hours
Total X Y Z
Final Totals 164960 58675 62499 43786 0 0
Recover
By machine hours 7200 18000
By labour hours 20800
Overhead recovery rate 8.15 3.47 2.11
per mc hr per mc hr per lab hr
Assume that the Product requires 1 labour and 1 machine hour in each department
If the direct cost of product is 120
Then unit cost is:
Direct cost £120.00
Overhead: labour hr machine hr
X 1 1 £8.15
Y 1 1 £3.47
Z 1 1 £2.11
£133.73
Cost of 100 products would be 100 £13,373

ABC 1

Russell
Output Production Material Labour Machine Direct
Units Runs in cost per hours per hours per Labour
Period unit unit unit cost per hour
A 20 4 40 1 1 5
B 20 4 100 2 2 5
C 200 5 40 1 1 5
D 200 5 100 2 2 5
18
Overhead Costs:
Scheduling 14280
Materials Handling 8800
23080
Absorption basis Machine hours
Absorption rate
Machine hours A B C D Total
20 40 200 400 660 34.97
per machine hour
Absorption costing schedule
A B C D Total
Direct Materials 800 2000 8000 20000 30800
Direct Labour 100 200 1000 2000 3300
Overheads 699 1399 6994 13988 23080
Total 1599 3599 15994 35988 57180
Units 20 20 200 200
Unit Cost £79.97 £179.94 £79.97 £179.94
Activity Based Costing
Overhead cost driver Production runs in period
Absorption rates Cost Runs Per Run
Scheduling 14280 18 793
Handling 8800 18 489
Production Runs A B C D Total
4 4 5 5 18
Scheduling Costs
Absorption rate per run 793 793 793 793
Cost 3173 3173 3967 3967
Handling Costs
Absorption rate per run 489 489 489 489
Cost 1956 1956 2444 2444
A B C D Total
Direct Materials 800 2000 8000 20000 30800
Direct Labour 100 200 1000 2000 3300
Overheads:
Scheduling 3173 3173 3967 3967 14280
Materials Handling 1956 1956 2444 2444 8800
Total 6029 7329 15411 28411 57180
Units 20 20 200 200
Unit Cost ABC 301.44 366.44 77.06 142.06
Unit Cost Traditional 79.97 179.94 79.97 179.94
Differences 221.47 186.51 -2.91 -37.88

ABC 2

A company
Output Set-ups Material Labour Machine Orders
Units in cost per hours per hours per Handled
Period unit unit unit in period
A 6000 10 40 2 4 15
B 7000 40 100 3 2 60
Overhead Costs:
Machine activity 320000
Production set-ups 40000
Materials Handling 65000
425000
Absorption basis Labour Hours
Absorption rate
Direct labour hours A B Total
12000 21000 33000 12.88
per labour hour
Absorption rate per unit £25.76 £38.64
Absorption costing schedule
A B Total
Direct Materials 240000 700000 940000
Direct Labour 180000 1260000 1440000
Overheads 309091 180303 489394
Total 729091 2140303 2869394
Units 6000 7000
Unit Cost £121.52 £305.76
(b) Activity Based Costing Approach
Overhead cost drivers Cost Activity Absorption
Rate
Machine activity Machine hours 320000 38000 8.42
Production set-ups Set-ups 40000 50 800.00
Materials Handling Number of orders 65000 75 866.67
A B Total
Machine hours 24000 14000 38000
Set-ups 10 40 50
Number of orders 15 60 75
Absorption rates
Overheads:
Machine activity 202105 117895 320000
Production set-ups 8000 32000 40000
Materials Handling 13000 52000 65000
Total 223105 201895
Units 6000 7000
Overhead per unit £37.18 £28.84

ABC Hospital

Hospital Case
In Average In-patient
Patients In-patient days
days per
patient
General 5,000 10 50,000
Geriatric 100 200 20,000
70,000
Overhead Costs:
Administration 6,000,000
Catering 2,000,000
Cleaning 1,500,000
Maintenance 1,000,000
Utilities 1,000,000
Total 11,500,000 70,000
Overhead absorption rate 164.29 per in-patient day
General Geriatric
In-Patient days 50,000 20,000
Overhead absorption rate 164.29 164.29
Overhead cost 8,214,286 3,285,714
Absorption costing schedule
Direct Treatment: General Geriatric Total
Consultants 2,000,000 500,000 2,500,000
Junior Doctors 500,000 1,000,000 1,500,000
Nurses 2,000,000 3,000,000 5,000,000
Direct Medical Supplies 500,000 1,500,000 2,000,000
Total Direct costs 5,000,000 6,000,000 11,000,000
Overheads 8,214,286 3,285,714 11,500,000
Total 13,214,286 9,285,714 22,500,000
In-patient days 50,000 20,000
Cost per In-patient day £264.29 £464.29
NB
Direct costs per patient 1,000 60,000 61,000
Indirect costs per patient 1,643 32,857 34,500
Total 2,643 92,857 95,500
Patients 5,000 100
Reconciliation to total costs 13,214,286 9,285,714 22,500,000

Smith ABC

Smith
Output Batch Production Machine Total Direct
Units Size Runs set-ups Machine Labour
per batch set-ups cost per hour
A 100000 100 1000 3 3000 5
B 100000 50 2000 4 8000 5
C 50000 25 2000 6 12000 5
5000 23000
Overhead Costs:
Machine set up 150000
Purchasing 70000
Processing 80000
300000
ABC basis Machine set-ups
Absorption rate
A B C Total
Machine set-ups 3000 8000 12000 6.52
per machine hour
Machine set up costs 19565 52174 78261
Per Unit £0.20 £0.52 £0.24
Total 0 0 0 0
Units 0 0 0
Unit Cost £0.00 £0.00 £0.00
Differences 0.00 0.00 0.00

MC and MR

SP per FC FC per AC per unit VC per
Output Unit Unit unit
0 0 600 600
1 505 600 600 720 120
2 471 600 300 402 102
3 439 600 200 288 88
4 407 600 150 231 81
5 377 600 120 201 81
6 346 600 100 189 89
7 317 600 86 182 96
8 288 600 75 180 105
9 259 600 67 186 119
10 232 600 60 198 138
Total Marginal Total Marginal Profit /
Output Revenue Revenue Cost Cost Loss (-)
0 0 0 600 -600
1 505 505 720 120 -215
2 942 437 804 84 138
3 1317 375 864 60 453
4 1628 311 924 60 704
5 1885 257 1005 81 880
6 2076 191 1134 129 942
7 2219 143 1274 140 945
8 2304 85 1440 166 864
9 2331 27 1674 234 657
10 2320 -11 1980 306 340

Full cost pricing

Units Rate
DM 10
DL 3 5 15
VO 0.5 6 3
28
Production Fixed
Overhead 350000 per month
Direct Labour hours 25000 per month
Overhead recovery 14 per hour
Machine hours 10000 per month
Monthly production 5000 units
Monthly labour 15000 hours
Full absorption cost
Direct Cost 28.00
Fixed 3 14 42.00
70.00
Profit 20% 14.00
84.00
Samson
Units Rate
DM 30
DL 1 6 6
VO 0.5 18 9
45
Production Fixed
Overhead 360000 per month
Direct Labour hours 36000 per month
Overhead recovery 10 per hour
Opportunity cost 10 per machine hour
Full absorption cost
Direct Cost 45.00
Fixed 1 10 10.00
Opportunity Cost 0.5 10 5.00
60.00
Profit 20% 12.00
72.00
Sahai (a)
VC 12 per hour
Fixed Costs 360000
Contract Labour 60000 hours
Overhead recovery 6 per labour hour
Full absorption cost
VC 12.00
Fixed 6.00
18.00
Profit 20% 3.60
21.60
Sahai (b)
SP per VC per Cont per Demand
hour hour hour hours
16 12 4 120000 480000
17 12 5 100000 500000
18 12 6 80000 480000
19 12 7 70000 490000
20 12 8 60000 480000

Naushaba

Units £ £ £
Sales 1000 100 100000
Cost of Goods sold:
Fixed 22000
Variable 1000 38 38000 60000
Gross Profit 40000
Marketing:
Fixed 18000
Variable 1000 12 12000 30000
Operating profit 10000
Mark up - full absorption cost basis
Gross Profit 40000
Cost of Goods sold 60000 66.67%
Variable costs:
CGS 38000
Marketing 12000
50000
(a) Mark- up - Variable cost basis
Gross Contribution 50000
Variable costs 50000 100.00%
(b) Special order: Cans
Activity 1000
Special order 300
1300
This is within current capacity SP VC Contb'n Total
Sales 300 55 38 17 5100
One-off manufacturing 3000
2100
Job can be accepted

Budgeting 1

Product X Y Total
Sales (units) 2000 1500
Materials per item
Material 216 4 6
Material 314 2 8
Materials in total
Material 216 8000 9000 17000
Material 314 4000 12000 16000
Opening stocks:
Units 100 200
Material 216 400 1200 1600
Material 314 200 1600 1800
Closing Stocks required (units) 200 150 10%
Material 216 800 900 1700
Material 314 400 1200 1600
Skilled labour hours 8 4
Semi-skilled labour hours 2 10
Production required (units) X Y
Sales 2000 1500
Closing stocks required 200 150
2200 1650
Opening stocks available 100 200
Production required 2100 1450
Materials Usage X Y Total
Material 216 8400 8700 17100
Material 314 4200 11600 15800
(c) Materials Purchases Mtl 216 Mtl 314
Used 17100 15800
Closing stocks required 1700 1600
18800 17400
Opening stocks available 1600 1800
Production required 17200 15600
Cost £ per kg or litre 20 9
Total cost £344,000 £140,400
(d) Labour budget X Y Total
Production required (units) 2100 1450
Hours
Skilled 16800 5800 22600
Semi - skilled 4200 14500 18700
Cost £ per hour £
Skilled 12 271200
Semi-skilled 8 149600
Total cost 420800
Echo
Sales 36800
Closing stocks required 7600
44400
Opening available 3000
41400
Efficiency of production 92%
Production required 45000
Labour hours 5
Total 225000
Alpha FG
Units
Sales 60000
Closing stocks required 3000
63000
Opening available 2000
Production required 61000
Kg RM
kgs
Production 3 183000
Planned closing stocks 7000
190000
Opening available 15000
Purchase 175000

Budgeting 2

Cash Budget 2007 2008
Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep
Sales (Units) 80 90 70 100 60 120 150 140 130 110 100 160
£ per unit 12 12 12 12 12 12 12 12 12 12 12 12
Sales Value £ 960 1080 840 1200 720 1440 1800 1680 1560 1320 1200 1920
Production (units) 70 80 90 100 110 130 140 150 120 160 170 180
Raw Materials £ per unit 4 4 4 4 4 4 4 4 4 4 4 4
Raw materials cost £ 280 320 360 400 440 520 560 600 480 640 680 720
Direct Labour £ per unit 3 3 3 3 3 3 3 3 3 3 3 3
Direct labour cost £ 210 240 270 300 330 390 420 450 360 480 510 540
Variable expenses £ per unit 2 2 2 2 2 2 2 2 2 2 2 2
Variable expenses cost £ 140 160 180 200 220 260 280 300 240 320 340 360
Fixed expenses 100 100 100 100 100 100 100 100 100 100 100 100
Cash budget
Opening balance 3200 3045 2920 2420 1545 780
Cash from Debtors 960 1080 840 1200 720 1440
4160 4125 3760 3620 2265 2220
Raw materials 520 560 600 480 640 680
Direct Labour 300 330 390 420 450 360
Variable expenses 0.75 150 165 195 210 225 180
Variable expenses 0.25 45 50 55 65 70 75
Fixed expenses 100 100 100 100 100 100
Motor Van 800
Total Cash out 1115 1205 1340 2075 1485 1395
Closing balance 3045 2920 2420 1545 780 825
Smith Ltd
Dec Jan Feb Mar
Sales value 80000 90000 150000 240000
Credit sales 50% 45000 75000 120000
Cash Sales 50% 45000 75000 120000
Purchases 60000 120000 200000
Expenses 20000 25000 25000
Depreciation 2000 2000 2000
Cash Expenses 18000 23000 23000
Cash budget
Opening balance -17000 13750 -1000
Balance Sheet debtors 80000
Cash Sales after discount 95% 42750 71250 114000
Cash from Debtors 0 45000 75000
105750 130000 188000
Purchases 90% 54000 108000 180000
Expenses 18000 23000 23000
Loan interest 0 0 5000
Dividend 20000 0 0
0 0 0
Motor Van
Total Cash out 92000 131000 208000
Closing balance 13750 -1000 -20000

Budgeting 3

Sahai 2008
Opening Balance Sheet
Cost Dep WDV
Machinery 4000 1600 2400
Motor Vehicles 2000 800 1200
3600
Stock
Finished goods 75 12 900
Raw materials 100 5 500
1400
Debtors
Oct 540
Nov 360
Dec 450 1350
Cash 650
3400
Creditors
Raw Materials Nov 120
Raw Materials Dec 180
Fixed Expenses Dec 100 400
Working Capital 3000
Net Assets 6600
Share Capital 4000 1 4000
Retained profit 2600
Total Capital Employed 6600
Jan Feb Mar Apr May Jun
Sales (Units) 40 50 60 90 90 70
£ per unit 18 18 18 18 18 18
Sales Value £ 720 900 1080 1620 1620 1260 7200
Production units 60 60 60 60 70 70
Raw materials units 30 40 50 60 80 64
Raw materials cost £ 5 150 200 250 300 400 320
Direct Labour £ per unit 4 4 4 4 4 4 4
Direct labour cost £ 240 240 240 240 280 280
3 3 3 3 3
Variable expenses £ per unit 3 3 3 3 3 3 3
Variable expenses cost £ 180 180 180 180 210 210
Production Budget (Units)
Opening Units 12 75 95 105 105 75 55
Produced 60 60 60 60 70 70
135 155 165 165 145 125
Sold 40 50 60 90 90 70
Closing stock 95 105 105 75 55 55
Fixed expenses 100 100 100 100 100 100 600
Cash budget
Opening balance 650 550 210 -2010 -2010 1050 DRS
Share Capital 3000
Cash from Debtors 540 360 450 720 900 1080 4500
1190 910 660 -1290 1890 2130
CRS
Raw materials 120 180 150 200 250 300 720
Direct Labour 240 240 240 240 280 280
Variable expenses 180 180 180 180 210 210
Fixed expenses 100 100 100 100 100 100
Machine 2000 0
Total Cash out 640 700 2670 720 840 890
Closing balance 550 210 -2010 -2010 1050 1240
Raw Materials Budget
Opening stock 500 350 250 200 200 250
Purchases 150 200 250 300 400 320 1620
650 550 500 500 600 570
To production 300 300 300 300 350 350
Closing stock 350 250 200 200 250 220
Production Cost
Materials 300 300 300 300 350 350 1900
Labour 240 240 240 240 280 280 1520
Variable Overhead 180 180 180 180 210 210 1140
720 720 720 720 840 840 4560
Creditors
Opening balance 300 330 350 450 550 700
Purchases 150 200 250 300 400 320
450 530 600 750 950 1020
Paid 120 180 150 200 250 300
Closing Balance 330 350 450 550 700 720
Debtors
Opening balance 1350 1530 2070 2700 3600 4320
Sales 720 900 1080 1620 1620 1260
2070 2430 3150 4320 5220 5580
Paid 540 360 450 720 900 1080
Closing Balance 1530 2070 2700 3600 4320 4500
Trading and Profit and Loss Account for period ending June 2008
Sales 7200
Opening stock RM 500
Purchases RM 1620
2120
Closing stock RM 220
Cost of RM sold 1900
Labour 1520
Variable Overhead 1140 4560
Opening stock of finished goods 900
5460
Closing stock of finished goods 660
Cost of Goods sold 4800
Gross Profit 2400
Fixed expenses 600
Depreciation:
Machinery 450
Vehicles 200 650
1250
Operating profit 1150
Profit brought forward 2600
Profit carried forward 3750
Closing Balance Sheet
Cost Dep WDV
Machinery 6000 2050 3950
Motor Vehicles 2000 1000 1000
8000 3050 4950
Stock
Finished goods 660
Raw materials 220
880
Debtors
Apr 1620
May 1620
Jun 1260 4500
Cash 1240
6620
Creditors
Raw Materials May 400
Raw Materials Jun 320
Fixed Expenses Jun 100 820
Working Capital 5800
Net Assets 10750
Share Capital 7000
Retained profit 3750
Total Capital Employed 10750

Budgeting 4

Manufacturing costs per unit
Material 10.00
Variable labour and overhead 16.00
Fixed overhead per month 210,000.00
Depreciation included in Fixed overhead 54,000
2008 2009
Dec Jan Feb Mar Apr May
Budgeted Sales 14,000 16,000 22,000 17,000 20,000 24,000
Raw material units 1 14,000 16,000 22,000 17,000 20,000 24,000
Closing Stock required:
Finished goods 25% 4,000 5,500 4,250 5,000 6,000
Manufacturing budget (units)
Sales in month 14,000 16,000 22,000 17,000 20,000
Opening stock finished goods 3,500 4,000 5,500 4,250 5,000
10,500 12,000 16,500 12,750 15,000
Closing stock required 4,000 5,500 4,250 5,000 6,000
Manufacture 14,500 17,500 20,750 17,750 21,000
Raw materials closing stock required 10% 1,750 2,075 1,775 2,100
Raw Materials budget (units)
Manufacture in month 14,500 17,500 20,750 17,750
Opening stock raw materials 1,450 1,750 2,075 1,775
13,050 15,750 18,675 15,975
Closing stock required 1,750 2,075 1,775 2,100
Purchase 14,800 17,825 20,450 18,075
Material 175,000 207,500 177,500
Variable labour and overhead 280,000 332,000 284,000
Fixed Overhead 210,000 210,000 210,000
665,000 749,500 671,500
Unit cost 38 36 38
Budgeted Profit and Loss Account Total
£
Sales 58 928,000 1,276,000 986,000 3,190,000
Opening stock of raw material 10 17,500 20,750 17,750 17,500
Purchases 178,250 204,500 180,750 563,500
195,750 225,250 198,500 581,000
Closing stock of raw materials 20,750 17,750 21,000 21,000
Cost of Raw materials consumed 175,000 207,500 177,500 560,000
Variable labour and overhead 280,000 332,000 284,000 896,000
Fixed Overhead 210,000 210,000 210,000 630,000
Manufacturing cost of goods produced 665,000 749,500 671,500 2,086,000
Opening stock of finished goods 40 160,000 209,000 153,512 160,000
825,000 958,500 825,012 2,246,000
Closing stock of finished goods 209,000 153,512 189,155 189,155
Cost of Goods Sold 616,000 804,988 635,857 2,056,845
Gross profit 312,000 471,012 350,143 1,133,155
Variable selling costs 7 112,000 154,000 119,000 385,000
Fixed selling costs 164,000 164,000 164,000 164,000 492,000
Total selling costs 276,000 318,000 283,000 877,000
Budgeted Operating Profit 36,000 153,012 67,143 256,155
Cash pattern for Debtors
Next month 50% 406,000
Same month 50% 464,000
870,000
Cash receipts and payments for January 2009 Jan
£
Inflow: Cash from Debtors 870,000
Outflows:
Cash for materials 148,000
Cash for variable overhead and labour 280,000
Fixed Overhead costs 156,000
Variable selling costs 112,000
Fixed selling costs 164,000
Total Outflows 860,000
Net Inflow of cash £10,000
Reconciliation:
Opening balance sheet End Dec End Jan
Depreciation -54,000
Stocks
FG 160,000 209,000
RM 17,500 20,750
177,500 229,750
Debtors 406,000 464,000
Cash 10,000
583,500 703,750
Creditors 148,000 178,250
Net 435,500 471,500
Capital Employed 435,500 435,500
Profit 0 36,000
Total Capital employed 435,500 471,500