Sales budget - microsoft excel

The company must maintain a minimum cash balance of $5,000. An open line of credit is available at a local bank for any borrowing that may be needed during the quarter. Borrowin and repayments are in multiples of $1,000 and are done at the beginning of a month. Intere is 1% per month and is paid each month based on the previous month's balance e. Create a tab titled "Cash" that looks like the image atthe top of the next page and make sure to follow the rules on the Microsoft Excel checklist. There are a few tricky cells here and the video will help you understand howto complete the formulas. Oakton Furniture Cash Budget For the Quarter Ended March 31 Februa Jan March Quarter 6 Beginning Cash Balance 7 Add Cash Receipts 8 Cash Available 9 Less Payments 10 For Inventory Purchases 11 For Operating Expenses 12 For Land Purchase 13 For Dividends 14 Total Budgeted Payments 15 Surplus/(Shortage) 16 Financing Activity 17 Borrowing 18 Interest Expense 19 Repayment 20 Ending Cash Balance 21 Check Figure: Ending Cash is $36,785 f. Create a tab title "Financials" and prepare the financial statements as of March 31. of the images belowwill be on the same tab. This uses your knowledge from ACC 153 as wel as Exhibit 9-17 from our textbook. The video will give you many hints! All three Oakton Furniture Income Statement For the Quarter Ended March 31 5 Revenues 6 Cost of Goods Sold 7 Gross Margin 8 Operating Expenses: 9 Salaries and wages 0 Advertising 11Shipping 12 Depreciation 13 Other Expenses 4 Total Operating Expenses 15 Net Operating Income 6 Less: Interest Expense 17 Net Income 18 Oakton Furniture Statement of Retained Earnings For the Quarter Ended March 31 20 21 23 24 Retained Earnings, December 31 25 Add: Net Income 26 Less: Dividends 27 Retained Earnings, March 31 28 Oakton Furniture Balance Sheet March 31 30 31 32 4 Current Assets: 35 Cash Accounts Receivable 38 Total current assets 39 Property and equipment, net 40 Total Assets 42 Current Llabilities 43 Accounts Payable 45 Stockholders Equity 46 Capital Stock Retained Eamings 8Total Stockholders Equity 9 Total Liabilities and Stockholders' Equity 50 Check Figures: Net Income is $116,460. (Video states that there is a Total Assets check figur but that is no longer provided. Just make sure Assets Liabilities+Stockholders' Equity) Trial Balance as of December 31 Debits Credits S40,000 70,000 119,625 145,000 Cash Accounts Receivable Inventory Property and Equipment (net) Accounts Pavable Capital Stock Retained Earnings $110,100 123,000 141,525 Sales Data $280,00 290,000 330,000 320,000 340,000 40% 60% December sales January sales February sales March sales April sales Credit Sales as a percent of sales Cash Sales as a percent of sales Purchases Data Cost of Goods Desired Inventory as percent of following month needs Purchases paid in month of purchase Purchases paid in month after purchase cent of sales 55% 75% 40% 60% Other Data 5% of sales 15% of sales 12,000 per month 25,000 per month 7,500 per quarter Other Expenses Salary Expense Advertising Depreciation Dividends in February Land Purchase in January Land Purchase in March 39,000 2,000 8,000 The company must maintain a minimum cash balance of $5,000. An open line of credit is available at a local bank for any borrowing that may be needed during the quarter. Borrowin and repayments are in multiples of $1,000 and are done at the beginning of a month. Intere is 1% per month and is paid each month based on the previous month's balance e. Create a tab titled "Cash" that looks like the image atthe top of the next page and make sure to follow the rules on the Microsoft Excel checklist. There are a few tricky cells here and the video will help you understand howto complete the formulas. Oakton Furniture Cash Budget For the Quarter Ended March 31 Februa Jan March Quarter 6 Beginning Cash Balance 7 Add Cash Receipts 8 Cash Available 9 Less Payments 10 For Inventory Purchases 11 For Operating Expenses 12 For Land Purchase 13 For Dividends 14 Total Budgeted Payments 15 Surplus/(Shortage) 16 Financing Activity 17 Borrowing 18 Interest Expense 19 Repayment 20 Ending Cash Balance 21 Check Figure: Ending Cash is $36,785 f. Create a tab title "Financials" and prepare the financial statements as of March 31. of the images belowwill be on the same tab. This uses your knowledge from ACC 153 as wel as Exhibit 9-17 from our textbook. The video will give you many hints! All three Oakton Furniture Income Statement For the Quarter Ended March 31 5 Revenues 6 Cost of Goods Sold 7 Gross Margin 8 Operating Expenses: 9 Salaries and wages 0 Advertising 11Shipping 12 Depreciation 13 Other Expenses 4 Total Operating Expenses 15 Net Operating Income 6 Less: Interest Expense 17 Net Income 18 Oakton Furniture Statement of Retained Earnings For the Quarter Ended March 31 20 21 23 24 Retained Earnings, December 31 25 Add: Net Income 26 Less: Dividends 27 Retained Earnings, March 31 28 Oakton Furniture Balance Sheet March 31 30 31 32 4 Current Assets: 35 Cash Accounts Receivable 38 Total current assets 39 Property and equipment, net 40 Total Assets 42 Current Llabilities 43 Accounts Payable 45 Stockholders Equity 46 Capital Stock Retained Eamings 8Total Stockholders Equity 9 Total Liabilities and Stockholders' Equity 50 Check Figures: Net Income is $116,460. (Video states that there is a Total Assets check figur but that is no longer provided. Just make sure Assets Liabilities+Stockholders' Equity) Trial Balance as of December 31 Debits Credits S40,000 70,000 119,625 145,000 Cash Accounts Receivable Inventory Property and Equipment (net) Accounts Pavable Capital Stock Retained Earnings $110,100 123,000 141,525 Sales Data $280,00 290,000 330,000 320,000 340,000 40% 60% December sales January sales February sales March sales April sales Credit Sales as a percent of sales Cash Sales as a percent of sales Purchases Data Cost of Goods Desired Inventory as percent of following month needs Purchases paid in month of purchase Purchases paid in month after purchase cent of sales 55% 75% 40% 60% Other Data 5% of sales 15% of sales 12,000 per month 25,000 per month 7,500 per quarter Other Expenses Salary Expense Advertising Depreciation Dividends in February Land Purchase in January Land Purchase in March 39,000 2,000 8,000