I need someone with excel knowledge urgently

profileJoyrose
balancesheet_and_cashflow_statements.xls
Home>Business & Finance homework help>Management homework help>I need someone with excel knowledge urgently

Sheet1

Habitat Coffee Cafe 
Projected Cash Flow Statement
Year 1 Year 2 Year 2
1st Quarter 2nd Quarter 3rd Quarter 4th Quarter 1st Quarter 2nd Quarter 3rd Quarter 4th Quarter 1st Quarter 2nd Quarter 3rd Quarter 4th Quarter
Beginning Cash Balance 25,000 25,000 25,000 25,000 25,000 25,000 25,000 25,000 35,000 35,000 35,000 35,000
Cash Inflows
Income from Sales 750,000 750,000 750,000 750,000 750,000 750,000 750,000 750,000 750,000 750,000 750,000 750,000
Accounts Receivable 250,000 250,000 250,000 250,000 250,000 250,000 250,000 250,000 250,000 250,000 250,000 250,000
Total Cash Inflows 1,000,000 1,000,000 1,000,000 1,000,000 1,000,000 1,000,000 1,000,000 1,000,000 1,000,000 1,000,000 1,000,000 1,000,000
Cash Outflows
Investing Activities
New Fixed Assets Purchases 140,000 140,000 140,000 140,000 140,000 140,000 140,000 140,000 140,000 140,000 140,000 140,000
Inventory Addition to Bal.Sheet 148,000 148,000 148,000 148,000 148,000 148,000 148,000 148,000 148,000 148,000 148,000 148,000
Cost of Sales 420,000 420,000 420,000 420,000 725,000 625,000 625,000 625,000 725,000 625,000 625,000 625,000
Operating Activities
Salaries and Wages 440,000 440,000 440,000 440,000 440,000 440,000 440,000 440,000 440,000 440,000 440,000 440,000
Fixed Business Expenses 658,880 658,880 658,880 658,880 658,880 658,880 658,880 658,880 658,880 658,880 658,880 658,880
Taxes 381,664 381,664 381,664 381,664 381,664 381,664 381,664 381,664 381,664 381,664 381,664 381,664
Financing Activities
Loan Payments 6,700 6,700 6,700 6,700 6,700 6,700 6,700 6,700 6,700 6,700 6,700 6,700
Line of Credit Interest 300 300 300 300 300 300 300 300 300 300 300 300
Line of Credit Repayments 5,000 5,000 5,000 5,000 5,000 5,000 5,000 5,000 5,000 5,000 5,000 5,000
Dividends Paid - 0 - 0 - 0 - 0 - 0 - 0 - 0 - 0 - 0 - 0 - 0 - 0
Total Cash Outflows 2,200,544 2,200,544 2,200,544 2,200,544 2,505,544 2,405,544 2,405,544 2,405,544 2,505,544 2,405,544 2,405,544 2,405,544
Cash Flow (1,200,544) (1,200,544) (1,200,544) (1,200,544) (1,505,544) (1,405,544) (1,405,544) (1,405,544) (1,505,544) (1,405,544) (1,405,544) (1,405,544)
Operating Cash Balance (1,175,544) (1,175,544) (1,175,544) (1,175,544) (1,480,544) (1,380,544) (1,380,544) (1,380,544) (1,470,544) (1,370,544) (1,370,544) (1,370,544)
Line of Credit Drawdowns 400 400 400 400 400 400 400 400 400 400 400 400
Ending Cash Balance (1,175,144) (1,175,144) (1,175,144) (1,175,144) (1,480,144) (1,380,144) (1,380,144) (1,380,144) (1,470,144) (1,370,144) (1,370,144) (1,370,144)
Line of Credit Balance 400 (4,200) (8,800) (13,400) 400 (4,200) (8,800) (13,400) 400 (4,200) (8,800) (13,400)

Sheet2

Habitat Coffee Cafe
Balance Sheet
End of Year one End of Year Two End of Year Three
Assets
Current Assets
Cash 100,000 210,000 150,000
Accounts Receivable 20,000 20,000 20,000
Inventory 60,000 60,000 60,000
Prepaid Expenses 3,000 3,100 2,000
Other Current 21,000 41,000 5,600
Total Current Assets 204,000 334,100 237,600
Fixed Assets
Real Estate-Land 400,000 400,000 400,000
Buildings 600,000 600,000 600,000
Leasehold Improvements 300,000 300,000 300,000
Equipment 490,000 490,000 490,000
Furniture and Fixtures 120,000 120,000 120,000
Vehicles 1,200,000 1,200,000 1,200,000
Other Fixed Assets 43,000 43,000 43,000
Total Fixed Assets 3,153,000 3,153,000 3,153,000
Less: Accumulated Depreciation 176,000 176,000 176,000
Total Assets 3,181,000 3,311,100 3,214,600
Liabilities and Owner's Equity
Liabilities
Accounts Payable 50,000 50,000 50,000
Notes Payable 5,000 5,600 4,200
Mortgage Payable 120,000 134,000 125,000
Credit Card Debt 30,000 43,000 54,000
Vehicle Loans 45,000 54,900 56,700
Other Bank Debt 4,200 3,200 5,400
Line of Credit Balance 21,000 23,000 32,000
Total Liabilities 275,200 313,700 327,300
Owner's Equity
Common Stock 2,000,000 2,000,000 2,000,000
Retained Earnings 905,800 997,400 887,300
Dividends Dispersed - 0 - 0 - 0
Total Owner's Equity 2,905,800 2,997,400 2,887,300
Total Liabilities and Owner's Equity 3,181,000 3,311,100 3,214,600

Sheet3