Acct 2220 Zeigler: Solution to Chp 7 Budget Case Study

Comprehensive Master Budgeting Problem

Acct 2220: Chp 7 Case Study - Workpapers (Pg 1/4)

A. Sales Budget

B. Schedule of Cash Receipts

C. Pro Forma Financial Stmt information

Estimated Sales Growth Rate > / 10% per month
Sales Budget
Pro Forma
A) Projected Sales / Jan / Feb / Mar / Stmt @3/31
Cash Sales / 100,000 / 110,000 / 121,000 / xxxxxx
Sales on Account / 300,000 / 330,000 / 363,000 / 363,000 (1)
Total Budgeted Sales / 400,000 / 440,000 / 484,000 / 1,324,000 (2)
B) Sch of Cash Receipts
Current Month’s Cash Sales / 100,000 / 110,000 / 121,000 / xxxxxx
+ 100% of Previous Month's A/R / 0 / 300,000 / 330,000 / xxxxxx
Total Budgeted Cash Collections / 100,000 / 410,000 / 451,000 / < To Cash Budget
(1) Accounts receivable balance on March 31 pro forma balance sheet.
(2) Sales revenue on first quarter pro forma income statement (sum of monthly sales).

D. Inventory Purchases Budget

E. Schedule of Cash Payments for Inventory

F. Pro Forma Financial Stmt Information

** COGS = 60% of Sales
Inventory Purchases Budget
Pro Forma
Jan / Feb / Mar / Stmt @3/31
D) PROJECTED PURCHASES
Budgeted Cost of Goods Sold / 240,000 / 264,000 / 290,400 / 794,400 (3)
Plus Desired Ending Inventory / 66,000 / 72,600 / 78,500 / 78,500 (4)
Total Inventory Needed / 306,000 / 336,600 / 368,900
Less Beginning Inventory / 0 / (66,000) / (72,600)
Required Inventory Purchases / 306,000 / 270,600 / 296,300
E) Schedule OF Cash Payments for Inventory Purchases
70% of Current Purchases / 214,200 / 189,420 / 207,410 / xxxxxx
30% of Prior Month Purchases / 0 / 91,800 / 81,180 / xxxxxx

Total Purch Cash Payments

/ 214,200 / 281,220 / 288,590 / 88,890 (5)
(3) Cost of goods sold on first quarter pro forma income stmt (sum of three months).
(4) Inventory balance on March 31 pro forma balance sheet.
(5) Accounts payable balance on March 31 pro forma balance sheet ($296,300 * 30%).

Note: *April* COGS (given) = $314,000 * .25 desired end inv = $78,500

Acct 2220: Chp 7 Case Study - Workpapers (Pg 2/4)

G. S&A Expense Budget

H. Schedule of Cash Payments for S&A Expenses

I. Pro Forma Data

GIVEN DATA:
Sales Commissions Percent / 5% of Sales
Supplies Expense Percent / 2% of Sales
Equipment Cost (in-place 1/1) / 100,000
Equipment Salvage Value / 10,000
Equip Useful Life (in Months) / 120
G) Selling and Administrative (S&A) Expense Budget
Jan / Feb / Mar / Pro Forma
PROJECTED S&A EXPENSES / Stmt @3/31
Salary Expense / 24,000 / 24,000 / 24,000
Sales Commissions 5% of Sales / 20,000 / 22,000 / 24,200 / 24,200 (6)
Supplies Expense 2% of Sales / 8,000 / 8,800 / 9,680
Utilities (Fixed) / 1,400 / 1,400 / 1,400 / 1,400 (7)
Depr on Equipment (per month) / 750 / 750 / 750 / 2,250 (8)
Rent (Fixed) / 3,600 / 3,600 / 3,600
Miscellaneous (Fixed) / 900 / 900 / 900
S&A Expenses before Interest / 58,650 / 61,450 / 64,530 / 184,630 (9)
** Interest expense is computed in the cash budget - see Budget “J”
H) SCHEDULE OF Cash Payments for S&A Expenses
Salary Expense / 24,000 / 24,000 / 24,000
100% Prior Month’s Sales Commissions / 0 / 20,000 / 22,000
Supplies Expense / 8,000 / 8,800 / 9,680
100% Prior Month’s Utilities / 0 / 1,400 / 1,400
Rent / 3,600 / 3,600 / 3,600
Miscellaneous / 900 / 900 / 900
Total Payments for S&A Expenses / 36,500 / 58,700 / 61,580
(6) Sales commissions payable balance on March 31 pro forma balance sheet.
(7) Utilities payable balance on March 31 pro forma balance sheet.
(8) Accumulated depreciation balance on March 31 pro forma balance sheet (sum of monthly amounts).
(9) S&A expense on first quarter pro forma income statement (sum of three months).

