Hello i need some help finishing up my master budget for my managerial accounting.

My teacher had given us what two of our answers should be the first being the

December ending Cash after financing should equal $63,170

and the second being

Total Assets should equal $325,220

Assumptions - San Marcos Emporium Expected Sales: September October November December January $ 202,000.00 $ 208,000.00 $ 207,000.00 $ 210,000.00 $ 204,000.00 Credit Sales Cash Sales 20% 80% Gross Margin CGS DEI 40% of sales 60% of sales 75% of next month's CGS Operating Expenses: Commissions Other Operating Expense Rent $ Depreciation $ 10% of sales 15% of sales 3,000.00 1,500.00 Income Tax Rate 28% of Operating Income Inventory Purchases: 85% paid in month of purchase 15% paid in month following the purchase Equipment Purchases $ $ 8,000.00 3,000.00 New Borrowings (I's OK to determine how much you need to borrow and enter it the assumption sheet) October Loan Interest Rate Assets Cash Accounts Receivable Inventory Plant & Equipment, net Total Assets Liabilities & Equity Accounts Payable Retained Earnings Total liabilities & equity 1% per month $ 12,000.00 40400.00 93600.00 121750.00 $ 267,750.00 $ 18,585.00 249165.00 $ 267,750.00 Budgeted Sales Revenue October November December $ 208,000.00 $ 207,000.00 $ 210,000.00 Cash Sales Credit Sales Total Sales Revenue $ $ $ 166,400.00 $ 165,600.00 $ 168,000.00 41,600.00 $ 41,400.00 $ 42,000.00 208,000.00 $ 207,000.00 $ 210,000.00 Budgeted Cost of Goods Sold Desired Ending Inventory Total Needs Beginning Inventory Required Purchases $ 124,800.00 $ 124,200.00 $ 126,000.00 93150 94500 91800 217950 218700 217800 93600 93150 94500 124,350.00 $ 125,550.00 $ 123,300.00 Variable Operating Expenses: Commissions Other Operating Expenses Total Variable Operating Expenses Fixed Operating Expenses: Rent Depreciation Total Fixed Operating Expenses Total Operating Expense $ $ $ $ 20,800.00 $ 31,200.00 $ 52,000.00 $ 20,700.00 $ 21,000.00 31,050.00 $ 31,500.00 51,750.00 $ 52,500.00 $ $ $ $ 3,000.00 1,500.00 4,500.00 56,500.00 3,000.00 1,500.00 4,500.00 56,250.00 $ 3,000.00 $ 1,500.00 $ 4,500.00 $ 57,000.00 207000 124200 82800 56250 26550 210000 126000 84000 57000 27000 Sales Cost of Goods Sold Gross Margin Operating Expenses Operating Income Interest Expense Income Taxes Net Income Collections of: Cash Sales Credit Sales Total Collections $ $ $ $ 208000 124800 83200 56500 26700 $ $ $ 166,400.00 $ 165,600.00 $ 168,000.00 40,400.00 $ 41,600.00 $ 41,400.00 206,800.00 $ 207,200.00 $ 209,400.00 Payments of: Current Month Purchases $ Prior Month Purchases $ Total Payments - Merchandise Inventory Purchases $ 105,697.50 $ 106,717.50 $ 104,805.00 18,585.00 $ 18,652.50 $ 18,832.50 124,282.50 $ 125,370.00 $ 123,637.50 Commissions Rent Other Operating Expenses Total Payments - Operating Expenses $ $ $ $ 20,800.00 3,000.00 31,200.00 55,000.00 $ $ $ $ 20,700.00 3,000.00 31,050.00 54,750.00 $ 21,000.00 $ 3,000.00 $ 31,500.00 $ 55,500.00 Beginning Cash Balance Cash Collections Cash Available Cash Payments: Merchandise Inventory Purchases Operating Expenses Equipment Purchase Income Taxes Ending Cash Balance before Financing New Borrowings Debt Repayments Interest Payments Ending Cash Balance after Financing Cash Accounts Receivable Inventory Plant & Equipment, net Total assets Accounts Payable Retained Earnings Total liabilities & equity $ $ $ 12,000.00 $ 50,407.22 206,800.00 $ 207,200.00 218,800.00 $ 257,607.22 $ $ 124,282.50 55,000.00 0 28% 39,517.22 $ $ $ 125,370.00 $ 54,750.00 $ 8,000.00 28% $ 69,486.94 11000 0 -110 50,407.22 $ 0 0 -220 69,266.94 Total $ 625,000.00 $ 500,000.00 $ 125,000.00 $ 625,000.00 $ 375,000.00 279450 654450 281250 $ 373,200.00 $ 62,500.00 $ 93,750.00 $ 156,250.00 $ 9,000.00 $ 4,500.00 $ 13,500.00 $ 169,750.00 625000 375000 250000 169750 80250 $ 500,000.00 $ 123,400.00 $ 623,400.00 $ 317,220.00 $ 56,070.00 $ 373,290.00 $ 62,500.00 $ 9,000.00 $ 93,750.00 $ 165,250.00 ACC 2362 Managerial Accounting: Excel Project #3 - Chap. 9 - Master Budget - Merchandising Company 1. Read these instructions completely before you begin the Excel project. 2. Academic Honesty: The project should reflect your individual work. This is NOT a group project - sharing answers or spreadsheet formats is considered academic dishonesty. If you use a spreadsheet from a prior semester or someone else's spreadsheet, you will receive a zero for the project and an honor code violation will be filed. 3. Due date: Friday, April 14th at the START of your class period or earlier. If you arrive more than 10 minutes late to class to turn in your project, there will be a 25% penalty. If you turn it in after class has ended, you will NOT receive credit for the assignment. 4. Deliverables: Attach a copy of the cover sheet to a printed copy of the completed Excel spreadsheet which will be turned in during your class period. An electronic copy of the Excel spreadsheet only (no cover sheet) will be submitted to TRACS via the Drop Box function in accordance with the due date and time stated above. You will not receive credit unless both the printed copy and electronic copy are turned in by the due date. No handwritten assignments or e-mail attachments will be accepted. 5. Specific Directions: This assignment will not be done through My Accounting Lab. It must be completed using Excel, a spreadsheet application. Use the Excel Project #3 S17 template as a starting point. If you use a spreadsheet from a prior semester or someone else's spreadsheet, you will receive a zero for the project and an honor code violation will be filed. First, enter your assumptions from the instruction sheet (Word Document) into the Excel template Assumptions sheet. This will serve as your data source for all your other cells in Excel template. No numbers should be entered directly into the Excel template spreadsheet other than what you enter into the assumptions sheet within the Excel Workbook. All calculation cells in the spreadsheet should either be a formula or a cell reference. All calculations should be performed on the template spreadsheet and not on the assumptions sheet within the Excel workbook. If the amount from one budget feeds into a subsequent budget, you should reference the budget for the information and not the assumption sheet. Only reference your assumption sheet when referencing the data, the first time. For example, information from the Sales Budget will be referenced on the Cost of Goods Sold Budget, Budgeted Income Statement and Cash Collections Budget. The budgets should only show each month in the fourth quarter (October, November, & December), and a total for the quarter for every budget except the Balance Sheet. The Balance sheet should only be done at the end of the fourth quarter. Information for months not part of the fourth quarter should be listed on the assumption sheet. You should reference the assumption sheet when you need to use this information to calculate items needed for the fourth quarter. Once you have determined how much you need to borrow based on the company's policy you can list this amount on the assumption sheet then reference it on the budget sheet. Assume all funds are borrowed at the end of the month and repaid at the end of the end of the quarter. You may add rows as needed to display the appropriate headings. The schedules should be presented in the same order listed under the requirements. All the budgets should be linked together so that if the sales projection changes, it will automatically change all the subsequent budgets. 1 You will need to format the budgets and the data on the assumption sheet so it is easily readable & understandable. You need to format your budget schedules to include underlining, dollar signs and decimal places. Be consistent in your presentation. Your assignment should be no more than 5 pages total (1 page - cover sheet, 1 page - assumption sheet & 3 pages - budgets) printed portrait on only on one side of the page (no double-sided). Page numbers should be added within Excel. 6. Grading: Assignment is worth 25 points. Presentation of the information and how easily it can be read will be considered in the grading of the project. 15 points will be for the content, accuracy of the calculations, and completeness. 10 points will be for the use of technology and communication of data. 7. Excel Help: See TRACS - Resources for a sheet offering Microsoft Office keyboard short cuts. CIS tutoring lab McCoy 336 YouTube offers many Excel tutorials. Office hours: GA Valerie Amadi: Tuesday, April 4 th and 11th from 2:30 p.m. to 4:30 p.m. in McCoy 442 Professors regularly scheduled office hours. Come by the office if you need help. 8. Lastly, remember, the project is due at the START of your class period. There is normally a backup in the computer lab on the due date with students printing the assignment at the last hour so don't be caught. Remember, there are penalties associated with late work. Don't wait until the last minute to work on it, print out the results to review it for accuracy. Do your own work! Required: Prepare a master budget for the San Marcos Emporium Company for the fourth quarter of 2017. The following component budgets must be included: 1. 2. 3. 4. 5. 6. 7. 8. 9. Sales Budget Cost of Goods Sold, Inventory and Purchases Budget Operating Expense Budget Budgeted Income Statement Schedule of Expected Cash Collections Schedule of Expected Cash Disbursements - Merchandise Purchases Schedule of Expected Cash Disbursements - Operating Expenses Combined Cash Budget Budgeted Balance Sheet ACC 2362 Managerial Accounting: Excel Project #3 - Chap. 9 - Master Budget - Merchandising Company The San Marcos Emporium Company is a merchandising business located downtown in San Marcos, Texas. The owners are Texas State alumni and they would like to maximize their profits. They understand that accurate budgeting will help obtain this goal. The company is completing its third year of operations and is preparing to build its master budget for the fourth quarter of the year. The budget will detail each month's activity and the total for the quarter. The master budget will be based on the following information: 2 a. Sales were budgeted at $202,000 for September. Expected sales are $208,000 for October, $207,000 for November, $210,000 for December, and $204,000 for January 2018. b. The gross margin is 40% of sales. c. Sales are projected to be 80% in cash and 20% on credit. Credit sales are collected in the month following the sale. The September accounts receivable are a result of the September credit sales. There are no bad debts. d. Each month's ending inventory should equal 75% of the next month's budgeted cost of goods sold. e. Merchandise Inventory Purchases are paid as follows; 85% of a month's inventory purchases are paid for in the month of purchase; the remaining 15% is paid for in the following month. The accounts payable at September 30 are the result of September purchases of inventory. f. Monthly operating expenses are as follows: commissions are 10% of sales; rent is $3,000 per month, other operating expenses (excluding depreciation) are 15% of sales. Assume these expenses are paid in cash each month. Deprecation is $1,500 per month. g. November equipment purchases cost $8,000, and December equipment purchases cost $3,000. All equipment purchases are paid for in cash in the month purchased. h. Income tax is estimated to be 28% of operating income. Estimated taxes are accrued each month and paid in cash in the last month of the quarter. i. Management would like to maintain a minimum cash balance of at least $50,000 at the end of each month. The company has an agreement with a local bank that allows them to borrow in increments of $1,000 at the end of each month. The interest rate on these loans is 1% per month and for simplicity we will assume that interest is not compounded (only paying interest on the principal). They would, as far as it is able, repay the loan plus accumulated interest in the last month of the quarter. j. The projected balance sheet as of September 30, is as follows: Assets Cash Accounts Receivable Inventory Plant & Equipment, net Total assets September 30 $12,000.00 40,400.00 93,600.00 121,750.00 $267,750.00 Liabilities & Equity Accounts Payable Retained Earnings Total liabilities & equity $18,585.00 249,165.00 $267,750.00 3 Assumptions - San Marcos Emporium Expected Sales: September October November December January $ $ $ $ $ 202,000.00 208,000.00 207,000.00 210,000.00 204,000.00 Credit Sales Cash Sales 20% 80% Gross Margin CGS DEI 40% of sales 60% of sales 75% of next month's CGS Operating Expenses: Commissions Other Operating Expense Rent $ Depreciation $ 10% of sales 15% of sales 3,000.00 1,500.00 Income Tax Rate 28% of Operating Income Inventory Purchases: 85% paid in month of purchase 15% paid in month following the purchase Equipment Purchases $ $ 8,000.00 3,000.00 Minimum Cash balance $ 50,000.00 New Borrowings (I's OK to determine how much you need to borrow and enter it the assumption sheet) October Loan Interest Rate Assets Cash Accounts Receivable Inventory Plant & Equipment, net Total Assets Liabilities & Equity Accounts Payable 1% per month $ 12,000.00 40400.00 93600.00 121750.00 $ 267,750.00 $ 18,585.00 Retained Earnings Total liabilities & equity 249165.00 $ 267,750.00 Budgeted Sales Revenue Sales Budget October November December $ 208,000 $ 207,000 $ 210,000 Cash Sales Credit Sales Total Sales Revenue $ $ $ Budgeted Cost of Goods Sold Desired Ending Inventory Total Needs Beginning Inventory Required Purchases 166,400 $ 41,600 $ 208,000 $ 165,600 $ 41,400 $ 207,000 $ Cost of Goods Sold, Inventory and Purchase Budget $ 124,800 $ 124,200 $ 93,150 $ 94,500 $ 217,950 $ 218,700 $ 93,600 $ 93,150 $ 124,350 $ 125,550 168,000 42,000 210,000 $ $ $ $ $ 126,000 91,800 217,800 94,500 123,300 Operating Expenses Budget Variable Operating Expenses: Commissions Other Operating Expenses Total Variable Operating Expenses Fixed Operating Expenses: Rent Depreciation Total Fixed Operating Expenses Total Operating Expense Sales Cost of Goods Sold Gross Margin Operating Expenses Operating Income Interest Expense Income Taxes Net Income $ $ $ 20,800 $ 31,200 $ 52,000 $ 20,700 $ 31,050 $ 51,750 $ 21,000 31,500 52,500 $ $ $ $ 3,000 1,500 4,500 56,500 $ $ $ $ 3,000 1,500 4,500 56,250 $ $ $ $ 3,000 1,500 4,500 57,000 Budgeted Income Statement $ 208,000 $ $ 124,800 $ $ 83,200 $ $ 56,500 $ $ 26,700 $ $ - $ $ 7,476 $ $ 19,224 $ 207,000 124,200 82,800 56,250 26,550 110 7,434 19,006 $ $ $ $ $ $ $ $ 210,000 126,000 84,000 57,000 27,000 110 7,560 19,330 165,600 $ 41,600 $ 207,200 $ 168,000 41,400 209,400 Schedule of Expected Cash Collection Collections of: Cash Sales Credit Sales Total Collections $ $ $ 166,400 $ 40,400 $ 206,800 $ Schedule of Expected Cash Disbursements- Merchandise Purchases Payments of: Current Month Purchases $ 105,698 $ 106,718 $ 104,805 Prior Month Purchases $ 18,585 $ 18,653 $ 18,833 Total Payments - Merchandise Inventory Purchases $ 124,283 $ 125,370 $ 123,638 Commissions Rent $ $ 20,800 $ 3,000 $ 20,700 $ 3,000 $ 21,000 3,000 Other Operating Expenses $ 31,200 $ 31,050 $ 31,500 Total Payments - Operating Expenses $ 55,000 $ 54,750 $ 55,500 Combined Cash Budget $ 12,000 $ $ 206,800 $ $ 218,800 $ 50,518 $ 207,200 $ 257,718 $ 69,598 209,400 278,998 123,638 55,500 3,000 22,470 74,390 Beginning Cash Balance Cash Collections Cash Available Cash Payments: Merchandise Inventory Purchases Operating Expenses Equipment Purchase Income Taxes Ending Cash Balance before Financing $ $ $ 124,283 $ 55,000 $ - $ $ 39,518 $ 125,370 $ 54,750 $ 8,000 $ $ 69,598 $ New Borrowings Debt Repayments Interest Payments $ $ 11,000 $ $ ### $ (110) $ ### (11,000) (220) Ending Cash Balance after Financing $ 50,518 $ 69,598 $ 63,170 Budgeted Balance Sheet Cash Accounts Receivable Inventory Plant & Equipment, net Total assets $ $ $ $ $ 63,170 42,000 91,800 128,250 325,220 Accounts Payable Retained Earnings Total liabilities & equity $ $ $ 18,495 306,725 325,220 Total $ 625,000 $ $ $ 500,000 125,000 625,000 $ $ $ $ $ 375,000 91,800 466,800 93,600 373,200 $ $ $ 62,500 93,750 156,250 $ $ $ $ 9,000 4,500 13,500 169,750 $ $ $ $ $ $ $ $ 625,000 375,000 250,000 169,750 80,250 220 22,470 57,560 $ $ $ 500,000 123,400 623,400 $ 317,220 s 208000 124800 83200 56500 26700 180 7426 19094 207000 124200 82800 56250 26550 180 7384 18936 210000 126000 84000 57000 27000 180 7510 19260 625000 375000 250000 169750 80250 540 22319 57290 $ 56,070 $ 373,290 $ $ 62,500 9,000 $ 93,750 $ 165,250 $ $ $ 12,000 623,400 635,400 $ $ $ $ $ 373,290 165,250 11,000 22,470 63,390 $ $ $ 11,000 (11,000) (220) $ 63,170 Assumptions - San Marcos Emporium Expected Sales: September October November December January $ $ $ $ $ 202,000.00 208,000.00 207,000.00 210,000.00 204,000.00 Credit Sales Cash Sales 20% 80% Gross Margin CGS DEI 40% of sales 60% of sales 75% of next month's CGS Operating Expenses: Commissions Other Operating Expense Rent $ Depreciation $ 10% of sales 15% of sales 3,000.00 1,500.00 Income Tax Rate 28% of Operating Income Inventory Purchases: 85% paid in month of purchase 15% paid in month following the purchase Equipment Purchases $ $ 8,000.00 3,000.00 Minimum Cash balance $ 50,000.00 New Borrowings (I's OK to determine how much you need to borrow and enter it the assumption sheet) October Loan Interest Rate Assets Cash Accounts Receivable Inventory Plant & Equipment, net Total Assets Liabilities & Equity Accounts Payable 1% per month $ 12,000.00 40400.00 93600.00 121750.00 $ 267,750.00 $ 18,585.00 Retained Earnings Total liabilities & equity 249165.00 $ 267,750.00 Budgeted Sales Revenue Sales Budget October November December $ 208,000 $ 207,000 $ 210,000 Cash Sales Credit Sales Total Sales Revenue $ $ $ Budgeted Cost of Goods Sold Desired Ending Inventory Total Needs Beginning Inventory Required Purchases 166,400 $ 41,600 $ 208,000 $ 165,600 $ 41,400 $ 207,000 $ Cost of Goods Sold, Inventory and Purchase Budget $ 124,800 $ 124,200 $ 93,150 $ 94,500 $ 217,950 $ 218,700 $ 93,600 $ 93,150 $ 124,350 $ 125,550 168,000 42,000 210,000 $ $ $ $ $ 126,000 91,800 217,800 94,500 123,300 Operating Expenses Budget Variable Operating Expenses: Commissions Other Operating Expenses Total Variable Operating Expenses Fixed Operating Expenses: Rent Depreciation Total Fixed Operating Expenses Total Operating Expense Sales Cost of Goods Sold Gross Margin Operating Expenses Operating Income Interest Expense Income Taxes Net Income $ $ $ 20,800 $ 31,200 $ 52,000 $ 20,700 $ 31,050 $ 51,750 $ 21,000 31,500 52,500 $ $ $ $ 3,000 1,500 4,500 56,500 $ $ $ $ 3,000 1,500 4,500 56,250 $ $ $ $ 3,000 1,500 4,500 57,000 Budgeted Income Statement $ 208,000 $ $ 124,800 $ $ 83,200 $ $ 56,500 $ $ 26,700 $ $ - $ $ 7,476 $ $ 19,224 $ 207,000 124,200 82,800 56,250 26,550 110 7,434 19,006 $ $ $ $ $ $ $ $ 210,000 126,000 84,000 57,000 27,000 110 7,560 19,330 165,600 $ 41,600 $ 207,200 $ 168,000 41,400 209,400 Schedule of Expected Cash Collection Collections of: Cash Sales Credit Sales Total Collections $ $ $ 166,400 $ 40,400 $ 206,800 $ Schedule of Expected Cash Disbursements- Merchandise Purchases Payments of: Current Month Purchases $ 105,698 $ 106,718 $ 104,805 Prior Month Purchases $ 18,585 $ 18,653 $ 18,833 Total Payments - Merchandise Inventory Purchases $ 124,283 $ 125,370 $ 123,638 Commissions Rent $ $ 20,800 $ 3,000 $ 20,700 $ 3,000 $ 21,000 3,000 Other Operating Expenses $ 31,200 $ 31,050 $ 31,500 Total Payments - Operating Expenses $ 55,000 $ 54,750 $ 55,500 Combined Cash Budget $ 12,000 $ $ 206,800 $ $ 218,800 $ 50,518 $ 207,200 $ 257,718 $ 69,598 209,400 278,998 123,638 55,500 3,000 22,470 74,390 Beginning Cash Balance Cash Collections Cash Available Cash Payments: Merchandise Inventory Purchases Operating Expenses Equipment Purchase Income Taxes Ending Cash Balance before Financing $ $ $ 124,283 $ 55,000 $ - $ $ 39,518 $ 125,370 $ 54,750 $ 8,000 $ $ 69,598 $ New Borrowings Debt Repayments Interest Payments $ $ 11,000 $ $ ### $ (110) $ ### (11,000) (220) Ending Cash Balance after Financing $ 50,518 $ 69,598 $ 63,170 Budgeted Balance Sheet Cash Accounts Receivable Inventory Plant & Equipment, net Total assets $ $ $ $ $ 63,170 42,000 91,800 128,250 325,220 Accounts Payable Retained Earnings Total liabilities & equity $ $ $ 18,495 306,725 325,220 Total $ 625,000 $ $ $ 500,000 125,000 625,000 $ $ $ $ $ 375,000 91,800 466,800 93,600 373,200 $ $ $ 62,500 93,750 156,250 $ $ $ $ 9,000 4,500 13,500 169,750 $ $ $ $ $ $ $ $ 625,000 375,000 250,000 169,750 80,250 220 22,470 57,560 $ $ $ 500,000 123,400 623,400 $ 317,220 s 208000 124800 83200 56500 26700 180 7426 19094 207000 124200 82800 56250 26550 180 7384 18936 210000 126000 84000 57000 27000 180 7510 19260 625000 375000 250000 169750 80250 540 22319 57290 $ 56,070 $ 373,290 $ $ 62,500 9,000 $ 93,750 $ 165,250 $ $ $ 12,000 623,400 635,400 $ $ $ $ $ 373,290 165,250 11,000 22,470 63,390 $ $ $ 11,000 (11,000) (220) $ 63,170