Hi - I need help to complete this homework.

Thank you!

my email is r..9@gmail.com

Master Budget Problem 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 Accounts Receivable Marketable securities 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 $ $ 35,000 270,000 15,000 154,000 626,000 1,100,000 176,400 12,500 3,600 300,000 500,000 $ 107,500 $ 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 (Includes depreciation on new equipment purchased at beginning of Quarter 1) 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 shortterm 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: Complete the Observer Company's Master Budget for Quarter 1 of 20x2 using the Excel spreadsheet provided. Master Budget Problem Sales Increase per Month 0.10 Sales Budget Total Sales Credit Sales Cash Sales 20x1 December $ 400,000 $ $ 300,000 $ $ 100,000 $ 20x2 January 440,000 330,000 110,000 Cash Receipts Budget 20x2 Cash Sales Cash collections-current month's credit sales Cash collections-previous month's credit sales Total Cash Receipts $ January 110,000 33,000 $ 270,000 413,000 Inventory Purchases Budget Budgeted CGS Add:Desired Ending Inv. Total goods needed Less: Expected Begin Inv. Purchases 20x1 December $ 280,000 $ 154,000 434,000 140,000 $ 294,000 $ 20x2 January 308,000 169,400 477,400 154,000 323,400 Cash Disbursements (CD) Budget 20x2 January Inventory Purchases: CDs for current month purchases $ 129,360 CDs for previous month purchases Total CDs for inventory purchases $ $ 176,400 305,760 $ 305,760 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 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: 15,000 100,000 (125,000) Projected cash balance at December 31, 20x1 Less: Required minimum cash balance Cash Available for equipment purchase Proceeds from sale of marketable securities Cash available Less: Cost of equipment Required short-term borrowing 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 (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 Instructions: We will complete the January (yellow cells) during the Session 8 lecture For homework, please complete: (1) the (gray cells) February and March Colum (2) the Quarter 1 Total columns (grey cells) and (3) the Income Statement and Balance Sheet (gray cells). Budget 20x2 February $ 484,000 $ $ 363,000 $ $ 121,000 $ March Quarter 1 532,400 $ 1,456,400 399,300 $ 1,092,300 133,100 $ 364,100 eipts Budget 20x2 February $ 121,000 $ March Quarter 1 133,100 $ 364,100 297,000 330,000 Check Figures are color coded to the gold $ 1,367,030 rchases Budget 20x2 February $ 338,800 March Quarter 1 $ ments (CD) Budget 20x2 February March Quarter 1 1,051,820 $ - $ - $ 1,004,612 $ 1,213,576 $ 25,954 Cash Budget 20x2 February March Quarter 1 15,000 100,000 (125,000) (100,000) (2,500) (50,000) (100,000) (2,500) (50,000) $ $ (162,500) 35,000 (127,500) $ $ $ $ 35,000 25,000 10,000 15,000 25,000 125,000 (100,000) - $ 160,656 - $ - $ 1,247,664 ssion 8 lecture uary and March Columns, come Statement coded to the gold cells Observer Company Budgeted Balance Sheet 31-Dec-01 Assets: Cash Accounts Receivable Marketable securities Inventory Building (net of accumulated depreciation) Total Assets Liabilities and Stockholder's Equity: Accounts payable Bond interest payable Property taxes payable Bonds payable (due in 2006) Common Stock Retained Earnings Total L & SE $ $ 35,000 270,000 15,000 154,000 626,000 1,100,000 176,400 12,500 3,600 300,000 500,000 $ 107,500 $ 1,100,000