Acct 2220: Chp 7 Case Study - Workpapers (Pg 3/4)

J. Cash Budget

K. Pro Forma Cash Flow Data

(J) Cash Budget
Desired Ending Cash Balance / 12,000
Interest on Line of Credit / 1%/month / Pro Forma
Jan / Feb / Mar / Stmt @3/31
Cash Receipts
Beginning Cash Balance / 0 / 12,300 / 12,750
Add Cash Receipts / 100,000 / 410,000 / 451,000 / 961,000 (10)
Total Cash Available / 100,000 / 422,300 / 463,750
Cash Payments
For Inventory Purchases / 214,200 / 281,220 / 288,590 / 784,010 (11)
For S&A Expenses / 36,500 / 58,700 / 61,580 / 156,780 (12)
For Interest Expense* / 0 / 2,630 / 1,960 / 4,590 (13)
To Purchase Equipment / 100,000 / 0 / 0 / 100,000 (14)
Total Budgeted Payments / 350,700 / 342,550 / 352,130
Financing Activities
Surplus (Shortage) / (250,700) / 79,750 / 111,620
Borrow (Repay) in even 1000 / 263,000 / (67,000) / (99,000) / 97,000 (15)
Ending Cash Balance / 12,300 / 12,750 / 12,620 / 12,620 (16)
Support Computations below:
* Interest payments: Feb. ($263,000) x .01; Mar. [($263,000 – $67,000) x .01].
(10) Cash receipts from customers—operating activities on first quarter pro forma statement of cash flows (sum of monthly amounts).
(11) Cash payments for inventory—operating activities on first quarter pro forma statement of cash flows (sum of monthly amounts).
(12) Cash payments for S&A expenses—operating activities on first quarter pro forma statement of cash flows (sum of monthly amounts).
(13) Cash payments for interest expense--operating activities, first quarter pro forma statement of cash flows and the income statement (sum of monthly amounts).
(14) Investing activities on first quarter pro forma statement of cash flows. The investment in center equipment also appears on the March 31 pro forma balance sheet.
(15) Financing activities, first quarter pro forma statement of cash flows and the March 31 pro forma balance sheet (sum of monthly amounts).
(16) Cash balance on the March 31 pro forma balance sheet and the last item in the first quarter pro forma statement of cash flows.

NOTE: COMPLETED SOLUTION THROUGH MARCH 31st. This is a typical budgetary process involving multiple areas of the business. Planning ahead is key to obtaining desired results and identifying problems (i.e. bottlenecks) prior to committing to such an investment. Students will need to do such planning when working with the upcoming simulation. See me or our GA team with your questions.

Acct 2220: Chp 7 Case Study - Workpapers (Pg 4/4)

Bee Gee Distributors
Pro Forma Income Statement
For the Quarter Ended March 31
Sales Revenue / $ 1,324,000 / From: Sales Budget (A)
Cost of Goods Sold / (794,400) / From: Purch Budget (D)
Gross Margin / 529,600
Selling and Admin. Exp. / (184,630) / From: S&A Exp Budget(G)
Operating Income / 344,970
Interest Expense / (4,590) / From: Cash Budget (J)
Net Income / $ 340,380
Bee Gee Distributors
Pro Forma Balance Sheet
AT March 31
Assets / Data Source:
Cash / $ 12,620 / Cash Budget (J)
Accounts Receivable / 363,000 / Sales Budget (A)
Inventory / 78,500 / Purch. Budget (D)
Equipment / $ 100,000 / S&A Exp. Budget(G)
Accum. Depr. Equip. / (2,250) / 97,750 / S&A Exp. Budget(G)
Total Assets / $ 551,870
Liabilities
Accounts Payable / $ 88,890 / Purch. Budget (D)
Sales Comm. Payable / 24,200 / S&A Exp. Budget(G)
Utilities Payable / 1,400 / S&A Exp. Budget(G)
Credit Line (Liability) Payable / 97,000 / Cash Budget (J)
Total Liabilities / 211,490
Equity (No Common Stock here) / xxxxxxx
Retained Earnings / $ 340,380 / 3/31 Ending R/E
Total Liabilities and Equity / $ 551,870
Bee Gee Distributors
Pro Forma Statement of Cash Flows
For the Quarter Ended March 31
Cash Flow From Operating Activities
Cash Receipts from Customers / $ 961,000
Cash Payments for Inventory / (784,010)
Cash Payments for S&A Expenses / (156,780)
Cash Payments for Interest Expense / (4,590)
Net Cash Flow from Operating Activities / $ 15,620
Cash Flow From Investing Activities
Cash Payments to Purchase Equipment / (100,000)
Cash Flow From Financing Activities
Inflow from Borrowing on Line of Credit / 97,000
Net Change in Cash (Agrees to Balance Sheet??) / (YES) / $ 12,620