Reference no: EM13566876

Facts: The Observer Company is a small, rapidly growing wholesaler of consumer electronic products including small kitchen appliances and power tools. A sales forecast supplied by the Marketing Department predicts that sales during Quarter 1 of 20x2 will increase by 10% each month over the previous month's sales. Then (beginning in April 20x2) sales are expected to remain constant for the next several months. The Company is going to put some new equipment in operation just after the New Year begins. They hope to finance it largely with cash and the sale of marketable securities, but if necessary they can get a short-term loan from the Karch Bank. Observer's Balance Sheet at December 31, 20x1 is as follows:

Observer Company
Balance Sheet
31-Dec-20x1
Assets:
Cash			$      35,000
Accounts Receivable			270,000
Marketable securities			15,000
Inventory			154,000
Building (net of accumulated depreciation)			626,000
Total Assets			1,100,000
Liabilities and Stockholder's Equity:
Accounts payable			$    176,400
Bond interest payable			12,500
Property taxes payable			3,600
Bonds payable (due in 20x6)			300,000
Common Stock			500,000
Retained Earnings			$    107,500
Total L & SE			$ 1,100,000

As Assistant Controller for the Observer Company you are now preparing a monthly budget for Quarter 1 of 20x2. In this process, the following information has been accumulated:

(1)Projected Sales for December 20x1 are $400,000. Credit sales typically are 75% of total sales. Observer's credit experience indicates that 10% of credit sales are collected during the month of sale, and the remainder is collected during the following month.

(2)Observer's cost of goods sold is normally 70% of sales. Inventory is purchased on account and 40% of each month's purchases are paid during the month of purchase. The remainder is paid during the following month. To have adequate amounts of inventory on hand Observer has a policy that inventory at the end of each month should equal half of the next month's projected cost of goods sold.

(3)Observer's other monthly expenses are estimated as follows:

Sales Salaries $21,000

Advertising & promotion $16,000

Administrative salaries $21,000

Depreciation $25,000

Interest on Bonds $2,500

Property taxes $900

Sales Commissions --- 1 percent of current month's sales

(4) The Company President has indicated that Observer will be investing $125,000 in equipment to be used in the firm's warehouse just after the New Year begins. This equipment purchase will be financed from the company's cash and marketable securities. However, the President noted that Observer should keep a minimum cash balance of $25,000. If necessary, the remainder of the equipment purchased will be financed using short-term credit from the Karch Bank. The minimum period for such a loan is 3 months with short-term interest rates being 10% per year. If a loan is needed the President has decided that it should be paid off at the end of the Quarter 1, if possible.

(5)Observer's Board of Directors has indicated an intention to declare and pay cash dividends of $50,000 on the last day of each quarter.

(6)The interest on any short-term borrowing will be paid when the loan is repaid. Interest on Observer's Bonds is paid semiannually on January 31 and July 31 for the preceding six-month period.

(7) Property taxes are paid semi-annually on February 28 and August 31 for the preceding six-month period.

Required: Prepare Observer Company's Master Budget for Quarter 1 of 20x2 using the Excel spreadsheet provided.

Master Budget Problem
Sales Increase per Month	10%
Sales Budget
	20x1	20x2
	December	January	February	March
Total Sales	$         400,000
Credit Sales	300,000
Cash Sales	$         100,000
Cash Receipts Budget
		20x2
		January	February	March
Cash Sales
Cash collections-current
month's credit sales
Cash collections-previous
month's credit sales
Total Cash Receipts
Inventory Purchases Budget
	20x1	20x2
	December	January	February	March
Budgeted CGS	$         280,000
Add:Desired Ending Inv.	154,000
Total goods needed	434,000
Less: Expected Begin Inv.	140,000
Purchases	$         294,000
Cash Disbursements (CD) Budget
		20x2
		January	February	March
Inventory Purchases:
CDs for current month purchases
CDs for previous month purchases
Total CDs for inventory purchases
Other Expenses:
Sales salaries
Advertising & promotion
Administrative salaries
Interest on bonds
Property taxes
Sales commissions
Total CDs for Other Expenses
Total Cash Disbursements
Summary Cash Budget
		20x2
		January	February	March
Cash Receipts
Cash Disbursements
Change in cash due to operations
Proceeds from sale of
marketable securities
Proceeds from bank loan
Purchase of equipment
Repayment of bank loan
Interest on bank loan
Payment of dividends
Change in cash balance during Quarter 1
Cash balance, January 1, 20x2
Cash balance, March 31, 20x2
Analysis of short-term financing needs:
Projected cash balance at December 31, 20x1			$           35,000
Less: Required minimum cash balance			25,000
Cash Available for equipment purchase			10,000
Proceeds from sale of marketable securities			15,000
Cash available			25,000
Less: Cost of equipment			125,000
Required short-term borrowing			$       (100,000)
Observer Company
Budgeted Income Statement
For the First Quarter 20x2
Sales Revenue
Less: Cost of Goods Sold
Gross Margin
Less: Selling & Administrative Expenses:
Sales salaries
Sales commissions
Advertising & promotion
Administrative salaries
Depreciation
Interest on bonds
Interest on short-term loan
Property taxes
Total S & A Expenses
Net Income
Observer Company
Budgeted Balance Sheet
3/31/20x2
Assets:
Cash
Accounts Receivable
Inventory
Building & Equipment (net of accumulated depreciation)
Total Assets
Liabilities and Stockholder's Equity:
Accounts payable
Bond interest payable
Property taxes payable
Bonds payable (due in 20x6)
Common Stock
Retained Earnings
Total L & SE