Need Assistance with Assignment 7

 Needing assistance with an assignment for an income statement vertical and horizontal analysis in excel. I’m attaching the assignment instructions, grading rubric and an excel spreadsheet and income statement for IBM for this assignment. 

Any concerns or pricing concerns please contact me prior to completing the assignment. 

Thanks

Sheet1

Assessment #5 Financial Data Income Statement All numbers in thousands Revenue

2018

2017

2016 2015 2014 Total Revenue 806,338 766,083 529,515 450,557 486,280 Cost of Revenue 206,052 187,289 131,070 102,571 121,020 Gross Profit 600,286 578,794 398,445 347,986 365,260 Operating Expenses Research Development 116,445 99,205 78,269 69,791 59,880 Selling General and Administrative 262,699 220,728 166,110 151,651 383,240 Total Operating Expenses 379,144 319,933 244,379 221,442 443,120 Operating Income or Loss 221,142 258,861 154,066 126,544 (77,860) Income from Continuing Operations Total Other Income/Expenses Net 13,432 7,603 8,011 418 504 Net Income 207,710 251,258 146,055 126,126 (78,364) Source: Congressional Budget Office Assumption

s specific to Company Category

Assumption
Total Revenue

Management projects revenue to continue same trend as previous 5 years Cost of Revenue

Management is implementing cost savings programs that should reduce the cost of revenue to approximately 2016 levels as a percentage Research Development

Management believes that new products are the life blood of the company and will continue with it current trend of R& D expenses Selling General and Administrative

Management projects that all other expense increases will be in line with Inflation

Summary Table 1.

CBO’s Projections of Key Economic Indicators for Calendar Years 2018 to

2028

Annual Average

Actual,

2017

2018

2019

2020

2021–

2022

2023–

2028

Percentage Change From Fourth Quarter to Fourth Quarter

Gross Domestic Product

Reala

2.6

3.3

2.4

1.8

1.5

1.7

Nominal 4.5 5.2 4.5 3.9

3.7

3.9

Inflation

PCE price index

1.7

1.8

2.0

2.1

2.1

2.0

Core PCE price indexb 1.5

1.9

2.1 2.2 2.1 2.0

Annual Average
Unemployment Rate (Percent) 4.4 3.8 3.3

3.6

4.4 4.8
Payroll Employment (Monthly change, in thousands)c 181 211 182 62 25 57

Interest Rates (Percent)

Three-month Treasury bills

0.9

1.9

2.9

3.6

3.7

2.8

Ten-year Treasury notes 2.3 3.0 3.7 4.1 4.1 3.7

Summary Table 1.

CBO’s Projections of Key Economic Indicators for Calendar Years 2018 to 2028

Annual Average
Actual,
2017

2018

2019

2020
2021–
2022
2023–
2028

Percentage Change From Fourth Quarter to Fourth Quarter
Gross Domestic Product
Reala

2.6

3.3

2.4

1.8

1.5

1.7

Nominal 4.5 5.2 4.5 3.9 3.7 3.9

Inflation
PCE price index

1.7

1.8

2.0

2.1

2.1

2.0

Core PCE price indexb 1.5 1.9 2.1 2.2 2.1 2.0

Annual Average

Unemployment Rate (Percent) 4.4 3.8 3.3 3.6 4.4 4.8

Payroll Employment (Monthly change, in thousands)c 181 211 182 62 25 57

Interest Rates (Percent)
Three-month Treasury bills

0.9

1.9

2.9

3.6

3.7

2.8

Ten-year Treasury notes 2.3 3.0 3.7 4.1 4.1 3.7

Sheet1

Thousands Thousands Thousands Thousands Thousands Thousands

,000

other

–

350,000 435,000

67,000 55,000 87,000

50,000

Selling, general & administrative expense

1,466,000

742,000

599,000

72,000 90,000 74,000

20,000

26,000

55,000

– – – – – –

63,000 116,000

260,000

327,000

– U.S. operations

Income from continuing operations before income taxes

77,000

553,000

55,000

134,000

28,000

116,000

-321,000

244,000

4,000

105,000

– –

5,000

– –

6

– –

0

– –

6.17

7

13.6

– –

0.01 0 -0.01 -0.18

– –

6.14

11.9

– – – – –

– – –

																																																																																																																																																																																																																																																																																																																																																																		As Reported Annual Income Statement
