Copy of Module 11 Student Excel Templates FINAL SOLUTIONS

xls

School

Harvard University *

*We aren’t endorsed by this school

Course

S- 2600

Subject

Accounting

Date

Jan 9, 2024

Type

xls

Pages

28

Uploaded by nataliehrycak1

Report
M11-15 a. Ending Cash Balance, 2019 Total Sales Revenue, 2019 2019 Cash % of Sales * 2020 Estimate Sales b. Original Estimated Cash Balance - 2020 Updated Estimated Cash Balance - 2020 c. Adjust the Original Estimated Cash Balance - 2020 by $14,173 d. Adjust Long-Term Debt for Updated Cash Balance Cash Flow - Operations Cash Flow - Investing Cash Flow - Financing Net Change in Cash Beginning Cash Balance
Ending Cash Balance e. Adjust Marketable Securities for Updated Cash Balance Cash Flow - Operations Cash Flow - Investing Cash Flow - Financing Net Change in Cash Beginning Cash Balance Ending Cash Balance M11-18 a. Historic ratio of Depreciation expense to PPE, net Forecasted Depreciation expense (2020) Historic ratio of CAPEX to Sales
Forecasted CAPEX (2020) Forecasted PPE, NET Amount M11-19 a. Dividend payout ratio (2019) Forecasted Dividends (2020) Forecasted Retained earnings (2020)
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
E11-30 $ millions Sales Growth Rate per Year NOPM (Net Operating Profit Margin Percentage) NOPAT: Net Operating Profit Margin % * EST Sales NOAT (Net Operating Asset Turnover) NOA: EST Sales / Net Operating Asset Turnover
E11-31 $ millions Sales Growth Rate per Year NOPM (Net Operating Profit Margin Percentage) NOPAT: Net Operating Profit Margin % * EST Sales NOAT (Net Operating Asset Turnover) NOA: EST Sales / Net Operating Asset Turnover
$ 51,141 7.22% Cash % of Sales $ 708,554 $ 740,439 7.22% $ 53,442 2020 Estimated $ 39,269 $ 53,442 $ (14,173) 3 Million Original Revised $ 57,696 $ 57,696 $ (14,908) $ (14,908) $ (54,660) $ (40,487) $ (11,872) $ 2,301 $ 51,141 $ 51,141
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
$ 39,269 $ 53,442 Original Revised $ 57,696 $ 57,696 $ (14,908) $ (735) $ (54,660) $ (54,660) $ (11,872) $ 2,301 $ 51,141 $ 51,141 $ 39,269 $ 53,442 POOL Depreciation Exp $ 895.00 BASE Gross PPE $ 10,259.00 Historic ratio of Depreciation Expense Gross PPE - 2019 Forecasted Depreciation expense (2020) POOL Capital Exp (CAPEX) $ 1,134.00 BASE 2019 Sales $ 30,557.00
Forecasted Sales - 2020 Historic ratio of CAPEX to Sales Forecasted CAPEX (2020) Beginning Balance $ 4,675.00 ADD Capital Expenditures (CAPEX) $ 1,224.74 LESS Depreciation Expense $ (952.67) Ending Balance $ 4,947.07 POOL Dividends - 2019 $ 2,681.40 BASE Net Income - 2019 $ 4,908.00 Dividend Payout Ratio - 2019 54.63% Net Income - 2020 $ 4,909.17 Beginning Balance $ 26,270.00
ADD Net Income $ 4,909.17 LESS Net Loss $ - LESS Dividends $ (2,682.04) Ending Balance $ 28,497.13 Actual 2019 Estimated 2020 2021 $ 75,356 $ 78,069 $ 80,879 3.6% 3.6% 4.3% 4.3% 4.3% $ 3,264 $ 3,357 $ 3,478 3.59 3.59 3.59 $ 21,016 $ 21,746 $ 22,529
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Actual 2019 Estimated 2020 2021 $ 2,788,322 $ 2,983,505 $ 3,192,350 7.0% 7.0% 7.58% 7.58% 7.58% $ 211,362 $ 226,150 $ 241,980 4.88 4.88 4.88 $ 571,823 $ 611,374 $ 654,170
s d Cash
8.72% 8.72% $ 10,920 $ 953 3.71%
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
$ 33,002 3.71% $ 1,225 2019 2020 2020 2020 54.63% $ 2,682.04 2019
2020 2020 2020 2020 2022 2023 $ 83,791 $ 86,807 3.6% 3.6% 4.3% 4.3% $ 3,603 $ 3,733 3.59 3.59 $ 23,340 $ 24,180
2022 2023 $ 3,415,814 $ 3,654,921 7.0% 7.0% 7.58% 7.58% $ 258,919 $ 277,043 4.88 4.88 $ 699,962 $ 748,959
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
2019 2020 Net sales $30,557 $33,001.56 Costs and expenses Cost of products sold 9,155 $9,900.47 Research and development expense 2,330 $2,508.12 Selling, general, and administrative expense 10,418 $11,253.53 Amortization of intangible assets 1,764 $1,914.09 Restructuring charges, net 198 148.5 Certain litigation charges, net 166 150 Other operating expense, net 258 258 Operating profit 6,268 $6,868.85 Other nonoperating income, net -373 -373 Interest expense 1,444 1,444 Income before income taxes 5,197 $5,797.85 Income tax provision 547 $869.68 Net income 4,650 $4,928.17 Net income loss attributable to noncontrolling interests -19 -19 Net income attributable to Medtronic $4,631 $4,909
E11-25 MEDTRO Forecast State $ millions Net sales $ 30,557 8% Increase i Costs and expenses Cost of products sold $ 9,155 30% of Net Sa Research and development expense $ 2,330 7.6% of Net S Selling, general, and admin expense $ 10,418 34.1% of Net S Amortization of intangible assets $ 1,764 5.8% of Net S Restructuring charges, net $ 198 75% of 2019 R Certain litigation charges $ 166 Amount for Li Other operating expense, net $ 258 No change fro Operating profit (loss) $ 6,268 Other non-operating income, net $ (373) No change fro Interest expense $ 1,444 No change fro Income (loss) before income taxes $ 5,197 Income tax provision $ 547 $ 5,798 Net income (loss) $ 4,650 Net (income) loss attrib to NCI $ (19) Net income (loss) attrib to Medtronic $ 4,631 Actual (2019)
MEDTRO Forecasted B $ millions Actual Cash and cash equivalents $ 4,393 Investments $ 5,455 $ 6,222 Inventories, net $ 3,753 Other current assets $ 2,144 Total current assets $ 21,967 Property, plant, and equipment, net $ 4,675 CAPEX increa Goodwill $ 39,959 Other intangible assets, net $ 20,560 Tax assets $ 1,519 Other assets $ 1,014 Total assets $ 89,694 Current liabilities Current debt obligations $ 838 Accounts payable $ 1,953 Accrued compensation $ 2,189 Accrued income taxes $ 567 Other accrued expenses $ 2,925 Total current liabilities $ 8,472 Long-term debt $ 24,486 $ 1,651 Accrued income taxes $ 2,838 Accounts receivable, less allowance of $190 Accrued compensation and retirement benefits
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Deferred tax liabilities $ 1,278 Other liabilities $ 757 Total liabilities $ 39,482 Shareholders’ equity $ - Additional paid-in capital $ 26,532 Retained earnings $ 26,270 $ 4,928 $ (2,711) Total shareholders’ equity $ 50,091 Noncontrolling interests $ 121 Total equity $ 50,212 Total liabilities and equity $ 89,694 Ordinary shares— par value $0.0001, 2.6 billion shares authorized, 1,340,697,595 and 1,354,218,154 shares issued and outstanding, respectively Accumulated other comprehensive loss
ONIC PLC ement of Income Forecast Assumptions in sales - 2019 to 2020 8.00% $ 33,002 ales in 2020 30.00% $ 9,900 Sales in 2020 7.60% $ 2,508 Sales in 2020 34.10% $ 11,254 Sales in 2020 5.80% $ 1,914 Restructuring Charges 75.00% $ 149 itigation was given $ 150.00 $ 150 om 2019 to 2020 $ 258 $ 6,869 om 2019 to 2020 $ (373) om 2019 to 2020 $ 1,444 $ 5,798 15% of Pretax Income 15.00% $ 870 $ 4,928 $ (19) $ 4,909 Forecast (2020)
ONIC PLC Balance Sheet $ 7,137 $ 5,455 $ 6,732.32 $ 4,059.19 $ 2,310.11 $ 25,694 ases this $ 4,950 $ 39,959 $ 18,646 $ 1,650 $ 1,089 $ 2,058 $ 2,112.10 $ 2,376.11 $ 627.03 $ 3,168.15 $ 10,341.39 $ 22,428 $ 1,651 $ 3,069
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
$ 1,386 $ 825 $ 39,700 $ - $ 26,532 $ (2,853) $ (19) $ 28,326 $ (2,711) $ 52,147 $ 140 $ 52,287 $ 91,987
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
5455 investments 825.04 33001.56 6732.318 accounts receivable less allowance 9900.468 4059.192 net inventory 2896 2508.119 2310.109 other current assets 11253.53 39959 goodwill 1914.09 1650.078 tax assets 148.5 1089.051 other assets 150 2112.1 AP 258 2376.112 accrued comp 6868.851 1651 accrued comp & retirement 5797.851 627.0296 accrued income tax current 869.6777 3168.15 other accrued exp 4928.173 3069.145 accrued income tax noncurrent 4909.173 1386.066 deferred tax 825.039 other liabilities 0 ordinary shares -2711 accumulated comprehensive loss
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
0.037111 1224.72 13200.86 10341.39 39700.65
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
E11-25 MEDTRONIC PLC Statement of Cash Flow $ millions For Year Ended Step 1 Net income (before non-controlling interest) Step 2A Add: Depreciation Step 2B Add: Amortization ASSETS Step 3 Change in Accounts receivable Step 3 Change in Inventories, net Step 3 Change in Other current assets Step 3 Change in Tax assets Step 3 Change in Other assets LIABILITIES Step 3 Change in Accounts payable Step 3 Change in Accrued compensation Step 3 Change in Accrued income taxes Step 3 Change in Other accrued expenses Step 3 Change in Accrued income taxes Step 3 Change in Deferred tax liabilities Step 3 Change in Other liabilities Net cash from operating activities Capital Expenditures Net cash from investing activities Dividends Change in current debt obligations Change in L-T Debt Net cash from financing activities
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Net change in cash Beginning cash Ending cash 2019 Net sales $30,557 Costs and expenses Cost of products sold 9,155 Research and development expense 2,330 Selling, general, and administrative expense 10,418 Amortization of intangible assets 1,764 Restructuring charges, net 198 Certain litigation charges, net 166 Other operating expense, net 258 Operating profit 6,268 Other nonoperating income, net -373 Interest expense 1,444 Income before income taxes 5,197 Income tax provision 547 Net income 4,650 Net income loss attributable to noncontrolling interests -19 Net income attributable to Medtronic $4,631
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
net sales $33,001.56 d $ 4,928.17 $ 950 $ 1,914 $ 7,792 6222 6,732 $ 510.32 3753 4,059 $ 306.19 2144 2,310 $ 166.11 1519 1,650 $ 131.08 1014 1,089 $ 75.05 1953 2,112 $ 159.10 2189 2,376 $ 187.11 567 627 $ 60.03 2925 3,168 $ 243.15 2838 3,069 $ 231.15 1278 1,386 $ 108.07 757 825 $ 68.04 $ 7,660 0.037111 $ 1,224.72 $ (1,224.72) $ 2,853 use 2058 838 $ (1,220.00) source 24486 22,428 $ (2,058) use $ (3,691)
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
$ 2,744 $ 4,393 $ 7,137 2020 $33,001.56 $9,900.47 $2,508.12 $11,253.53 $1,914.09 148.5 150 258 $6,868.85 -373 1,444 $5,797.85 $869.68 $4,928.17 -19 $4,909
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help

