Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

I need assistance with part 4 of this accounting exercise! Please when you attach your excel sheet or attach the work, ensure that the formulas/entries

I need assistance with part 4 of this accounting exercise! Please when you attach your excel sheet or attach the work, ensure that the formulas/entries are in regards to my own excel sheet! thank you!

image text in transcribedimage text in transcribedimage text in transcribedimage text in transcribedimage text in transcribedimage text in transcribed
Excel File Edit View Insert Format Tools Data Window Help 100% Mon 2:56 PM Q E AutoSave O OFF ~ H E 6 5 - STERLING TEMPLATE Home Insert Draw Page Layout Formulas Data Review View Tell me Share Commen X Calibri (Body) 11 AA D Wrap Text v General Insert v x Delete AYOU Paste BIUV MVA Merge & Center v v % " Conditional Format Cell Format Sort & Find & deas Sensitivity Formatting as lable Styles Filter Select P19 X V fx A B C D E G H M N 0 P Q R U V w X Y Z AA AB AC AD AE AF FEB MAR APR MAY JUNE QTR NO MI A W N SALES Units 24000 28000 35000 45000 60000 140000 SP /unit 8.5 8.5 8.5 8.5 8.5 8.5 Sales revenue 204000 238000 297500 382500 510000 1190000 COLLECTIONS Current month 74375 95625 127500 297500 One month ago 119000 148750 191250 459000 10 Two months ago 51000 59500 74375 184875 Total collections 244375 303875 393125 941375 APRIL MAY JUNE QTR PURCHASES 6 Sales 35000 45000 60000 140000 7 Plus EI 40500 54000 36000 Total needed 36000 75500 99000 96000 76000 9 Less BI 31500 40500 54000 31500 10 Purchases - units 44000 58500 42000 144500 Cost/unit 2 Total cost of purchases 220000 292500 210000 722500 23 24 CASH PAYMENTS 5 Current month 10000 146250 105000 361250 Previous month 85750 110000 146250 342000 Total payments 195750 256250 251250 703250 APRIL MAY JUNE QTR SELLING & ADMIN Variable sales commissions 17500 22500 30000 70000 Fixed selling & admin Salaries 22000 22000 22000 66000 Utilities 14000 14000 14000 42000 Insurance 2400 2400 2400 7200 Depreciation 5000 15000 15000 45000 Miscellaneous 3000 3000 3000 9000 9 Total selling & admin 73900 78900 86400 239200 PT I PT II PT III PT IV + Ready - + 80 22 64 AUG 10 istv A W XExcel File Edit View Insert Format Tools Data Window Help 100% Mon 2:56 PM Q E AutoSave O OFF ~ H E 6 5 - STERLING TEMPLATE Home Insert Draw Page Layout Formulas Data Review View Tell me Share Commen Calibri (Body) 11 AA D Wrap Text v General Insert v [ v Six Delete AYOU Paste BIUV MVA Merge & Center v v % " Conditional Format Cell Styles Format Sort & Find & deas Sensitivity Formatting as lable Filter Select B27 X V fx A B C D E F G H K L M N O P Q R S T U V Menswear International - Sterling Tie Division Budgeted Income Statement For the Month Ended June 30th, 2020 April May June Qtr Budgeted Sales Revenue 297500 382500 510000 1190000 Less: Budgeted Cost of Goods Sold 220000 292500 210000 722500 Budgeted Gross Margin 77500 90000 300000 467500 Less:Budgeteds Selling and Administrative Expenses 73900 78900 8640 23920 9 Budgeted Net Operating Income 3600 11100 213600 228300 10 #1 Contribution Margin Ratio: (Unit Contribution Margin/ Sales Price) 41% 12 Quarterly budgeted break-even point in units 56400 #3 Margin of safety in units for the quarter 83600 15 #4 Operating leverage 2.15 #5 $350,000 for the quarter, how many units must it sell? 148343 Contribution Margin 3.5 19 20 PT I PT II PT III PT IV + Ready 222 64 AUG 10 itv WExcel File Edit View Insert Format Tools Data Window Help 100% Mon 2:57 PM Q E AutoSave O OFF ~ H E 6 5 - STERLING TEMPLATE Home Insert Draw Page Layout Formulas Data Review View Tell me Share Commen Calibri (Body) AA General Insert v 11 D Wrap Text v 5x Delete AYOU Paste BIUV MVA Merge & Center v v % " Conditional Format Cell Sort & Find & deas Sensitivity Formatting as lable Styles Format Filter Select J16 X V fx A B C D E F G H J K L M N O P Q R S T U V w Menswear International - Sterling Tie Division Cash Budge For the Month Ended June 30th, 2020 April May June QTR Beginning cash balance 44000 $ 24,125 $ 10,250 44000 Plus: Budgeted Cash Receipts 244375 303875 393125 94137 Total Cash Available 288375 $ 328,000 $ 403,375 985375 8 Less: 9 Total Payments 195750 256250 251250 703250 10 Variable Sales Commissions 17500 2250 3000 70000 11 Salaries 22000 22000 200 6000 Utilities 14000 14000 14000 42000 Miscelleneous 3000 3000 3000 9000 Dividend Paid 12000 0 200 15 Equipment Purchased 0 25000 2500 Total Cash Disburstmetns 264250 317750 345250 927250 Ending cash balance $ 24,125 $ 10,250 $ 58,12 $ 58,125 20 PT I PT II PT III PT IV + Ready + 100 222 64 AUG 10 stv A W XExcel File Edit View Insert Format Tools Data Window Help 100% Mon 2:57 PM Q E AutoSave O OFF ~ H E v 5 - STERLING TEMPLATE Home Insert Draw Page Layout Formulas Data Review View Tell me Share Commen Calibri (Body) 11 AA D Wrap Text v General Insert v 5x Delete AYOU Paste BIUV MVA Merge & Center v v % 9 Conditional Format Cell Sort & Formatting as lable Styles Format Find & deas Sensitivity Filter Select E26 X V fx A B C D E F G H K L M N O P Q R S T U V W X STERLING TIES BUDGETED BALANCE SHEET AT 6/30 AT 3/31 6 Cash 44000 Accounts receivable 229500 Inventory 157500 9 Prepaid insurance 14400 10 Property, plant and equipment, net 322700 11 Total Assets 768100 12 13 Accounts payable 85750 14 Dividends payable 12000 Interest payable 4500 Note payable 15000 Total liabilities 252250 18 19 Common stock 30000 Retained earnings 215850 Total stockholders' equity 515850 23 Total Liabilities and Stockholders' Equity 768100 38 D PT I PT II PT III PT IV + Ready - + 100 222 64 AUG 10 stv A W XADDITIONAL INFORMATION Sterling plans to purchase land for $25,000 cash in June. The company also declares dividends of $12,000 per quarter. The dividend is declared in the last month of the quarter and paid in the first month of the following quarter. At the end of each quarter, Sterling makes a tax payment equal to 20% of budgeted income before tax. For purposes of this project, you may assume that the payment is debited to income tax expense. At the beginning of the year, Sterling borrowed $150,000 on a five-year 12% note payable with interest to be paid annually at December 31. The division's balance sheet at March 31 is as follows: STERLING TIES BALANCE SHEET At March 31 ASSETS Cash $ 44.000 Accounts receivable 229,500 Inventory 157,500 Prepaid insurance 14,400 Property, plant and equipment, net 322.700 Total Assets $768.100 LIABILITIES AND STOCKHOLDERS' EQUITY Accounts payable $ 25 750 Dividends payable 12,000 Interest payable 4,500 Note payable 150,000 Common stock, no par 300,000 Retained earnings 215.850 Total Liabilities and Stockholders' Equity $768.100REQUIREMENTS PART 2 1) Prepare a budgeted income statement for the quarter only using the contribution format. List each cost separately. 2) At the bottom of your worksheet, answer the questions below. Use operating income (income before interest and taxes) in your calculations. Round your answers to the nearest whole unit, dollar or one percent as necessary. Be sure to label your answers. a. What is the division contribution margin ratio? b. What is the quarterly budgeted break-even point in units? c. What is the margin of safety in units for the quarter? d. What is operating leverage for the quarter? e. If the division wants to earn income before interest and taxes of $350,000 for the quarter, how many units must it sell? PART 3 Prepare a cash budget by month and for the quarter, listing each category of payment separately. (HINT: Ending cash balance at April 30 should be $24,125.) PART 4 1) The balance sheet information provided in the project data has been entered on a spreadsheet in your workbook. Prepare a comparative budgeted balance sheet at June 30 next to the first quarter balance sheet. If you cannot get your second quarter balance sheet to balance, enter the amount necessary to get it to balance as a separate line item and label this amount "plug." 2) At the bottom of your worksheet, answer the questions below. Round your answers to the nearest whole unit, dollar or one percent as necessary. a. What is the budgeted ROI for the quarter? b. If Menswear, International has established a minimum required rate of return of 18% for all divisions, what is budgeted residual income for the quarter? c. If the division vice president has set a target ROI of 25% for the division, how many units will the company have to sell for the quarter

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image_2

Step: 3

blur-text-image_3

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Intermediate Accounting

Authors: Donald E. Kieso, Jerry J. Weygandt, Terry D. Warfield

18th Edition

1119790972, 9781119790976

More Books

Students also viewed these Accounting questions