Acct 2220 Zeigler:Chp 7 Intro & Bee Gee Budget Case Study

Assignment(s): Read Chp 7 and review the PPT slides. Next,walk-through the self-review problem (pg 272-274). Note that this is a *partial*“Master Budget”. Next, complete E7-2A (pg 275) by hand for an overview (& H/W collection).

Last, Review theBee Gee Case below. We will work through together next class.

Comprehensive Master (Operating) Budget

Bee Gee Distributors, a wholesale company, is considering whether to open a new distribution center near Bowling Green, Ohio. The center would open January 1, 2016. The economy is currently problematic and extensive advance planning is requiredif such a commitment is to be made. As a part of the planning process, The Board of Directors requires a Master (i.e. Operating) Budget for the center’s first quarter of operations (i.e. January, February March of 2016). In order to prepare any budget, management must make reasonable assumptions about expected sales, inventory levels and cash flows.

Required: Your help is needed to construct the entirefirst quarter Master Budget based upon the following two pages of management assumptions:

SALES BUDGET (i.e. “What is the Profit Plan?”)

** It all starts with a sales forecast**

a.January sales are estimated to be $400,000 of which $100,000 (25%) will be cash and $300,000 will be on credit. Management expects the above sales pattern to continue with an overall grow rate of 10% per month. Prepare a sales budget.

b.The company expects to collect 100% of the accounts receivable in the month following the month of the sale. Prepare a schedule of expected cash receipts.

c.Use the information developed above in requirements a and b to determine the amount of accounts receivable on the March 31 pro forma balance sheet and the amount of sales on the first quarter pro forma income statement.

______

PURCHASES BUDGET (Note: “What are our total needs, less what do we have”?):

d.Cost of goods sold will be 60% of sales. Company policy is to budget an ending inventory balance equal to 25% of the next month’s projected cost of goods sold. Prepare an inventory purchases budget.

Note: For March analysis needs, April cost of goods sold is expected to be $314,000.

e.All inventory purchases are on account. The company pays 70% of accounts payable in the month of purchase. It pays the remaining 30% in the following month. Prepare a schedule of expected cash payments for inventory purchases.

f.Use the information developed above in requirements d and e to determine the amount of cost of goods sold on the first quarter pro forma income statement and the amounts of ending inventory and accounts payable on the March 31 pro forma balance sheet.

ADMINISTRATIVE & SALES EXPENSE BUDGET:

g.Budgeted monthly selling and administrative expenses are:

Salary Expense / $24,000
Sales Commissions / 5% of Sales
Supplies Expense / 2% of Sales
Utilities / $ 1,400
Depreciation on New Equipment (see note below*) / ?
Rent / $ 3,600
Miscellaneous / $ 900

*The capital expenditures budget shows that Bee Gee must purchase $100,000 of equipment on January 1 to establish the new center. Since the equipment supplier allows a thirty-day trial period, assume Bee Gee will pay for the equipment in January (i.e. by 1/31). Using Straight-line depreciation, the equipment is expected to have a 10-year useful life and a $10,000 salvage value.

SELLING AND ADMINISTRATIVE EXPENSE BUDGET:

h.Sales commissions and utilities are paid in the month after the month in which they are incurred. All other expenses are paid in the month in which they are incurred. Prepare a schedule of cash payments for selling and administrative expenses.

i.Use the information developed above in requirements g and h to determine the amount of sales commissions payable, utilities payable, and accumulated depreciation on the March 31 pro forma balance sheet and the amount of selling and administrative expense on the first quarter pro forma income statement.

CASH BUDGET (i.e. Financing or “How will we pay for this Profit Plan?”):

j.Using a “line of credit”, Bee Geeborrows and repays principal in increments of $1,000 on the last day of the month as needed. It pays interest of 1 percent per month in cash on the last day of the month. Company policy is to maintain an ending cash balance of at least $12,000. Use this information and the schedules prepared in requirements b, e, and h to prepare a Cash Budget.

k.Use the information developed in requirement “j”to determine the cash flows from operating, investing, and financing activities on the first quarter pro forma statement of cash flows, the interest expense on the first quarter pro forma income statement and the amount of the ending cash balance and the line of credit liability on the March 31 pro forma balance sheet.

l.Complete the first quarter (for three-months) Pro Forma Income Statement.

Note: Income Statement check figures for January and February will be posted.

m.Complete the March 31 Pro Forma Balance Sheet.

Note: Balance Sheet check figures for January and February will be posted.

n.Complete the first quarter (for three-months) Pro Forma Statement of Cash Flows.

Note: Stmt of Cash Flows check figures for January and February will be posted.

Name:______Class Period:______