Report Date	12/31/18	12/31/17	12/31/16	12/31/15	12/31/14	12/31/13	12/31/12
Scale							Thousands
Services revenue	51,	3	5		0	50,709,000	51,268,000	49,911,000	55,673,000	57,6			5	5,000	59,453,000
Sales revenue	26,641,000	26,715,000	26,942,000	29,9	67,000	35,0	63,000	40,049,000	43,01	4,000
Financing revenue	1,	599,000	1,715,000	1,710,000	1,864,000	2,057,000	2,047,000	2,040,000
Total revenue	79,591,000	79,139,000	79,919,000	81,741,000	92,793,000	99,751,000	104,507,000
Cost of services	34,059,000	34,447,000	34,021,000	33,1	26,000	36,034,000	37,564,000	39,166,000
Cost of sales	7,464,000	7,256,000	6,559,000	6,9	20,000	9,312,000	12,5	72,000	13,956,000
Cost of financing	1,132,000	1,210,000	1,044,000	1,011,000	1,040,000	1,110,000	1,0	87,000
Total cost	42,655,000	42,913,000	41,625,000	41,057,000	46,386,000	51,246,000	54,209,000
Gross profit	36,936,000	36,227,000	38,294,000	40,684,000	46,407,000	48,505,000	50,298,000
	Selling, general & administrative expense																											–	16,438,000	16,568,000	16,971,000	16,643,000	18,532,000	19,187,000	19,589,000
Advertising & promotional expense		1,466,000	1,445,000	1,	327,000	1,2	90,000	1,307,000	1,294,000	1,339,000
Workforce rebalancing charges	598,000	199,000	1,038,000	587,000	1,472,000	1,064,000	803,000
Retirement-related costs	959,000		742,000	1,052,000	811,000	995,000	945,000
Amortization of acquired intangible assets		435,000	496,000	503,000	304,000	3	74,000	370,000	3	28,000
Stock-based compensation	361,000	384,000	401,000	322,000	498,000
Bad debt expense	231,000	334,000	156,000
19,366,000	20,107,000	21,069,000	20,430,000	23,180,000	23,502,000	23,	553,000
Research, development & engineering expense	5,379,000	5,787,000	5,751,000	5,247,000	5,437,000	6,226,000	6,302,000
Intellectual property & custom development income	1,026,000	1,631,000	682,000	822,000	1,074,000
Foreign currency transaction gains (losses)	427,000	-405,000			116,000	-414,000		260,000	240,000
Gains (losses) on derivative instruments	-434,000	341,000	-260,000	853,000	-654,000	-166,000	-72,000
Interest income	264,000	144,000	108,000	109,000
Net gains (losses) from securities & investment assets	101,000	-23,000	-47,000	29,000
Retirement-related income (costs)	-1,572,000
Other income	-85,000	1,878,000	131,000	511,000
Other income & expense	1,152	-216,000	145,000	724,000	1,938,000	843,000
Interest expense	723,000	615,000	630,000	468,000	484,000	402,000	459,000
Total expense & other income	-25,594,000	24,827,000	25,964,000	24,740,000	26,421,000	28,981,000	28,396,000
	Income from continuing operations before income taxes	627,000	560,000	3,650,000	5,915,000	7,509,000	6,857,000	9,668,000
