Solved by Expert Tutors
Solved by Expert Tutors
Question

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

Assumptons - San Marcos Emporium ExpecTed Sales: SepTember $ 202,000.00 OcTober $ 208,000.00 November $ 207,000.00 December $ 210,000.00 January $ 204,000.00 CrediT Sales 20% Cash Sales 80% Gross Margin 40% of sales CGS 60% of sales DEI 75% of nexT monTh's CGS Operatng Expenses: Commissions 10% of sales OTher Operatng Expense 15% of sales RenT $ 3,000.00 Depreciaton $ 1,500.00 Income ±ax RaTe 28% of Operatng 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 assumpton sheeT) OcTober Loan InTeresT RaTe 1% per monTh AsseTs Cash $ 12,000.00 AccounTs Receivable 40400.00 InvenTory 93600.00 PlanT & EquipmenT, neT 121750.00 ±oTal AsseTs $ 267,750.00 Liabilites & EquiTy AccounTs Payable $ 18,585.00 ReTained Earnings 249165.00 ±oTal liabilites & equiTy $ 267,750.00
Background image of page 1
October November December Budgeted Sales Revenue $ 208,000.00 $ 207,000.00 $ 210,000.00 Cash Sales $ 166,400.00 $ 165,600.00 $ 168,000.00 Credit Sales $ 41,600.00 $ 41,400.00 $ 42,000.00 Total Sales Revenue $ 208,000.00 $ 207,000.00 $ 210,000.00 Budgeted Cost of Goods Sold $ 124,800.00 $ 124,200.00 $ 126,000.00 Desired Ending Inventory 93150 94500 91800 Total Needs 217950 218700 217800 Beginning Inventory 93600 93150 94500 Required Purchases $ 124,350.00 $ 125,550.00 $ 123,300.00 Variable Opera±ng Expenses: Commissions $ 20,800.00 $ 20,700.00 $ 21,000.00 Other Opera±ng Expenses $ 31,200.00 $ 31,050.00 $ 31,500.00 Total Variable Opera±ng Expenses $ 52,000.00 $ 51,750.00 $ 52,500.00 Fixed Opera±ng Expenses: Rent $ 3,000.00 $ 3,000.00 $ 3,000.00 Deprecia±on $ 1,500.00 $ 1,500.00 $ 1,500.00 Total Fixed Opera±ng Expenses $ 4,500.00 $ 4,500.00 $ 4,500.00 Total Opera±ng Expense $ 56,500.00 $ 56,250.00 $ 57,000.00 Sales 208000 207000 210000 Cost of Goods Sold 124800 124200 126000 Gross Margin 83200 82800 84000 Opera±ng Expenses 56500 56250 57000 Opera±ng Income 26700 26550 27000 Interest Expense Income Taxes Net Income Collec±ons of: Cash Sales $ 166,400.00 $ 165,600.00 $ 168,000.00 Credit Sales $ 40,400.00 $ 41,600.00 $ 41,400.00 Total Collec±ons $ 206,800.00 $ 207,200.00 $ 209,400.00 Payments of: Current Month Purchases $ 105,697.50 $ 106,717.50 $ 104,805.00 Prior Month Purchases $ 18,585.00 $ 18,652.50 $ 18,832.50 Total Payments - Merchandise Inventory Purchases $ 124,282.50 $ 125,370.00 $ 123,637.50 Commissions $ 20,800.00 $ 20,700.00 $ 21,000.00 Rent $ 3,000.00 $ 3,000.00 $ 3,000.00 Other Opera±ng Expenses $ 31,200.00 $ 31,050.00 $ 31,500.00 Total Payments - Opera±ng Expenses $ 55,000.00 $ 54,750.00 $ 55,500.00
Background image of page 2
Show entire document
ACC 2362 Managerial Accounting: Excel Project #3 – Chap. 9 – Master Budget – Merchandising Company 1. Read these instrucTons completely before you begin the Excel project. 2. Academic Honesty: The project should refect your individual work. This is NO± 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 violaTon will be Fled. 3. Due date: ±riday, 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 a²er class has ended, you will NO± receive credit For the assignment. 4. Deliverables: A³ach 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 submi³ed to TRACS via the Drop Box Func´on in accordance with the due date and ´me stated above. You will not receive credit unless both the printed copy and electronic copy are turned in by the due date. No hand- wri³en assignments or e-mail a³achments will be accepted. 5. SpeciFc DirecTons: This assignment will not be done through My Accoun´ng Lab. It must be completed using Excel, a spreadsheet applica´on. Use the Excel Project #3 S17 template as a star´ng 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 violaTon will be Fled. ±irst, enter your assump´ons From the instruc´on sheet (Word Document) into the Excel template AssumpTons 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 assump´ons sheet within the Excel Workbook. All calcula´on cells in the spreadsheet should either be a Formula or a cell reFerence. All calcula´ons should be perFormed on the template spreadsheet and not on the assump´ons sheet within the Excel workbook. IF the amount From one budget Feeds into a subsequent budget, you should reFerence the budget For the inForma´on and not the assump´on sheet. Only reFerence your assump´on sheet when reFerencing the data, the µrst ´me. ±or example, inForma´on From the Sales Budget will be reFerenced on the Cost oF Goods Sold Budget, Budgeted Income Statement and Cash Collec´ons 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. InForma´on For months not part oF the Fourth quarter should be listed on the assump´on sheet. You should reFerence the assump´on sheet when you need to use this inForma´on 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 assump´on 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 projec´on changes, it will automa´cally change all the subsequent budgets. 1
Background image of page 1
You will need to format the budgets and the data on the assumpTon 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 presentaTon. Your assignment should be no more than 5 pages total (1 page - cover sheet, 1 page - assumpTon 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. PresentaTon of the informaTon 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 calculaTons, and completeness. 10 points will be for the use of technology and communicaTon of data. 7. Excel Help: See ±RACS – Resources for a sheet oFering Microso² O³ce keyboard short cuts. CIS tutoring lab McCoy 336 You±ube oFers many Excel tutorials. O³ce hours: GA Valerie Amadi: ±uesday, April 4 th and 11th from 2:30 p.m. to 4:30 p.m. in McCoy 442 Professors regularly scheduled o³ce hours. Come by the o³ce if you need help. 8. Lastly, remember, the project is due at the START of your class period. ±here is normally a backup in the computer lab on the due date with students prinTng the assignment at the last hour so don’t be caught. Remember, there are penal±es associated with late work. Don’t wait unTl 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. Sales Budget 2. Cost of Goods Sold, Inventory and Purchases Budget 3. OperaTng Expense Budget 4. Budgeted Income Statement 5. Schedule of Expected Cash CollecTons 6. Schedule of Expected Cash Disbursements - Merchandise Purchases 7. Schedule of Expected Cash Disbursements - OperaTng Expenses 8. Combined Cash Budget 9. Budgeted Balance Sheet ACC 2362 Managerial Accounting: Excel Project #3 – Chap. 9 – Master Budget – Merchandising Company ±he San Marcos Emporium Company is a merchandising business located downtown in San Marcos, ±exas. ±he owners are ±exas State alumni and they would like to maximize their pro´ts. ±hey understand that accurate budgeTng will help obtain this goal. ±he company is compleTng its third year of operaTons and is preparing to build its master budget for the fourth quarter of the year. ±he budget will detail each month’s acTvity and the total for the quarter. ±he master budget will be based on the following informaTon: 2
Background image of page 2
Show entire document
Sign up to view the entire interaction

Step-by-step answer

entesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac, dictum vitaacinia pulvi

llentesq

Answer 14042017 budget.xlsx
rem ipsum dolor sit amet, consect entesque dapibu at, ultri e vel l acinia p icitur l ultrices s s s acinia pulvi sque dapib , consecte ultrices a m ipsum dolor sit amet, at, ultrices a pulvinar to

Subscribe to view the full answer

Subject: Accounting, Business

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question