Master budgeting project for ACC 2362. It is due at 10:00 am and I can't figure out what to do at all.

#Hayden Weir ##H#a#y#d#e#n# #W#e#i#r# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # f#7 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, November 13th 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 assignment 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 hand written 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. You may use the Excel Project #3 F15 template as a starting point. First, you will need to enter your assumptions in the template which will serve as your data source for all your other cells in Excel. No numbers should be entered directly into the 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. If the amount from one budget feeds into a subsequent budget you should reference the budget for the information and not the assumptions sheet. Only reference your assumption sheet when referencing the data the first time. For example, information from the Sales Budget will be referenced for the Cost of Goods Sold Budget, Budgeted Income Statement and Cash Collections Budget. All calculations should be performed on the budget spreadsheet and not on the assumptions sheet. You may add rows and columns as needed to perform necessary calculations and add 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. You will need to format the budgets and the data on the assumption sheet so it is easily readable & understandable. A single underline indicates a sub-total and a double underline indicates a total. Your assignment should be no more than 5 pages total (cover sheet, assumption sheet & budgets) printed on only on one side of the page (no double-sided). Page numbers should be added within Excel. 6. Grading: Assignment is worth 35 points. Presentation of the information and how easily it can be read will be considered in the grading of the project. 20 points will be for the content, accuracy of the calculations, and completeness. 15 points will be for the use of technology and communication of data. 1 7. Excel Help: See TRACS - Resources for Microsoft Office keyboard short cuts. YouTube offers many Excel tutorials Come by my office if you need help. 8. Lastly, remember, the project is due at the START of your class period. There is normally a back up 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 Bobcat Boutique for the second quarter of 2016. 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 2 ACC 2362 Managerial Accounting: Excel Project #3 - Chap. 9 - Master Budget - Merchandising Company Bobcat Boutique 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 second year of operations and is preparing to build its master budget for the second quarter. The budget will detail each quarter's activity and the total for the quarter. The master budget will be based on the following information: a. Sales were budgeted at $50,000 for March. Expected sales are $60,000 for April, $72,000 for May, $90,000 for June, and $48,000 for July. b. The gross margin is 25% of sales. c. Sales are projected to be 60% for cash and 40% on credit. Credit sales are collected in the month following the sale. The March accounts receivable are a result of the March credit sales. There are no bad debts. d. Each month's ending inventory should equal 80% of the following month's budgeted cost of goods sold. e. One half of a month's inventory purchases is paid for in the month of purchase; the other half is paid for in the following month. The accounts payable at March 31 are the result of March purchases of inventory. f. Monthly operating expenses are as follows: commissions are 12% of sales; rent is $2,500 per month, other operating expenses (excluding depreciation) are 6% of sales. Assume these expenses are paid monthly. Deprecation is $900 per month. g. Equipment costing $1,500 will be purchased for cash in April. h. Management would like to maintain a minimum cash balance of at least $4,000 at the end of each month. The boutique has an agreement with a local bank that allows them to borrow in increments of $1,000 at the beginning of each month, up to a total loan balance of $20,000. The interest rate on these loans is 1% per month and for simplicity we will assume that interest is not compounded. They would, as far as it is able, repay the loan plus accumulated interest at the end of the quarter. i. The balance sheet as of March 31, is as follows: Assets Cash Accounts Receivable Inventory Plant & Equipment, net Total assets March 31 $8,000 20,000 36,000 120,000 $184,000 Liabilities & Equity Accounts Payable Retained Earnings Total liabilities & equity $21,750 162,250 $184,000 3 Assumptions Expected Sales: December January February March April Credit Sales Cash Sales Gross Margin CGS DEI of sales of sales of next month's CGS Operating Expenses: Commissions of sales Other Operating Expeof sales Rent Depreciation Inventory Purchases paid in month of purchase paid in month following the purchase Equipment Purchases Income Tax Rate of Operating Income Assets Cash Accounts Receivable Inventory Plant & Equipment, net Liabilities & Equity Accounts Payable Retained Earnings New Borrowings (I's OK to determine how much you need to borrow and enter it the assumption sheet) January Loan Interest Rate per month January February March Budgeted Sales Revenue Total Cash Sales Credit Sales Total Sales Revenue Budgeted Cost of Goods Sold Desired Ending Inventory Total Needs Beginning Inventory Required Purchases 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 Cash Sales Credit Sales Total Collections Collections of Current Month Purchases Prior Month Purchases Total Payments - Merchandise Inventory Purchases Commissions Rent Other Operating Expenses Total Payments - Operating Expenses 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