Related Documents

Extra practice for chapter 5.docx
ACC 411 Milestone One.docx
ACC430 Milestone One MS.docx
planning and evaluating tests of control 231127.docx
ACCT 101-2020 Final.pdf
CVP supplemental h.w. #2.docx
Real Estate Closings Research Paper Purdy.docx
Final Paper .docx
milestone 3.docx
Week1_Gregory.docx
Week2_Gregory.docx
Week7_Gregory.docx
Related Questions
How do I calculate: Sales per day Accounts receivable new policy Freed-up cash
Calculate the EFN, external financing needed.
How would you prepare a statement of Cash Flows using the following information? Maple Group Ltd Comparative Balance Sheet December 31, 2020 and 2019   2020 2019  Increase/Decrease       Assets       Cash and cash Equivalent  $   51,500.00  $   45,400.00  $              6,100.00 Accounts Receivable  $   51,100.00  $   61,400.00  $            10,000.00 Inventories  $   61,400.00  $   51,900.00  $              9,500.00 Fixed Assets, net  $ 160,000.00  $ 110,000.00  $            50,000.00 Total Assets  $ 324,000.00  $ 268,700.00  $            55,600.00      Liabilities       Accounts Payable  $   35,450.00  $   27,800.00  $              7,650.00 Accrued liabilities  $   31,000.00  $   37,550.00 -$             6,550.00 Long-term notes payable  $   60,000.00  $   78,540.00 -$           18,540.00      Stockholders Equity:       Common Stock  $ 143,050.00  $ 105,110.00  $            37,940.00 Retained Earnings  $   54,800.00  $   19,700.00  $…
Hi pls answer a - c for 2019: a) What is the cash from Operating Activities for 2019?b) What is the cash from Investing Activities for 2019?c) What is the cash from Financing Activities for 2019?
Perform a horizontal analysis for the balance sheet entry "Cash" given below. That is, find the amount of increase or decrease (in $) and the associated percent (rounded to the nearest tenth).
Find operating cash flow to current liabilites for years 2017, 2018, and 2019.
How do you calculate a statement of Cash Flows using the below details: Maple Group Ltd Comparative Balance Sheet December 31, 2020 and 2019   2020 2019  Increase/Decrease       Assets       Cash and cash Equivalent  $   51,500.00  $      45,400.00  $              6,100.00 Accounts Receivable  $   51,100.00  $      61,400.00  $            10,000.00 Inventories  $   61,400.00  $      51,900.00  $              9,500.00 Fixed Assets, net  $ 160,000.00  $    110,000.00  $            50,000.00 Total Assets  $ 324,000.00  $    268,700.00  $            55,600.00      Liabilities       Accounts Payable  $   35,450.00  $      27,800.00  $              7,650.00 Accrued liabilities  $   31,000.00  $      37,550.00 -$             6,550.00 Long-term notes payable  $   60,000.00  $      78,540.00 -$           18,540.00      Stockholders Equity:       Common Stock  $ 143,050.00  $    105,110.00  $            37,940.00 Retained Earnings  $   54,800.00  $…
Problem 17-4 (IAA) Pro Forest Company provided the following information for the preparation of a statement of cash flows for the current year Fear state 2020 2019 Cas! Accc Inve Inve Lan Pro Acc Goc 300,000 Cash and cash equivalents Trading securities Accounts receivable, net of allowance Inventory Property, plant and equipment (net) Goodwill Discount on bonds payable 603,000 300,000 200,000 600,000 900,000 2,000,000 200,000 72,000 520,000 840,000 2,100,000 200,000 100,000 4,675,000 4,260,000 Acc No Во Sh. Sh Re Tr 490,000 310,000 800,000 800,000 210,000 1,000,000 Accounts payable Accrued expenses Bonds payable Preference share capital, P100 par, each share convertible into two ordinary shares Ordinary share capital, P20 par Share premium Retained earnings 500,000 700,000 400,000 650,000 400,000 820,000 500,000 1,355,000 A 4,675,000 4,260,000 1. Additional information 2. 1. Net income for the current year was P1,705,000. 2. Cash dividend paid during the year totaled P1,000,000. 3. The…
Prepare a ratio analysis ( Conclusion only) on each statement
s
Find operating cash flow to current liabilites for years 2017, 2018, and 2019.
Prepare an indirect Cash flow statement
SEE MORE QUESTIONS
Recommended textbooks for you
Text book image
Cornerstones of Financial Accounting
Accounting
ISBN:9781337690881
Author:Jay Rich, Jeff Jones
Publisher:Cengage Learning
Text book image
Financial Accounting: The Impact on Decision Make...
Accounting
ISBN:9781305654174
Author:Gary A. Porter, Curtis L. Norton
Publisher:Cengage Learning
Text book image
Financial Accounting Intro Concepts Meth/Uses
Finance
ISBN:9781285595047
Author:Weil
Publisher:Cengage
SEE MORE TEXTBOOKS
Related Questions
Recommended textbooks for you
Text book image
Cornerstones of Financial Accounting
Accounting
ISBN:9781337690881
Author:Jay Rich, Jeff Jones
Publisher:Cengage Learning
Text book image
Financial Accounting: The Impact on Decision Make...
Accounting
ISBN:9781305654174
Author:Gary A. Porter, Curtis L. Norton
Publisher:Cengage Learning
Text book image
Financial Accounting Intro Concepts Meth/Uses
Finance
ISBN:9781285595047
Author:Weil
Publisher:Cengage
SEE MORE TEXTBOOKS