Part II – Observations & Recommendations

Work on Part II using the excel numbers.

Buns Bakery
Basic Information
For Year Ended December 31, Year 2
Tab Colors
Case Data
= Signifies original case assumptions.
Sales Information:
Estimated % drop:
Sales:
20%
Muffins
Estimated Growth:
Cookies
Bread
5%
Estimated Additional Holiday Inc
Sales Numbers Trasposed for Cop
Year 1
Q4
45,000
65,000
85,000
Muffins
Year 2
Q1
Q2
Q3
Q4
36,000
37,800
39,690
47,628
52,000
54,600
57,330
68,796
68,000
71,400
74,970
89,964
43,758
45,946
63,206
66,367
82,654
86,787
Year 3
Q1
Q2
Sales Price/Item:
$6.00
$5.25
$5.75
Q1
36,000
Cookies
Q1
52,000
Bread
Q1
68,000
Collection Information:
Credit Policy:
Cash Sales
Credit Sales
Collection in Current Quarter
Collection in Following Quarter
Collection in 3rd Quarter
Prior Year
Current Year
10%
10% Collection Schedule Transposed for Copying
90%
90%
Q1
Q2
30%
30%
37.0%
40.5%
45%
45%
25%
25%
Collections from Year 1 Sales:
Quarter
Q3
Q4
Sold
$802,000
$1,002,500
$1,804,500
Received
$621,550
$370,925
$992,475
Manufacturing Information:
Ending Inventory Goal:
2.22%
Q1
$180,450
$406,013
$586,463
Payments in Current Year
Q2
Q3
$0
$0
$225,563
$0
$225,563
$0
Buns Bakery
Basic Information
For Year Ended December 31, Year 2
Raw Materials Goal:
15%
Raw Materials:
Muffins
Materials:
Flour
Margarine
Sugar
Eggs
Milk
Mini Chips
Baking Packet
Units
Needed
0.5
0.25
0.5
2
0.1
1
1
Cost per
Unit
$0.15
$0.25
$0.20
$0.05
$1.25
$0.75
$0.10
Total
Cost
$0.08
$0.06
$0.10
$0.10
$0.13
$0.75
$0.10
Cookies
Materials:
Flour
Margarine
Sugar
Eggs
Cocoa
PB chips
Baking Packet
Units
Needed
0.5
0.75
1
2
0.25
1
1
Cost per
Unit
$0.15
$0.25
$0.20
$0.05
$1.50
$0.75
$0.10
Total
Cost
$0.08
$0.19
$0.20
$0.10
$0.38
$0.75
$0.10
Bread
Materials:
Flour
Sugar
Milk
Shortening
Baking Packet
Units
Needed
3
0.25
0.25
0.25
1
Cost per
Unit
$0.15
$0.20
$1.25
$0.50
$0.10
Total
Cost
$0.45
$0.05
$0.31
$0.13
$0.10
Accounts Payable
Payments in Current Quarter
Payments in Following Quarter
Payments in 3rd Quarter
Flour
Margarine
Sugar
Eggs
Milk
Mini Chips
Baking Packet
Cocoa
PB chips
Shortening
Prior Year
Current Year
85%
85%
15%
15%
0%
0%
Payments on Year 1 Purchases:
Quarter
Q3
Q4
Purchases
Paid
Q1
$210,984
$179,337
$31,648
$31,648
Dozens Made
Per 15 Min.
Dozens Made
Per Hour
Hours
Per Dozen
Payments in Current Year
Q2
Q3
NA
$0
$0
Direct Labor:
Cost
Per Dozen
$0
$0
Buns Bakery
Basic Information
For Year Ended December 31, Year 2
Muffins
Mixing
Baking
Packaging
Cookies
Mixing
Baking
Packaging
Bread
Mixing
Baking
Packaging
8
4
2
32
16
8
0.03125
0.06250
0.12500
$0.23
$0.50
$0.81
12
6
2
48
24
8
0.02083
0.04167
0.12500
$0.16
$0.33
$0.81
4
2
8
16
8
32
0.06250
0.12500
0.03125
$0.47
$1.00
$0.20
Variable OH Allocation per DL hr:
$2.00
Fixed OH per Quarter:
Amount of Fixed OH from Depr:
$160,000
8%
Manufacturing OH Information:
Selling and Administrative Expense Information:
Variable S&A for Muffins:
Variable S&A for Cookies:
Variable S&A for Bread:
1%
1%
1%
Fixed S&A Expenses per Quarter:
Advertising
Cleaning Supplies
Janitorial Services
Office Staff Salaries
Office Supplies
Rent – Office
Sales Salaries
Top Management Salaries
Utilities – Office
$0.06
$0.05
$0.06
$40,000
$1,000
$6,000
$25,000
$3,000
$10,000
$35,000
$80,000
$1,800
Cash Flow and Investment Information:
Mortgage
Beginning Balance:
Interest Rate:
Quarterly Interest Rate:
Quarterly Payment:
$1,109,969
6%
1.5%
$20,000
Quarterly Interest Breakdown:
Beginning Bal:
Interest Paid:
Principal Paid:
Q1
$1,109,969
$16,649.54
$3,350.47
Q2
$1,106,619
$16,599.28
$3,400.72
Q3
$1,103,218
$16,548.27
$3,451.73
Q4
$1,099,766
$16,496.49
$3,503.51
Buns Bakery
Basic Information
For Year Ended December 31, Year 2
Requirements
Minimum Cash Balance:
$40,000
Dividends
Required Dividend Payment each Qtr:
$24,000
Planned Expansion (Purchase of Equipment)
Q1
$75,000
Q2
$100,000
Q3
$50,000
Q4
$35,000
Line of Credit Information:
Line of Credit Max:
Interest Rate on LofC:
Quarterly Interest Rate
$1,000,000
8%
2%
Common Stock information:
Original Shares of Stock Outstanding:
New Shares Sold:
Ending Shares of Stock Outstanding
Cash from Sale of Stock:
150,000
50,000
200,000
$400,000
Miscellaneous Information:
The company’s tax rate is:
Last year the company paid taxes of:
Amount of last year’s tax to pay this year:
Total tax to prepay this year:
Payment due each quarter:
30%
$14,000
110%
$15,400
$3,850
Q1
$24,000
Q2
$24,000
Q1
$75,000
Q2
$100,000
Buns Bakery
Basic Information
For Year Ended December 31, Year 2
Due in Week 1
Due in Week 2
Due in Week 3
Due in Week 4
Le Chantilly
Balance Sheet
As of December 31, 2022
timated Additional Holiday Increase:
15%
Assets
umbers Trasposed for Copying
Muffins
Q2
37,800
Q3
39,690
Current Assets
Q4
47,628
Q1
43,758
Q2
45,946
Total Current Assets
Property, Plant, and Equipment
Cookies
Q2
54,600
Q3
57,330
Q4
68,796
Q1
63,206
Q2
66,367
Bread
Q2
71,400
Q3
74,970
Q4
89,964
Q1
82,654
Q2
86,787
Total PPE
Total Assets
Liabilities and Stock Holder’s Equ
ansposed for Copying
Q3
22.5%
Liabilities
Total Liabilities
Stockholder’s Equity
Year
Q4
$0
$0
$0
Total Stockholder’s Equity
$0
Total Liabilities and Stockholder’s Equity
Buns Bakery
Basic Information
For Year Ended December 31, Year 2
$0.15
$0.25
$0.20
$0.05
$1.25
$0.75
$0.10
$1.50
$0.75
$0.50
Year
Q4
$0
$0
DL Wage
Per Hour
Buns Bakery
Basic Information
For Year Ended December 31, Year 2
$7.50 Mixer
$8.00 Baker
$6.50 Packer
$1,096,263
$16,443.94
$3,556.06
Buns Bakery
Basic Information
For Year Ended December 31, Year 2
Q3
$24,000
Q4
$24,000
Q3
$50,000
Q4
$35,000
Buns Bakery
Basic Information
For Year Ended December 31, Year 2
Le Chantilly
Balance Sheet
As of December 31, 2022
Assets
Current Assets
Cash
Accounts Receivable
Raw Materials Inventory
Finished Goods Inventory
Total Current Assets
$40,000
$812,025
$21,098
$13,831
Property, Plant, and Equipment
Land
Building
Equipment
Accumulated Depr – Equipment
Total PPE
$75,000
$568,000
$750,000
($90,000)
$886,954
$1,303,000
Total Assets
$2,189,954
Liabilities and Stock Holder’s Equity
Liabilities
Accounts Payable
Mortgage Payable
Total Liabilities
Stockholder’s Equity
Common Stock (no par value; 150,000 shares
outstanding)
Retained Earnings
Total Stockholder’s Equity
$31,648
$1,109,969
$1,141,617
$150,000
$898,337
$1,048,337
Total Liabilities and Stockholder’s Equity
$2,189,954
2023 Ratios from the Pro Forma financial statements:
EPS:
$9.73
Buns Bakery
Basic Information
For Year Ended December 31, Year 2
CFO: $2,749,459
Profit Margin:
49.4%
10% bonus: ($172,038)
Sales Budget
Q1
Q2
Quarter
Q3
Q4
Total
2023
Muffins
Budgeted Sales
Selling price per dozen
Total Sales Revenue from Muffins
36,000
$6.00
$216,000
37,800
$6.00
$226,800
39,690
$6.00
$238,140
47,628
$6.00
$285,768
161,118
$6.00
$966,708
Cookies
Budgeted Sales
Selling price per dozen
Total Sales Revenue for Cookies
52,000
$5.25
$273,000
54,600
$5.25
$286,650
57,330
$5.25
$300,983
68,796
232,726
$5.25
$5.25
$361,179 $1,221,812
Bread
Budgeted Sales
Selling price per loaf
Total Sales Revenue for Bread
68,000
$5.75
$391,000
71,400
$5.75
$410,550
74,970
$5.75
$431,078
89,964
304,334
$5.75
$5.75
$517,293 $1,749,921
Total Sales Revenue
$880,000
$924,000
$970,200 $1,164,240 $3,938,440
Schedule of Cash Collections
Collections from last year
Q1
Q2
Q3
Q4
Total Cash Collections
Q1
$586,463
$325,600
$0
$0
$0
$912,063
Quarter
Total
Q2
Q3
Q4
$225,563
$0
$0
$812,025
$356,400
$198,000
$0
$880,000
$341,880
$374,220
$207,900
$924,000
$358,974
$392,931
$751,905
$0
$430,769
$430,769
$923,843
$931,194 $1,031,600 $3,798,699
2024
Q1
Q2
43,758
$6.00
$262,549
45,946
$6.00
$275,677
63,206
$5.25
$331,833
66,367
$5.25
$348,425
82,654
$5.75
$475,263
86,787
$5.75
$499,026
$1,069,646 $1,123,128
Balance
$0
$0
$0
$218,295
$733,471
$951,766
Production Budget
Q1
Q2
Quarter
Q3
Q4
Total
20×1
Muffins
Budgeted Sales
Desired Ending Inventory
Total Units Needed
Less: Beginning Inventory
Required Muffin Production
36,000
840
36,840
(800)
36,040
37,800
882
38,682
(840)
37,842
39,690
1,058
40,748
(882)
39,866
47,628
972
48,600
(1,058)
47,542
161,118
972
162,090
(800)
161,290
Cookies
Budgeted Sales
Desired Ending Inventory
Total Units Needed
Less: Beginning Inventory
Required Cookie Production
52,000
1,213
53,213
(1,156)
52,058
54,600
1,274
55,874
(1,213)
54,661
57,330
1,529
58,859
(1,274)
57,585
68,796
1,405
70,201
(1,529)
68,672
232,726
1,405
234,131
(1,156)
232,975
Bread
Budgeted Sales
Desired Ending Inventory
Total Units Needed
Less: Beginning Inventory
Required Bread Production
68,000
1,587
69,587
(1,511)
68,076
71,400
1,666
73,066
(1,587)
71,479
74,970
1,999
76,969
(1,666)
75,303
89,964
1,837
91,801
(1,999)
89,802
304,334
1,837
306,171
(1,511)
304,660
20×2
Q1
43,758
1,021
44,779
(972)
43,807
45,946
63,206
1,475
64,681
(1,405)
63,277
66,367
82,654
1,929
84,583
(1,837)
82,746
86,787
Raw Materials Budget
Required Production
Muffins
Cookies
Bread
Raw Materials Needed
Muffins
Flour
Margarine
Sugar
Eggs
Milk
Mini Chips
Baking Packet
Cookies
Flour
Margarine
Sugar
Eggs
Cocoa
PB chips
Baking Packet
Bread
Flour
Sugar
Milk
Shortening
Baking Packet
Totals
Flour
Margarine
Sugar
Eggs
Milk
Mini Chips
Baking Packet
Cocoa
PB chips
Shortening
Desired Ending Inventory
Flour
Margarine
Sugar
Eggs
Milk
Q1
Quarter
Q2
Q3
Q4
Total
20×1
36,040
52,058
68,076
37,842
54,661
71,479
39,866
57,585
75,303
47,542
68,672
89,802
161,290
232,975
304,660
18,020
9,010
18,020
72,080
3,604
36,040
36,040
18,921
9,461
18,921
75,684
3,784
37,842
37,842
19,933
9,967
19,933
79,733
3,987
39,866
39,866
23,771
11,886
23,771
95,084
4,754
47,542
47,542
80,645
40,323
80,645
322,581
16,129
161,290
161,290
26,029
39,043
52,058
104,116
13,014
52,058
52,058
27,330
40,996
54,661
109,321
13,665
54,661
54,661
28,792
43,189
57,585
115,170
14,396
57,585
57,585
34,336
51,504
68,672
137,344
17,168
68,672
68,672
116,488
174,731
232,975
465,950
58,244
232,975
232,975
204,227
17,019
17,019
17,019
68,076
214,438
17,870
17,870
17,870
71,479
225,910
18,826
18,826
18,826
75,303
269,405
22,450
22,450
22,450
89,802
913,979
76,165
76,165
76,165
304,660
248,276
48,053
87,097
176,196
20,623
36,040
156,173
13,014
52,058
17,019
260,689
50,456
91,452
185,005
21,654
37,842
163,982
13,665
54,661
17,870
274,635
53,155
96,344
194,902
22,812
39,866
172,754
14,396
57,585
18,826
327,512
63,389
114,893
232,428
27,205
47,542
206,015
17,168
68,672
22,450
1,111,112
215,054
389,785
788,531
92,294
161,290
698,925
58,244
232,975
76,165
39,103
7,568
13,718
27,751
3,248
41,195
7,973
14,452
29,235
3,422
49,127
9,508
17,234
34,864
4,081
45,267
8,761
15,880
32,125
3,760
45,267
8,761
15,880
32,125
3,760
Mini Chips
Baking Packet
Cocoa
PB chips
Shortening
5,676
24,597
2,050
8,199
2,680
5,980
25,913
2,159
8,638
2,824
7,131
30,902
2,575
10,301
3,368
6,571
28,474
2,373
9,491
3,103
6,571
28,474
2,373
9,491
3,103
Total Needs
Flour
Margarine
Sugar
Eggs
Milk
Mini Chips
Baking Packet
Cocoa
PB chips
Shortening
287,379
55,622
100,814
203,946
23,871
41,716
180,771
15,064
60,257
19,699
301,885
58,429
105,903
214,241
25,076
43,822
189,895
15,825
63,298
20,694
323,762
62,664
113,578
229,767
26,893
46,998
203,657
16,971
67,886
22,193
372,779
72,151
130,773
264,553
30,965
54,113
234,490
19,541
78,163
25,553
1,156,379
223,815
405,665
820,656
96,054
167,861
727,400
60,617
242,467
79,268
Less: Beginning Inventory
Flour
Margarine
Sugar
Eggs
Milk
Mini Chips
Baking Packet
Cocoa
PB chips
Shortening
(37,241)
(7,208)
(13,065)
(26,429)
(3,093)
(5,406)
(23,426)
(1,952)
(7,809)
(2,553)
(39,103)
(7,568)
(13,718)
(27,751)
(3,248)
(5,676)
(24,597)
(2,050)
(8,199)
(2,680)
(41,195)
(7,973)
(14,452)
(29,235)
(3,422)
(5,980)
(25,913)
(2,159)
(8,638)
(2,824)
(49,127)
(9,508)
(17,234)
(34,864)
(4,081)
(7,131)
(30,902)
(2,575)
(10,301)
(3,368)
(37,241)
(7,208)
(13,065)
(26,429)
(3,093)
(5,406)
(23,426)
(1,952)
(7,809)
(2,553)
Raw Materials to be Purchased
Flour
Margarine
Sugar
Eggs
Milk
Mini Chips
Baking Packet
Cocoa
PB chips
Shortening
250,138
48,414
87,750
177,517
20,778
36,310
157,345
13,112
52,448
17,147
262,781
50,861
92,185
186,490
21,828
38,146
165,298
13,775
55,099
18,013
282,567
54,690
99,126
200,531
23,471
41,018
177,744
14,812
59,248
19,369
323,652
62,642
113,539
229,688
26,884
46,982
203,588
16,966
67,863
22,186
1,119,137
216,607
392,601
794,227
92,961
162,455
703,974
58,664
234,658
76,715
Cost per unit of Raw Materials
Flour
Margarine
Sugar
Eggs
Milk
Mini Chips
Baking Packet
$0.15
$0.25
$0.20
$0.05
$1.25
$0.75
$0.10
$0.15
$0.25
$0.20
$0.05
$1.25
$0.75
$0.10
$0.15
$0.25
$0.20
$0.05
$1.25
$0.75
$0.10
$0.15
$0.25
$0.20
$0.05
$1.25
$0.75
$0.10
$0.15
$0.25
$0.20
$0.05
$1.25
$0.75
$0.10
Cocoa
PB chips
Shortening
$1.50
$0.75
$0.50
Cost of Raw Materials to be purchased
Flour
Margarine
Sugar
Eggs
Milk
Mini Chips
Baking Packet
Cocoa
PB chips
Shortening
Total Cost
$37,521
$12,103
$17,550
$8,876
$25,972
$27,233
$15,734
$19,668
$39,336
$8,573
$212,567
$1.50
$0.75
$0.50
$1.50
$0.75
$0.50
$1.50
$0.75
$0.50
$1.50
$0.75
$0.50
$39,417
$42,385
$48,548
$12,715
$13,673
$15,661
$18,437
$19,825
$22,708
$9,324
$10,027
$11,484
$27,285
$29,339
$33,605
$28,609
$30,763
$35,236
$16,530
$17,774
$20,359
$20,662
$22,218
$25,448
$41,324
$44,436
$50,897
$9,007
$9,685
$11,093
$223,311 $240,125 $275,039
$167,871
$54,152
$78,520
$39,711
$116,201
$121,842
$70,397
$87,997
$175,993
$38,358
$951,041
Schedule of Cash Payments
Accounts Payable, 1/01/20×1
Q1
Q2
Q3
Q4
Total Cash Paid for Materials
Q1
$31,648
$180,682
$0
$0
$212,329
Quarter
Q2
Q3
$0
$0
$31,885
$0
$189,814
$33,497
$0 $204,106
Total
Q4
$0
$0
$0
$36,019
$233,783
$221,699 $237,603 $269,802
$31,648
$212,567
$223,311
$240,125
$233,783
$941,433
20×2
Q1
43,807
63,277
82,746
21,903
10,952
21,903
87,614
4,381
43,807
43,807
31,638
47,457
63,277
126,553
15,819
63,277
63,277
248,239
20,687
20,687
20,687
82,746
301,780
58,409
105,867
214,167
25,067
43,807
189,830
15,819
63,277
20,687
45,946
$0
$0
$0
$0
$41,256
$41,256
Direct Labor Budget
Quarter
Required Production
Muffins
Cookies
Bread
DL hours per Unit
Muffins
Mixing
Baking
Packaging
Cookies
Mixing
Baking
Packaging
Bread
Mixing
Baking
Packaging
Total DL hours needed
Muffins
Mixing
Baking
Packaging
Cookies
Mixing
Baking
Packaging
Bread
Mixing
Baking
Packaging
Total
Mixing
Baking
Packaging
DL Cost Per Hour
Mixing
Baking
Packaging
DL Cost
Mixing
Baking
Packaging
Total DL Cost
Total
Q1
Q2
Q3
Q4
36,040
52,058
68,076
37,842
54,661
71,479
39,866
57,585
75,303
47,542
68,672
89,802
161,290
232,975
304,660
0.031
0.063
0.125
0.031
0.063
0.125
0.031
0.063
0.125
0.031
0.063
0.125
0.125
0.250
0.500
0.021
0.042
0.125
0.021
0.042
0.125
0.021
0.042
0.125
0.021
0.042
0.125
0.083
0.167
0.500
0.063
0.125
0.031
0.063
0.125
0.031
0.063
0.125
0.031
0.063
0.125
0.031
0.250
0.500
0.125
1,126
2,253
4,505
1,183
2,365
4,730
1,246
2,492
4,983
1,486
2,971
5,943
5,040
10,081
20,161
1,085
2,169
6,507
1,139
2,278
6,833
1,200
2,399
7,198
1,431
2,861
8,584
4,854
9,707
29,122
4,255
8,509
2,127
4,467
8,935
2,234
4,706
9,413
2,353
5,613
11,225
2,806
19,041
38,082
9,521
6,466
12,931
13,140
32,536
6,789
13,578
13,797
34,163
7,152
14,304
14,535
35,991
8,529
17,058
17,333
42,920
28,935
57,870
58,804
145,609
$7.50
$8.00
$6.50
7.50
8.00
6.50
7.50
8.00
6.50
7.50
8.00
6.50
7.50
8.00
6.50
$48,491
$103,448
$85,407
$237,347
$50,916
$108,621
$89,678
$249,214
$53,640
$114,431
$94,475
$262,546
$63,967
217,014
$136,463
462,963
$112,665
382,225
$313,095 $1,062,202
Manufacturing Overhead Budget
Budgeted Direct Labor hours
Variable Overhead rate
Total Variable Overhead
Fixed Overhead
Total Overhead
Q1
32,536
$2.00
$65,072
$160,000
$225,072
Quarter
Q2
Q3
34,163
35,991
$2.00
$2.00
$68,326
$71,981
$160,000
$160,000
$228,326
$231,981
Total
Q4
42,920
$2.00
$85,840
$160,000
$245,840
Total Manufacturing Overhead
Budgeted Direct Labor Hours
Predetermined Overhead Rate
145,609
$2.00
$291,219
$640,000
$931,219
$931,219
145,609
$6.40
Schedule of Cash Payments for Manufacturing Overhead
Total Overhead
Less: Depreciation and Amortization
Cash Payments for Overhead
Q1
$225,072
($12,800)
$212,272
Quarter
Q2
Q3
$228,326
$231,981
($12,800)
($12,800)
$215,526
$219,181
Total
Q4
$245,840
($12,800)
$233,040
$931,219
($51,200)
$880,019
Ending Inventory Budget
Item
Production Cost per Dozen Muffins
Direct Materials
Flour
Margarine
Sugar
Eggs
Milk
Mini Chips
Baking Packet
Total DM
Quantity
0.5
0.3
0.5
2.0
0.1
1.0
1.0
Cost
$0.15
$0.25
$0.20
$0.05
$1.25
$0.75
$0.10
each
each
each
each
each
each
each
Direct Labor
Mixing
Baking
Packaging
Total DM
0.031
0.063
0.125
$7.50 per hour
$8.00 per hour
$6.50 per hour
OH
0.219 hours
$6.40 per DL hour
Unit Production Cost
Budgeted Finished Goods Inventory – Muffins
Ending Finished Goods Inventory
Unit Production Costs
Ending finished goods inventory in dollars
Production Cost per Dozen Cookies
Direct Materials
Flour
Margarine
Sugar
Eggs
Cocoa
PB chips
Baking Packet
Total DM
Total
$0.08
$0.06
$0.10
$0.10
$0.13
$0.75
$0.10
$1.31
$0.23
$0.50
$0.81
$1.55
$1.40
$4.2584
972
$4.26
$4,141
0.5
0.75
1
2
0.25
1
1
0.15 each
0.25 each
0.2 each
0.05 each
1.5 each
0.75 each
0.1 each
$0.08
$0.19
$0.20
$0.10
$0.38
$0.75
$0.10
$1.79
Direct Labor
Mixing
Baking
Packaging
Total DM
0.021
0.042
0.125
$7.50 per hour
$8.00 per hour
$6.50 per hour
$0.16
$0.33
$0.81
$1.30
OH
0.188 hours
$6.40 per DL hour
Unit Production Cost
Ending Finished Goods Inventory
Unit Production Costs
Ending finished goods inventory in dollars
Production Cost per Dozen Loaves of Bread
Direct Materials
Flour
3
Sugar
0.25
Milk
0.25
Shortening
0.25
Baking Packet
1
Total DM
1,405
$4.29
$6,024
0.15 each
0.2 each
1.25 each
0.5 each
0.1 each
$0.45
$0.05
$0.31
$0.13
$0.10
$1.0375
$0.47
$1.00
$0.20
$1.67
Direct Labor
Mixing
Baking
Packaging
Total DM
0.063
0.125
0.031
$7.50 per hour
$8.00 per hour
$6.50 per hour
OH
0.219 hours
$6.40 per DL hour
Unit Production Cost
Ending Finished Goods Inventory
Unit Production Costs
Ending finished goods inventory in dollars
Total Ending Inventory
$1.20
$4.2887
$1.40
$4.1084
1,837
$4.11
$7,546
$17,711
Selling and Administrative Budget
Quarter
Total
Q1
Q2
Q3
Q4
Budgeted sales (by Item)
Muffins
Cookies
Bread
36,000
52,000
68,000
37,800
54,600
71,400
39,690
57,330
74,970
47,628
68,796
89,964
161,118
232,726
304,334
Variable S&A Expense
Muffins
Cookies
Bread
$0.06
$0.05
$0.06
$0.06
$0.05
$0.06
$0.06
$0.05
$0.06
$0.06
$0.05
$0.06
$0.06
$0.05
$0.06
Total Variable S&A
Muffins
Cookies
Bread
Total Variable S&A
$2,160
$2,730
$3,910
$8,800
$2,268
$2,867
$4,106
$9,240
$2,381
$3,010
$4,311
$9,702
$2,858
$3,612
$5,173
$11,642
$9,667
$12,218
$17,499
$39,384
Budgeted Fixed S&A Expenses
Advertising
Cleaning Supplies
Janitorial Services
Office Staff Salaries
Office Supplies
Rent – Office
Sales Salaries
Top Management Salaries
Utilities – Office
Total Budgeted Fixed S&A Expense
$40,000
$40,000
$40,000
$40,000
$1,000
$1,000
$1,000
$1,000
$6,000
$6,000
$6,000
$6,000
$25,000
$25,000
$25,000
$25,000
$3,000
$3,000
$3,000
$3,000
$10,000
$10,000
$10,000
$10,000
$35,000
$35,000
$35,000
$35,000
$80,000
$80,000
$80,000
$80,000
$1,800
$1,800
$1,800
$1,800
$201,800 $201,800 $201,800 $201,800
$160,000
$4,000
$24,000
$100,000
$12,000
$40,000
$140,000
$320,000
$7,200
$807,200
Total S&A Expense
$210,600 $211,040 $211,502 $213,442
$846,584
Cash Budget
Cash Balance, Beginning
Add Receipts:
Cash Collected from Customers
Total Cash Available
Q1
$40,000
Quarter
Q2
Q3
$40,000
$40,000
$912,063
$952,063
$923,843
$963,843
Less: Manufacturing Outflows
Direct Materials
Direct Labor
Variable OH
Fixed OH
Total Manufacturing Outflows
($212,329)
($237,347)
($65,072)
($160,000)
($674,748)
Less: Selling & Administrative Outflows
Variable Selling and Administrative
Fixed Selling and Administrative
Total Selling and Administrative Outflows
Total
Q4
$88,150
$40,000
$931,194
$971,194
$1,031,600
$88,150
$3,798,699
$3,838,699
($221,699)
($249,214)
($68,326)
($160,000)
($699,239)
($237,603)
($262,546)
($71,981)
($160,000)
($732,130)
($269,802) ($941,433)
($313,095) ($1,062,202)
($85,840) ($291,219)
($160,000) ($640,000)
($828,736) ($2,934,854)
($8,800)
($201,800)
($210,600)
($9,240)
($201,800)
($211,040)
($9,702)
($201,800)
($211,502)
($11,642)
($201,800)
($213,442)
Total Cash Outflows from Operations
Cash Available from Normal Operations
($885,348)
$66,714
($910,279)
$53,563
($943,632)
$27,562
Less: Investments
Equipment Purchases
Cash Available after Investments
($75,000)
($8,286)
($100,000)
($46,437)
($50,000)
($22,438)
($35,000)
($989,029)
($260,000)
($202,739)
($3,850)
($3,850)
($3,850)
($3,850)
($15,400)
($16,650)
($3,350)
$0
($24,000)
($47,850)
($56,136)
($16,599)
($3,401)
$0
($24,000)
($47,850)
($94,287)
($16,548)
($3,452)
$400,000
($24,000)
$352,150
$329,712
($16,496)
($3,504)
$0
($24,000)
($47,850)
($1,036,879)
($66,294)
($13,706)
$400,000
($96,000)
$208,600
$5,861
$0
$1,923
$96,136
$4,608
$230,422
$4,608
$0
$21,538
$32,677
$96,136
$134,287
$0
$1,076,879
$1,307,301
$0
$0
$40,000
$0
$0
$40,000
($230,422)
($11,140)
$88,150
$0
$0
$40,000
($230,422)
($11,140)
$1,071,600
Miscellaneous Cash Flows
Income Taxes
Mortgage Payments
Interest
Principal
Sale of Stock, issue of other debt, etc.
Dividends
Total Miscellaneous Cash Flows
Total Cash before Line of Credit
Line of Credit
Beginning Loan Balance
Interest (accumulated)
Loans
Repayments
Principal
Interest
Ending Cash Balance
($39,384)
($807,200)
($846,584)
($1,042,179) ($3,781,438)
($954,029)
$57,261
Le Chantilly
Pro Forma Income Statement
For Year Ended December 31, 2023
Sales
Cost of Goods Sold*
Gross Profit
$3,938,440
($2,923,212)
$1,015,228
Less Selling and Administrative Expenses
Income before Interest and Taxes
$846,584
Interest Expense*
Income before Taxes
$98,971
Income Taxes Expense
Net Income
($15,400)
$1,861,812
$1,960,783
$1,945,383
EPS
$9.73
*Accompanying Calculations
Calculating COGS
Beginning FG Inventory
Plus: Direct Materials
Beginning Raw Materials Inv.
Plus: Purchased
Less: Ending Raw Materials Inv.
(See below)
Total DM
Plus: Direct Labor
Plus: Manufacturing Overhead
Total Cost of Goods Available for Sale
Less: Ending Inventory
COGS
$1,062,202
$931,219
$2,940,923
($17,711)
$2,923,212
Calculating Ending Direct Materials Inventory
Ingredient
Units on Hand Cost per Unit
Flour
45,267
$0.15
Margarine
8,761
$0.25
Sugar
15,880
$0.20
Eggs
32,125
$0.05
Milk
3,760
$1.25
Mini Chips
6,571
$0.75
Baking Packet
28,474
$0.10
Cocoa
2,373
$1.50
PB chips
9,491
$0.75
Shortening
3,103
$0.50
Total Ending Direct Materials Inv.
Total Cost
$6,790
$2,190
$3,176
$1,606
$4,700
$4,928
$2,847
$3,559
$7,119
$1,551
$38,468
Calculating Interest Expense
Line of Credit interest accrued
Mortgage interest accrued
Total Interest Expense
$13,831
$21,098
$951,041
($38,468)
$32,677
$66,294
$98,971
Le Chantilly
Pro Forma Balance Sheet
As of December 31, 2023
Assets
Current Assets
Cash
Accounts Receivable
Raw Materials Inventory
Finished Good Inventory
Total Current Assets
$1,071,600
$733,471
$38,468
$17,711
Property, Plant, and Equipment
Land
Building
Equipment
Accumulated Depr – Building and Equipment
Total PPE
$75,000
$568,000
$1,010,000
($141,200)
$1,861,250
$1,511,800
Total Assets
$3,373,050
Liabilities and Stock Holder’s Equity
Current Liabilities
Accounts Payable
Interest Payable
Income Tax Payable
Total Current Liabilities
$41,256
$98,971
$15,400
$155,627
Long-term Liabilities
Bonds Payables
Loan Payable
Total Long-term Liabilities
Total Liabilities
$0
$230,422
$230,422
$386,049
Stockholder’s Equity
Common Stock (200,000 shares outstanding)
Retained Earnings
Total Stockholder’s Equity
$200,000
$2,747,720
$2,947,720
Total Liabilities and Stockholder’s Equity
Beginning
$750,000
$3,333,769
Q1
Q2
Q3
Q4
Ending
$75,000
$100,000
$50,000
$35,000
$1,010,000
Beginning
($90,000)
($51,200)
Ending
($141,200)
($39,280)
Le Chantilly
Pro Forma Statement of Cash Flows
For Year Ended December 31, 2023
Cash Flows from Operating Activities
Net Income
Adjustments:
Change in Accounts Receivable*
Change in Raw Materials Inventory*
Change in Finished Good Inventory*
Change in Accounts Payable*
Change in Interest Payable*
Change in Income Tax Payable*
Depreciation
Total Cash Flows from Operating Activities
$1,945,383
$733,471
$38,468
$17,711
$41,256
$98,971
$15,400
($141,200)
Cash Flows from Investing Activities
Cash Paid for Equipment
Total Cash Flows from Investing Activities
($260,000)
Cash Flows from Financing Activities
Cash Received from Loans
Cash Paid on Loans and Mortgage
Cash Received from Sale of Stock
Cash Paid as Dividends
Total Cash Flows from Financing Activities
$32,677
($321,562)
$400,000
($96,000)
Total Change in Cash
Cash Balance, 1/1
Cash Balance, 12/31
$804,076
$2,749,459
($260,000)
$15,115
$2,504,574
$40,000
$2,544,574
Part II – Observations & Recommendations
The venture capital firm express particular interest in the EPS, cash flow from operations, and profit margin. Five s
Prepare a What-If Analysis of each scenario and discuss if each one should be pursued.
(In the past, the master budget file was copied to create a separate workbook for each scenario.)
For each scenario, what it entails and make that change to the appropriate workbook. All that is needed is to make
All spreadsheets will be automatically updated and that’s the beauty of constructing a budget like this for the entire company.
State the change to the EPS, Profit Margin, and Cash Flow for each scenario compared to the orignal budget.
What is your recommendation for management?
State the change to the EPS, Profit Margin, and Cash Flow for each scenario compared to the orignal budget.
For each scenario, what it entails and make that change to the appropriate workbook. All that is needed is to make the change
s, and profit margin. Five separate scenarios were proposed to improve these metrics.