Managerial accounting master budget

profilepulzx2d4
81554_1407744_Ch.8ExcelwithHWCanvas.xlsx
Home>Business & Finance homework help>Accounting homework help>Managerial accounting master budget

CH.8 Excel

Doing calendar Q2
Doing calendar Q2
Doing calendar Q2
Doing calendar Q2
Doing calendar Q2
Doing calendar Q2
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Units Sales 20,000 50,000 30,000 25,000 15,000 32,000 36,000 42,000 66,000
Price each $ 10.00 $ 10.00 $ 10.00 $ 10.00 $ 10.00 $ 10.00
Budgeted Sales 200,000 500,000 300,000 250,000 150,000 320,000
Period ending cash CASH 90,000 30% uncollected
Collections 75,000 25%/30% of end Q1 A/R will be collected
70% of current priod 175,000 105,000 224,000
25% of prior period 62,500 37,500
Sum Cash collected 250,000 167,500 261,500 679,000 679,000
Looking at Q2
Ending inventory units 20% Mar Apr May Jun Jul Aug
Units Sales 30,000 25,000 15,000 32,000 36,000 42,000
Budget Ending Inventory 4,000 3,000 6,400 7,200 8,400 [20% next mo. Sales]
Sales + Ending Given 28,000 21,400 39,200 44,400
Less Beginning = prior month-end (4,000) (3,000) (6,400) (7,200)
= Unit Production 24,000 18,400 32,800 37,200
Cost/Lb Cost/Unit
Quantity per unit in Lbs. 5.00 $ 0.40 $ 2.00 Ending Inventory % next month 10% Looking at Q2
Mar Apr May Jun Jul
= Unit Production - 0 24,000 18,400 32,800 37,200
Required for Production/Lbs - 0 120,000 92,000 164,000 186,000
$s Into FG for Production $ 48,000 $ 36,800 $ 65,600 $ 150,400 Qtr. Total
Budget Ending Inventory 13,000 9,200 16,400 18,600 10% of following Month
Sales + Ending 129,200 108,400 182,600
Less Beginning = prior month-end (13,000) (9,200) (16,400)
Qty. Purchase [additions] of Raw material 116,200 99,200 166,200 Material budget
$s. Purchase of Raw material $ 46,480 $ 39,680 $ 66,480 Material budget $ 0.40 per lb.
Apr Apr.
CASH Ending A/P $ 12,000 $ 12,000 Beg $ 12,000
Cr. To A/P = purchases $ 46,480 $ 39,680 $ 66,480 $ 46,480 Add $ 46,480
Pay 50% current [given company policy] $ 23,240 $ 19,840 $ 33,240 1/2 April $ (23,240) Paid $ (35,240)
Pay prior $ 12,000 $ 23,240 $ 19,840 Cash budget $ 35,240 $ 23,240
Total paid $ 35,240 $ 43,080 $ 53,080 Cash budget $ 131,400 Paid A/P Ending
Ending A/P [Beginning + Additions - payments] $ 23,240 $ 19,840 $ 33,240 Qtr. Total
Guaranteed Hours Rate
Payment for quarter 1500 $ 10.00
Required Hrs. per unit 0.05 3 minutes
DL$s. per unit $ 0.50 700
used for ending Q2 inventory
Apr May Jun Qtr sum
= Unit Production 24,000 18,400 32,800 75,200
HRs of Prodctn. at Required per unit of 0.05 hrs. ea. 1,200 920 1,640 3,760
DL Cost of production into units at 10 per Hr $ 12,000 $ 9,200 $ 16,400 37,600
unfavorable variance of $ 8,800
Hrs paid 1,500 1,500 1,640 4,640 880 Hours
$s paid $ 15,000 $ 15,000 $ 16,400 46,400
ADDED
Productivity at budget earned HRs/paid HRs 80% 61% 100% Spend > Used by $ 8,800 into CoGS
*******
Variable OH $s per HR $ 20.00 Given: rate is per DL hr.
Required Hrs. per unit 0.05 Hrs. per unit 3 minutes
Variable OH $s per unit $ 1.00
Fxd. MOH per month $50,000
Non cash MOH $20,000
Cash Mfg. OH $30,000
Actual Overhead rates NOT predetermined rates
Apr May Jun Qtr. Sum Required Hrs. per unit 0.05
= Unit Production 24,000 18,400 32,800 75,200 Fxd. OH spending $50,000 $50,000 $50,000
# Hrs. 1,200 920 1,640
HRs of Prodctn. at Required per unit of 0.05 hrs. ea. 1,200 920 1,640 3,760 per Hr. 41.67 54.35 30.49
VOH Cost of production at $20 per DL Hr $24,000 $18,400 $32,800 per unit 2.08 2.72 1.52
Fixed manufacturing OH per period $50,000 $50,000 $50,000 $ 150,000 ADDED
Fxd. + Var. Mfg. OH Total MOH per Month $74,000 $68,400 $82,800 $ 225,200 $$$$ QTY $ 225,200
Fxd. Mfg. OH rate/hr. $ 41.67 $ 54.35 $ 30.49 39.89 $ 150,000 3,760 3,760
Fxd. Mfg. OH unit $ 2.08 $ 2.72 $ 1.52 $ 1.99 0.05 hrs. per unit 59.89
Fxd. Mfg. OH rate/hr. Quarter averageò Apr May Jun per Hr.
Budgeted MOH rate per period Fxd.+ Var. 61.67 74.35 50.49 59.89 Fxd + Var $ 41.67 $ 54.35 $ 30.49 Fxd rate
$ 20.00 $ 20.00 $ 20.00 V. Rate
0 Non-cash expense ($20,000) ($20,000) ($20,000) $ 61.67 $ 74.35 $ 50.49
Cash MOH $ 54,000 $ 48,400 $ 62,800 $ 165,200
Qtr Total
Product Cost using Qtr. Average Fxd.unit Variable Fixed
0.40 $5.00 Materials $ 2.00 5 lbs $0.40/lb
0.05 $10.00 DL $ 0.50 This example: Std. Hrs./unit $ 10
0.05 $20.00 V Mfg. OH $ 1.00 0.05
0.05 $59.89 F MFG. OH 1.99 This example: Actual rate for Qtr. $ 0.50
Sum $ 3.50 $ 1.99
$ 5.49 average per unit for Qtr
Product cost without labor variance
CoGS chart Qty 4,000 No WIP in Example
given $ 5.49 $ 21,979 Beginning Materials
Excel C Beginning FG $ 22,000 4,000 Excel B Beginning FG
For Inome Statement
+ Input addtions
Excel C Materials $ 150,400 CoGS =
Excel D Labor $ 46,400 with variance + Beginning
Excel E Overhead $ 225,200 75,200 Excel B + Additions
Total $ 422,000 - Ending Ending Inventory Q2
Average per FG unit 5.49 Excel E = CoGS
$39,562 $ 0.40 per lb.
- Ending 7,200 (39,562) 7,200 Excel B End Qty. 7,200 FG 18,600 # lbs.
= CoGS $ 404,438 Each 5.49 End $s RM $ 7,440 $47,002
$47,002 total Inv.
Variable unit period cost $ 0.50
Fixed period costs $ 70,000
Non cash expenses $ 10,000
Cash Expense $ 60,000
Selling and Administrative
Period Costs
Apr May Jun Qtr. Sum
Units Sales 25,000 15,000 32,000 72,000
Variable Period costs/unit sold $ 0.50 $ 0.50 $ 0.50
Variable unit period expenses 12,500 7,500 16,000 36,000
Fixed Period Expense $ 70,000 $ 70,000 $ 70,000 210,000
Total 82,500 77,500 86,000 246,000 to Income statement
Non cash portion (10,000) (10,000) (10,000) (30,000)
Cash Period Expense 72,500 67,500 76,000 216,000 CASH
Example uses the Direct Method of Receipts and Disbusrsement
for Cash Budgets; large companies use the Balance Ssheet Indirect method
We'll assume the debt exists for full quarter; borrowing may be drwn down as needed
and result is different result
Target Minimum Cash Balance $10,000 given
Quarter June 30
Beginning Cash Balance $ 40,000 Given
+ Collections $ 679,000 Excel A
Cash avaialble $ 719,000
Cash disbursements:
Materials $ 131,400 Excel C
Direct labor $ 46,400 Excel D
Mfg. Overhead=Fxd. + Var - Non-cash $ 165,200 Excel E required without interest $ 35,000
Selling/Admin. $ 216,000 Excel G Borrowing $ 19,000 Average borrowing given
Equipment Purchased $ 125,000 Given data # months 3
Interest $ 285 6% interest $ 285 6%
Total $ 684,285
Management judged Cash balance adequate to operate did not pay down debt
Cash Balance $ 34,715 Could reduce Cash or change borrowing
Debt on BS $ - 0 Excel K Below Can balance BS with CASH or With Borrowing
Royal Company
Statement of Income
QE: 6/30 GAAP, FAC
Sales 720,000 100.0% Excel A
Less: Cost of Goods Sold $ 404,438 56.2% Excel F
Gross Margin $ 315,562 43.8%
Selling & Admin, Expense 246,000 34.2% Excel G
Operating Income 69,562 9.7%
Interest Expense $ 285 0.0%
Income before taxes $ 69,847 9.7%
Beg Cash $ 40,000
Royal Company Period Cash $ 34,715
Month ending 6/30 $ 74,715
Balance Sheet
Assets debt to Balance
Cash $ 74,715 Excel H Keep Cash 392,717 Assets
sold 320,000 Accounts receivable 96,000 Excel A $ (33,240)
70% collected (224,000) Inventory 47,002 Excel F $ (200,000)
30% not collected 96,000 Land 50,000 Given $ (156,422)
Equipment 125,000 Given 3,055
Statement of Retained Earnings Total assets 392,717
Beginning $ 86,575
less: Dividends 0 Liabilities & Stockholders' Equity
Plus: Income $ 69,847 Accounts Payable $ 33,240 Excel C
Ending Retained Earnings $ 156,422 Long term debt $ 3,055 Excel H Keep Cash Back into to Balance
Common stock $ 200,000 Given
Retained Earnings $ 156,422 ççright
Total Liabilities & Stockholders' Equity $ 392,717
Minimize cash pay debt: balance with cash Cash $ 71,660 Accounts Payable $ 33,240 debt to Balance
Accounts receivable $ 96,000 Common stock $ 200,000 $ 389,662 assets
Inventory $ 47,002 Retained Earnings $ 156,422 $ (33,240)
Land $ 50,000 $ 389,662 $ (200,000)
Equipment $ 125,000 Debt to balance $ - 0 $ (156,422) pay down debt to -0-
$ 389,662 $ 389,662 $ - 0

Excel A Sales Budget

Excel D Direct Labor

Excel E Manufacturing Overhead

Excel F CoGS

Excel G S&A Expense

Excel B FG budget

Excel H Cash

Excel I Statement of Income

Excel J // Balance Sheet

2

1

5

3

6

7

4

8

9

10

Excel C Materials

invested capital

Sheet2

Sheet3