Special Assignment #3: Comprehensive Budgeting Problem

ABC Company is considering whether to open a new distribution center. The center would open January 1, 2015. To make the decision, the planning committee requires a master budget for the centers first quarter of operation (January, February, and March of 2015).

Required

You are to construct the first quarter master budget based on the following expectations:

a. January sales are estimated to be $400,000 of which $100,000 will be cash sales and $300,000 will be on credit. The company expects sales to grow 10% per month for the first few months of operation. Prepare a sales budget for the first quarter.

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

c. Use the information developed 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.

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 months projected cost of goods sold. Assume ABC Company expects April cost of goods sold to be $314,000. Prepare an inventory purchases budget.

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 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.

g. Budgeted monthly selling and administrative expenses are:

Salary Expense (Fixed)	$24,000
Sales Commissions	5% of Sales
Supplies Expense	2% of Sales
Utilities (Fixed)	$ 1,400
Depreciation on Center Equipment (Fixed)*	$ 750
Rent (Fixed)	$ 3,600
Miscellaneous (Fixed)	$ 900

* The capital expenditures budget shows that ABC Company must purchase $100,000 of equipment on January 1 to establish the new center. The equipment supplier allows a thirty-day trial period. ABC Company will pay for the equipment on January 31. The equipment is expected to have a 10-year useful life and a $10,000 salvage value. Prepare a 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 they are incurred. Prepare a schedule of cash payments for selling and administrative expenses.

i. Use the information developed 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.

j. Using a line of credit, ABC Company borrows 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 pro forma income statement.

m. Complete the March 31 pro forma balance sheet.

n. Complete the first quarter pro forma statement of cash flows.

Work Papers

a. b. c. Sales Budget, Schedule of Cash Receipts, Pro Forma Data

Sales Budget
				Pro Forma
Projected Sales	Jan	Feb	Mar	Stmt. Data
Cash Sales	$100,000	$110,000	$121,000
Sales on Account	300,000	330,000	363,000	(a)
Total Budgeted Sales	$400,000	$440,000	$484,000	(b)
Schedule of Cash Receipts
Current Months Cash Sales	$100,000	110,000	121,000
Previous Month's A/R	0	300,000	330,000
Total Budgeted Cash Collections	$100,000	410,000	451,000
(a) Accounts receivable balance on March 31 pro forma balance sheet. (b) Sales revenue on first quarter pro forma income statement (sum of monthly sales).

Accounts receivable balance on March 31 pro forma balance sheet.

$363,00

(b) Sales revenue on first quarter pro forma income statement (sum of monthly sales).

400,000 + 440,000 + 484,000 = $1,324,000

d. e. f. Inventory Purchases Budget, Schedule of Cash Payments for Inventory, Pro Forma Data

Inventory Purchases Budget
				Pro Forma
	Jan	Feb	Mar	Stmt. Data
PROJECTED PURCHASES
Budgeted Cost of Goods Sold	$240,000	$264,000	$290,400	(a)
Plus Desired Ending Inventory	66,000	72,600	78,500	(b)
Total Inventory Needed	$306,000	$336,000	$368,900
Less Beginning Inventory	0	66,000	72,600
Required Inventory Purchases	$306,000	$270,600	$296,300	(c)
Schedule OF Cash Payments for Inventory Purchases
Current Purchases	$214,200	$189,420	$207,410
Prior Month's Purchases	0	91,800	81,180
Total Budgeted Inventory Payments	$214,200	$281,220	$288,590
(a) Cost of goods sold on first quarter pro forma income statement (sum of monthly amounts). (b) Inventory balance on March 31 pro forma balance sheet. (c) Accounts payable balance on March 31 pro forma balance sheet

(a) Cost of goods sold on first quarter pro forma income statement (sum of monthly amounts).

$240,00 + $264,000 + 290,400 = $794,400

(b) Inventory balance on March 31 pro forma balance sheet.

$78,500

