Sales Budget using microsoft excel

this is complete question

Sales budget using Microsoft excel Oakton Furniture prepares its master budget on a quarterly basis. The following data have been assembled to assist in preparation of the master budget for the first quarter: Sales are 60% for cash and 40% on cred. All payments on credit sales are collected in the month flowing ale. The accounts receivable at December 31 ar, a result of 1. Create a tab titled "Sales that looks like this and make sure to follow the rules on the Microsoft Excel checklist. The video will help you understand how to complete the formulas. Oakton Furniture Sales Budget For the Quarter Ended March 31 Total January FebruaryMarch 7 Cash Sales 8 Credit Sales Total Sales For the Quarter Ended March January FebruaryMach 6Cash Sales from this month T Credit Sales from last month Total t March 31 will be Check Figures: Total Sales for quarter are $940,000. Total Collections for quarter are $882,000. 2. Cost of Sales is 55%ofsales At the end of each month of purchase and 6 % in the month following the purchase. The accounts payable at December 31 were from December purchases that were not yet paid inventory is to be on hand equal to 75% of the flowingmonth's sales reed, stated at cost i ventory purchases are a disne emo nth Create a tab titled "Purchases" that looks like the image at the top of the next page and make sure to follow the rules on the Microsoft Excel checklist Oakton Furniture Pureahses Budget For the Quarter Ended March 31 lanuary February March Total Budgeted Cost of Sales 8 Desired Ending Inventory 9 Total lnventory Needs 10 Less Beginning Inventory 11 Required Purchases Oakton Furniture Schedule of Cash Payments for Inventery For the Quarter Ended Mareh 31 Total JanuaryFebruary March 17 18 Paid on curent month purchases 19 Pald on prior month parchanes Total Payments 22 Accounts Payable at March 31 will be Check Figures: Total Purchases for quarter are $537,625 and Total Payments for quarter are $537,17 3. Create a tab titled "Operating' that looks like the image below and use formulas to il lt in from information on the Data tab. Oakton Furniture Schedule of Cash Payments for Operating Expenses For the Quarter Ended March 31 anuary February March Total Salary Expense Advertising 9 Shipping 10 11 12 Other Expenses Cash payments for operating expenses Check Figure: Total Cash Payments for quarter are $299.000 4. 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 areinmultiples of $1,000 and are done at the beginning of a moreh. Interest Create a tab titled "Cash" that looks lke the i help you understand how to complete the formulas. 1% per month and h padeach month based on the previous month's balance. mage at the 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 wil Oakton Furniture Cash Budget For the Quarter Ended March 31 January Fe 6 Beginning Cash Balance 7 Add Cash Recelpts Cash Pvailable 9 Less Payments 10 For Inventory Purchases 111 For Operating Expenses 12 For Land Purchase For Dvidends Total Budgeted Payments 5 SurpluAShotage % Financing Activity T Borowin 19 Repayment 20 Ending Cash Balance Check Figure: Ending Cash is $36,785. S. Create a tab title "Financials" and prepare the financial statements as of March 31. All three of the images below will be on the same tab Check Figures: Net Income is $116,460. Just make sure As- sets Liabilities +Stockholders' Equity Income Statement 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