Acct 2220: Chp 7 Bee Gee Case Study - Workpapers (Pg 1/6)

USE PENCIL FOR ALL WORK

A. Sales Budget

B.Schedule of Cash Receipts

C. Pro Forma Financial Stmt information

Estimated Sales Growth Rate / 10% per month
Sales Budget
C)Pro Forma
A) Projected Sales / Jan / Feb / Mar / Info for 3/31
Cash Sales / xxxxxx
Sales on Account / (1)
Total Budgeted Sales / (2)
B) Sch of Cash Receipts
Current Month’s Cash Sales / xxxxxx
+ 100% of Previous Month's A/R / xxxxxx
Total Budgeted Cash Collections / xxxxxx
(1) Accounts receivable balance at March 31 (for 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 Purchases

F. Pro Forma Financial Stmt Information

** COGS = 60% of Sales
Inventory Purchases Budget
F) Pro Forma
Jan / Feb / Mar / Info for 3/31
D) PROJECTED PURCHASES
Budgeted COGS (60% of Sales) / (3)
Plus Desired Ending Inventory / (4)
Total Inventory Needed
Less Beginning Inventory
Required Inventory Purchases
E) Schedule OF Cash Payments for Inventory Purchases
70% of Current Purchases / xxxxxx
30% of Prior Month Purchases / xxxxxx

Total Purch Cash Payments

/ (5)
(3) Cost of goods sold on first quarter pro forma Income Stmt (sum of three months).
(4) Inventory balance atMarch 31 (for pro forma Balance Sheet).
(5) Accounts payable balance atMarch 31 (for pro forma Balance Sheet).

Acct 2220: Chp 7 Bee Gee Case Study - Workpapers (Pg 2/6)

USE PENCIL FOR ALL WORK

G.S&A Expense Budget

H. Schedule of Cash Payments for S&A Expenses

I. Pro Forma Data for 3/31 Financial Stmt

GIVEN DATA:
Sales Commissions Percent / 5% of Sales
Supplies Expense Percent / 2% of Sales
Equipment Cost (Purchased 1/1) / 100,000
Equipment Salvage Value / 10,000
Equip Useful Life (in Months) / 120
G) Selling and Administrative (S&A) Expense Budget
I) Pro Forma Info for
Jan / Feb / Mar / 3/31 Fin
PROJECTED S&A EXPENSES / Statements
Salary Expense
Sales Commissions 5% of Sales / (6)
Supplies Expense 2% of Sales
Utilities / (7)
Depr on Equipment (per month) / (8)
Rent
Miscellaneous
S&A Expenses before Interest / (9)
** Interest expense is computed in the cash budget - see Budget “J”
H) SCHEDULE OF Cash Payments (for S&A Expenses)
Salary Expense
100% Prior Month’s Sales Commissions
Supplies Expense
100% Prior Month’s Utilities
Rent
Miscellaneous
Total Payments for S&A Expenses
(6) Sales commissions payable balance on March 31 (for pro forma Balance Sheet).
(7) Utilities payable balance on March 31 (for pro forma Balance Sheet).
(8) Accumulated depreciation balance on March 31 for 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 Bee Gee Case Study - Workpapers (Pg 3/6)

USE PENCIL FOR ALL WORK

J. Cash Budget

K. Pro Forma Cash Flow Data

(J) Cash Budget
Desired Ending Cash Balance / 12,000 / K) Info For
Interest on Line of Credit / 1%/month / Pro Forma
Jan / Feb / Mar / 3/31 Stmt
Cash Receipts /
Beginning Cash Balance
Add Cash Receipts / (10)
Total Cash Available
Cash Payments
For Inventory Purchases / (11)
For S&A Expenses / (12)
For Interest Expense* / (13)
To Purchase Equipment / (14)
Total Budgeted Payments
Financing Activities
Surplus (Shortage)
Borrow (Repay) in even 1000 / (15)
Ending Cash Balance / 12,300 / (16)
Support Calculations 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.

* MARCH 31stQUARTERLY PRO-FORMA FIN STMT * (Pg 4/6)

