My project has 4 parts (i have already completed parts 1-3 which are excel sheets 1-3 as well) I only need help with completing part 4 (excel sheet 4) please feel free to work directly on my sheet! there is ADDITIONAL INFORMATION under part 4 all the way down in this description.
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 $85,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,100
REQUIREMENTS
PART 4
1) The balance sheet information provided in the project data has been entered on a spreadsheet in your workbook.Work to complete 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
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 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 XExcel File Edit View Insert Format Tools Data Window Help 98% Tue 12:30 PM Q AutoSave OFF 3Managerial Accounting Project (Part IV) - Saved to my Mac Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Arial 11 AA D Wrap Text v General Insert v E 5x Delete Paste BIUV MVAv Merge & Center v $ ~ % " Conditional Format Cell Sort & Find & Ideas Sensitivity Formatting as lable Styles Filter Select J20 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 Y Z NOLAWN Menswear International - Sterling Tie Division Cash Budget 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 941375 Total Cash Available 288375 $ 328,000 $ 403,375 85375 8 Less: 9 Total Payments 195750 256250 251250 703250 10 Variable Sales Commissions 17500 22500 30000 70000 11 Salaries 2000 22000 22000 66000 12 Utilities 14000 14000 14000 42000 Miscelleneous 3000 3000 3000 9000 Dividend Paid 12000 12000 15 Equipment Purchased 0 25000 25000 Total Cash Disburstmetns 264250 317750 34525 27250 Ending cash balance $ 24,125 $ 10,250 $ 58,125 58,125 PT I PT II PT III PT IV + 100% 238 18 AUG 11 itv W X