(c) Accounts payable balance on March 31 pro forma balance sheet

$296,300 x 30% = $88,80

g. h. i. S&A Expense Budget, Schedule of Cash Payments for S&A

Selling and Administrative (S&A) Expense Budget
	Jan	Feb	Mar	Pro Forma
PROJECTED S&A EXPENSES				Stmt. Data
Salary Expense	$24,000	$24,000	$24,000
Sales Commissions	20,000	22,000	24,200	(a)
Supplies Expense	8,000	8,800	9,680
Utilities	1,400	1,400	1,400	(b)
Depreciation on Equipment	750	750	750	(c)
Rent	3,600	3,600	3,600
Miscellaneous	900	900	900
S&A Expenses before Interest	$58,650	$61,450	$64,530	(d)
SCHEDULE OF Cash Payments for S&A Expenses
Salary Expense	$24,000	$24,000	$24,000
Sales Commissions		20,000	22,000
Supplies Expense	8,000	8,800	9,680
Utilities		1,400	1,400
Depreciation on Equipment	0	0	0
Rent	3,600	3,600	3,600
Miscellaneous	900	900	900
Total Payments for S&A Expenses	$36,500	$58,700	$61,580
(a) Sales commissions payable balance on March 31 pro forma balance sheet. (b) Utilities payable balance on March 31 pro forma balance sheet. (c) Accumulated depreciation balance on March 31 pro forma balance sheet (sum of monthly amounts). (d) S&A expense on first quarter pro forma income statement (sum of monthly amounts).

j. k. Cash Budget, Pro Forma Cash Flow Data

Cash Budget
				Pro Forma
	Jan	Feb	Mar	Stmt. Data
Cash Receipts
Beginning Cash Balance
Add Cash Receipts
Total Cash Available
Cash Payments
For Inventory Purchases
For S&A Expenses
For Interest Expense*
To Purchase Equipment
Total Budgeted Payments
Financing Activities
Surplus (Shortage)
Borrowing (Repayment)				(f)
Ending Cash Balance				(g)
(a) Cash receipts from customersoperating activities on first quarter pro forma statement of cash flows (sum of monthly amounts). (b) Cash payments for inventoryoperating activities on first quarter pro forma statement of cash flows (sum of monthly amounts). (c) Cash payments for S&A expensesoperating activities on first quarter pro forma statement of cash flows (sum of monthly amounts). (d) Cash payments for interest expense--operating activities, first quarter pro forma statement of cash flows and the income statement (sum of monthly amounts). (e) 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. (f) Financing activities, first quarter pro forma statement of cash flows and the March 31 pro forma balance sheet (sum of monthly amounts). (g) Cash balance on the March 31 pro forma balance sheet and the last item in the first quarter pro forma statement of cash flows.

l.

ABC Company Distributors Pro Forma Income Statement For the Quarter Ended March 31, 2015
		Data Source
Sales Revenue	$	Sales Budget
Cost of Goods Sold		Purchases Budget
Gross Margin
Selling and Admin. Exp.		S&A Exp. Budget
Operating Income
Interest Expense		Cash Budget
Net Income	$

m.

ABC Company Distributors Pro Forma Balance Sheet March 31, 2015
Assets			Data Source
Cash		$	Cash Budget
Accounts Receivable			Sales Budget
Inventory			Purch. Budget
Equipment	$		S&A Exp. Budget
Accum. Depr. Equip.			S&A Exp. Budget
Total Assets
Liabilities
Accounts Payable		$	Purch. Budget
Sales Comm. Payable			S&A Exp. Budget
Utilities Payable			S&A Exp. Budget
Line of Credit Liability			Cash Budget
Total Liabilities
Equity
Retained Earnings
Total Liabilities and Equity		$

n.

ABC Company Distributors Pro Forma Statement of Cash Flows For the Quarter Ended March 31, 2015
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 Cash Flow		$
Plus Beginning Cash Balance
Ending Cash Balance		$