Income from continuing operations before income taxes – non-U.S. operations	10,715,000	10,840,000	8,680,000	10,030,000	12,4	77,000	12,667,000	12,234,000
11,342,000	11,400,000	12,330,000	15,945,000	19,986,000	19,524,000	21,902,000
Provision for (benefit from) U.S. federal income taxes – current	-342,000	2,388,000	186,000		-321,000	1,	134,000	1,406,000	1,361,000
Provision for (benefit from) U.S. federal income taxes – deferred	1,377,000	-746,000		105,000	-652,000	403,000
Total provision for (benefit from) U.S. federal income taxes	1,035,000	2,465,000	-560,000	232,000	1,239,000	754,000	1,764,000
Provision for (benefit from) U.S. state & local income taxes – current	127,000		244,000	128,000	541,000	178,000
Provision for (benefit from) U.S. state & local income taxes – deferred	-292,000	-44,000	-105,000	289,000
Total provision for (benefit from) U.S. state & local income taxes	-165,000	83,000	200,000	436,000	-143,000	423,000
Provision for (benefit from) nopn-U.S. income taxes – current	2,135,000	3,891,000	988,000	2,101,000	2,825,000	3,067,000	3,006,000
Provision for (benefit from) nopn-U.S. income taxes – deferred	-386,000	-797,000	-179,000	-266,000	-637,000
Total provision for (benefit from) nopn-U.S. income taxes	1,749,000	3,094,000	809,000	2,105,000	2,559,000	2,430,000	3,111,000
Provision for (benefit from) income taxes	2,619,000	5,642,000	449,000	2,581,000	4,234,000	3,041,000	5,298,000
Income (loss) from continuing operations	8,723,000	5,758,000	11,881,000	13,364,000	15,751,000
Income (loss) from discontinued operations, net of tax	-5,000	-9,000	-174,000	-3,729,000
Net income (loss)	8,728,000	5,753,000	11,872,000	13,190,000	12,022,000	16,483,000	16,604,000
Weighted average shares outstanding – basic	912,048.07	932,828.30	955,422.53	978,744.52	1,004,272.58	1,103,042.16	1,142,508.52
Weighted average shares outstanding – diluted	916,315.71	937,385.63	958,714.10	982,700.27	1,010,000.48	1,094,486.60	1,155,449.32
Year end shares outstanding	892,479.41	922,179.23	945,867.40	965,728.73	990,523.76	1,054,390.94	1,117,367.68
Earnings (loss) per share from continuing operations – basic	9.56		6.17	12.44		13.6	15.68
Earnings (loss) per share from discontinued operations – basic		0.01		-0.01		-0.18	-3.71
Net earnings (loss) per share – basic	9.57	12.43	13.48		11.9	15.06	14.53
Earnings (loss) per share from continuing operations – diluted	9.51		6.14	12.39	15.59
Earnings (loss) per share from discontinued operations – diluted	-3.69
Net earnings (loss) per share – diluted	9.52	12.38	13.42	14.94	14.37
Dividends per share of common stock	6.21	3.3
Total number of employees	350,600	366,600	380,300	377,757	379,592	431,212	434,246
Number of common stockholders	395,480	408,697	425,272	444,582	458,306	473,872	488,800
Foreign currency translation adjustments	-730,000	152,000	-20,000	-1,379,000

Deliverable 7 – Analyzing and summarizing a Company’s Operations

Competencies

· Analyze the role of accounting in business operations.

· Apply the principles of accrual accounting and accounting cycles.

· Appraise the financial health and performance of a company.

· Analyze cost behaviors and production costing methods.

· Employ accounting data for business analysis and prediction.

· Evaluate accounting-related legal and ethical business implications.

Scenario

The Vice President of Finance called you into a meeting to discuss the overall vision and future of your company. He states that he has always admired International Business Machines (IBM) since he studied the company in college. The VP has not followed the company as of late and would like you to prepare an update as to how IBM is doing over last three years and the top line outlook for next year. The VP would like you to use similar techniques that you have used in writing your previous reports.

Instructions

Using the financial statements of International Business Machines Corporation:

1. Prepare an income statement vertical and horizontal analysis of International Business Machines Corporation using three years of data. Use Mergent Online to download income statement to Excel.

Be sure to search for International Business Machines Corp, and choose the United States entity. FAQ for accessing and downloading income statements

2. Using the line item descriptions from the income statement, create a new tab and identify which expenses (costs) are likely fixed, variable, product, and period costs.

3. Using Mergent Online, create a ratio analysis report showing International Business Machines Corporation and its largest 4 competitors based on total revenue
Download the following ratios and peer average into Excel. Format and arrange the data in a professional manner:

a. Current Ratio

b. Gross Margin

c. Inventory % of TA

d. Inventory Turnover

e. Debt/Equity Ratio

f. Net Current Assets % TA

g. Net PPE % TA

h. Net Profit Margin %

i. Operating Margin %

j. R + D % TR

k. ROA %

l. ROE %

m. ROI %

n. Selling and General Admin % TR

o. Total Asset Turnover

4. Using the historical income statement data, prepare a sales (revenue) forecast for the upcoming year including your assumptions, calculations, and rationale.

5. Using the 

SEC.gov

 website, find articles on of International Business Machines Corporation and comment in a Word document on any recent accounting standards or ethical considerations that affect the company.