(L) Bee Gee Distributors
Pro Forma Income Statement
For the Quarter Ended March 31st
Sales Revenue / $ / From: Sales Budget (A)
Cost of Goods Sold / From: Purch Budget (D)
Gross Margin
Selling and Admin. Exp. / From:S&A Exp Budget(G)
Operating Income
Interest Expense / From: Cash Budget (J)
Net Income / $
(M) Bee Gee Distributors
Pro Forma Balance Sheet
AT March 31st
Assets / Data Source:
Cash / $ / Cash Budget (J)
Accounts Receivable / Sales Budget (A)
Inventory / Purch. Budget (D)
Equipment / $ / S&A Exp. Budget(G)
Accum. Depr. Equip. / S&A Exp. Budget(G)
Total Assets / $
Liabilities
Accounts Payable / $ / Purch. Budget (D/E)
Sales Comm. Payable / S&A Exp. Budget(G)
Utilities Payable / S&A Exp. Budget(G)
Line of Credit Liability / Cash Budget (J)
Total Liabilities
Equity(No Common Stock here) / xxxxxxxx
Retained Earnings / $ / 3/31 Ending R/E
Total Liabilities and Equity / $
(N) Bee Gee Distributors
Pro Forma Statement of Cash Flows
For the QUARTER Ended March 31st
Cash Flow From Operating Activities
Cash Receipts from Customers / $
Cash Payments for Inventory (answer given >) / 784,010
Cash Payments for S&A Expenses
Cash Payments for Interest Expense
Net Cash Flow from Operating Activities
Cash Flow From Investing Activities
Cash Payments to Purchase Equipment
Cash Flow From Financing Activities
Inflow from Borrowing on Line of Credit
Net Change in Cash for the (entire) QUARTER

JANUARY (ONLY) PRO-FORMA FINANCIAL STMTS (PG 5/6)

Bee Gee Distributors
Pro Forma Income Statement
For the MONTH Ended JANUARY 31st
Sales Revenue / From: Sales Budget (A)
Cost of Goods Sold / From: Purch Budget (D)
Gross Margin
Selling and Admin. Exp. / From:S&A Exp Budget(G)
Operating Income
Interest Expense / From: Cash Budget (J)
Net Income
Bee Gee Distributors
Pro Forma Balance Sheet
AT JANUARY 31st
Assets / Data Source:
Cash / Cash Budget (J)
Accounts Receivable / Sales Budget (A)
Inventory / Purch. Budget (D)
Equipment / S&A Exp. Budget(G)
Accum. Depr. Equip. / S&A Exp. Budget(G)
Total Assets
Liabilities
Accounts Payable / Purch. Budget (D/E)
Sales Comm. Payable / S&A Exp. Budget(G)
Utilities Payable / S&A Exp. Budget(G)
Line of Credit Liability / Cash Budget (J)
Total Liabilities
Equity(No Common Stock here) / xxxxxxxx
Retained Earnings / 1/31 Ending R/E
Total Liabilities and Equity / $
Bee Gee Distributors
Pro Forma Statement of Cash Flows
For the MONTH Ended JANUARY 31st
Cash Flow From Operating Activities
Cash Receipts from Customers
Cash Payments for Inventory
Cash Payments for S&A Expenses
Cash Payments for Interest Expense
Net Cash Flow from Operating Activities
Cash Flow From Investing Activities
Cash Payments to Purchase Equipment
Cash Flow From Financing Activities
Inflow from Borrowing on Line of Credit
Net Change in Cash for the Month / $ 12,300

FEBRUARY (ONLY) PRO-FORMA FINANCIAL STMTS (PG 6/6)

Bee Gee Distributors
Pro Forma Income Statement
For the MONTH Ended FEBRUARY28th
Sales Revenue / $ / From: Sales Budget (A)
Cost of Goods Sold / From: Purch Budget (D)
Gross Margin
Selling and Admin. Exp. / From:S&A Exp Budget(G)
Operating Income
Interest Expense / From: Cash Budget (J)
Net Income / $
Bee Gee Distributors
Pro Forma Balance Sheet
AT FEBRUARY 28th
Assets / Data Source:
Cash / $ / Cash Budget (J)
Accounts Receivable / Sales Budget (A)
Inventory / Purch. Budget (D)
Equipment / $ / S&A Exp. Budget(G)
Accum. Depr. Equip. / S&A Exp. Budget(G)
Total Assets / $
Liabilities
Accounts Payable / $ / Purch. Budget (D/E)
Sales Comm. Payable / S&A Exp. Budget(G)
Utilities Payable / S&A Exp. Budget(G)
Line of Credit Liability / Cash Budget (J)
Total Liabilities
Equity(No Common Stock here) / xxxxxxxx
Retained Earnings / $ / 2/28 Ending R/E
Total Liabilities and Equity / $
Bee Gee Distributors
Pro Forma Statement of Cash Flows
For the MONTH Ended FEBRUARY28th
Cash Flow From Operating Activities
Cash Receipts from Customers / $
Cash Payments for Inventory
Cash Payments for S&A Expenses
Cash Payments for Interest Expense
Net Cash Flow from Operating Activities
Cash Flow From Investing Activities
Cash Payments to Purchase Equipment
Cash Flow From Financing Activities
Inflow from Borrowing on Line of Credit
Net Change in Cash for the Month