Problem in Financial Planning and complete the assignment using the Excel template. the introduction will be in the attach files

FIN 340 Individual Case #1: Problem in Financial Planning Assignment: 1. Prepare a monthly cash budget for the six-month period, January through June. 2. Prepare a pro-forma income statement and balance sheet for the same period, January through June. 3. Complete the assignment using the Excel template. Print out two pages (one including the cash budget and another with the pro forma statements) and turn in at the start of class on Wednesday, Nov 9th. Twosided printing is preferred. You do not need to print the formulas. Information: a) Sales are 15% for cash, 85% on account (credit sales). b) Of the credit sales, 20% are collected during the month of sale, 50% in the month following the month of sale, and 30% in the second month following the sale. c) Cost of goods sold is strictly materials (inventory) and averages 65% of sales. d) All inventory purchases are paid for one month after they are made. e) The store follows a policy of purchasing enough inventory each month to cover the following month's sales. f) A minimum cash balance of $8,000 is to be maintained by the store. g) \"Accrued wages and salaries\" and \"other liabilities\" remain unchanged. h) The corporate tax rate is 40%. As of December 31, 2015 all back taxes had been paid. No additional tax payment is due. i) A 10,000 dividend payment will be made in June. Balance Sheet as of December 31, 2015 Cash Accounts Receivable Inventory Furniture & Fixtures Accum. Depreciation Total Assets $8,000 12,750 7,200 18,000 (3,500) $42,450 Accounts Payable Accrued Wages & Salaries Other liabilities Capital stock Retained Earnings Total Liab & Equity Past Sales: November $10,000; December $15,000 Sales Forecast: January $11,000 May February 9,000 June March 21,000 July April 29,000 Monthly Expenses: a. Wages and Salaries (paid as incurred): January $1,500 April February 1,500 May March 2,500 June b. Rent: $600/mo. c. Depreciation: 100/mo. d. Other expenses: 2% of sales (paid as incurred) per month. $7,150 2,300 7,000 16,900 9,100 $42,450 $36,000 19,000 11,000 $3,000 2,000 1,500 Cash Budget Percent Revenues 100% November 10,000 December 15,000 Cash Inflows: Cash First Month Second Month Total Inflows: Cash Outflows: Inventory Purchases Wages Rent Other Expenses Dividend Payment Total Outflows: Beginning Cash Balance Add: Inflows Subract: Outflows Unadjusted Cash Balance Minimum Cash Balance Surplus (Deficit) of Cash Minimum Cash Balance 8,000 8,000 January 11,000 February 9,000 March April 21,000 29,000 May June July 36,000 19,000 11,000 Pro Forma Income Statement Forecast Period Jan-Jun Assumptions Net sales Cost of sales Gross Profit Operating Expenses Wages Rent Depreciation Other EBIT Interest EBT Income taxes Net income Dividends Pro Forma Balance Sheet Assets Cash balances Accounts receivable Inventories Total current assets 8,000 12,750 7,200 27,950 Furniture & Fixtures Accumulated depreciation Net Fixed Assets 18,000 (3,500) 14,500 Total Assets Liabilities Accounts Payable Accrued Wages & Salaries Accrued Taxes Other Liab Total current liabilities Long-Term Debt Capital Stock Retained earnings Total shareholders' equity Total Liabilities & OE External Funds Needed 42,450 7,150 2,300 7,000 16,450 16,900 9,100 26,000 42,450 na Forecast Period Jan-Jun