?
A?
VI
TU
_S
ED
OV
PR
AP
,AP
TABLE OF CONTENTS
Solutions Manual: Financial Analysis with Microsoft® Excel®, 8th Edition
PR By Timothy R. Mayes and Todd M. Shank
Chapter 1 Introduction to Excel 2016
Chapter 2
OV The Basic Financial Statements
Chapter 3 Financial Statement Analysis Tools
Chapter 4 The Cash Budget
Chapter 5
ED
Financial Forecasting
Chapter 6 Forecasting Sales with Time Series Methods
_S
Chapter 7 Break-Even and Leverage Analysis
Chapter 8 The Time Value of Money
Chapter 9 Common Stock Valuation
TU
Chapter 10 Bond Valuation
Chapter 11 The Cost of Capital VI
Chapter 12 Capital Budgeting
Chapter 13 Risk and Capital Budgeting A?
Chapter 14 Portfolio Statistics and Diversification
Chapter 15 Writing User-Defined Functions with VBA
Chapter 16 Analyzing Datasets with Tables and Pivot Tables
?
1
,AP CHAPTER 1: SPREADSHEET BASICS
Instructor’s Manual Problem Set
PR
Solutions can be found in the accompanying Excel files. Note that if you wish to see all of the formulas at
once, you may use the CTRL+` (Control plus grave accent) shortcut key to toggle them on or off.
1. The following table contains closing monthly stock prices for Oracle Corporation (ORCL),
Microsoft Corporation (MSFT), and NVidia (NVDA) for the first half of 2017.
OV
Ticker
ORCL
6/30/2017
50.14
5/31/2017
45.39
4/30/2017
44.96
3/31/2017
44.61
2/28/2017
42.59
1/31/2017
40.11
MSFT 68.93 69.84 68.46 65.86 63.98 64.65
NVDA 144.56 144.35 104.3 108.93 101.48 109.18
a) Enter the data, as shown, into a worksheet and format the table as shown.
b) Create a formula to calculate the monthly rate of return during the first semester of 2017 and for each
ED
company. Format the results as percentages with two decimal places.
c) Calculate the total return for the entire holding period, the compound average monthly rate of return,
the average monthly rate of return using the AVERAGE function, and the average monthly rate of
return using the GEOMEAN function.
d) Create a line chart showing the stock prices from January to June 2017 for these companies. Make
_S
sure to title the chart and label the axes. Also present the data from January to June and use Times
New Roman for the title, labels, and numbers. Select a different dash type for each line representing
each company.
TU
2. The following table contains financial information for Intel Corp.
Intel Corporation
Income Statement ($ millions)
Dec-16 Dec-15 Dec-14 Dec-13 Dec-12
Sales 59387 55355 55870 52708 53341
Cost of Goods Sold (COGS) incl. D&A 23425 20651 20522 21418 20507
Gross Income
SG&A Expense
?
21149
?
19835
?
19693
VI ?
18729
?
18117
EBIT (Operating Income) 14813 14869 15655 12561 14717
Nonoperating Income - Net 533 -51 224 595 463
Interest Expense
Unusual Expense - Net
Pretax Income ?
733
1677
?
337
269
?
192
-114
?
A?
244
301
?
90
217
Income Taxes ? ? ? ? ?
Net Income 10316 11420 11704 9620 11005
a) Enter the data, as shown, into a worksheet and format the numbers with a comma separating the
thousands position and no decimal places.
?
b) Create the required formulas to calculate the missing variables, and format the results to match the
other numbers.
c) Calculate the average tax rate, the gross profit margin, and the net profit margin for 2012-2016.
Format the results as percentages with two decimal places.
d) Create a line chart showing the gross profit margin and the net profit margin for 2012-2016. Make
sure to title the chart and label the axes.
e) Create a copy of the income statement and replace each item with a formula that shows it as a
percentage of sales. You should only use one formula that can be copied and pasted to the rest of the
income statement.
1
, AP
2 Chapter 1: Spreadsheet Basics
IM Problem Set & Solutions
3. The following table contains financial information for Intel Corp.
PR Intel Corporation
Balance Sheet ($ millions)
Dec-16 Dec-15 Dec-14 Dec-13 Dec-12
Assets
Cash & Short-Term Investments 17099 25313 14054 20087 18162
Inventories
OV
Short-Term Receivables 5074
5553
5530
5167
4427
4273
3647
4172
4699
4734
Other Current Assets 7782 4346 4976 4178 3763
Total Current Assets ? ? ? ? ?
Net Fixed Assets 77819 62709 64226 60274 52993
Total Assets ED ? ? ? ? ?
Liabilities & Shareholders' Equity
ST Debt & Curr. Portion LT Debt 4634 2634 1604 281 312
Accounts Payable 2475 2063 2748 2969 3023
Income Tax Payable 329 272 443 542 711
Other Current Liabilities 12864 10698 11224 9776 8852
Total Current Liabilities ? ? ? ? ?
Long-Term Debt
Deferred Tax Liabilities
Other Liabilities
_S 20649
1730
3538
20036
2539
2841
12107
3775
3278
13165
4397
2972
13136
3412
3702
Total Liabilities ? ? ? ? ?
Preferred Stock (Carrying Value) 882 897 912 0 0
Common Equity
Total Shareholders' Equity
Total Liabilities & Shareholders' Equity ?
TU
66226
67108
61085
61982
?
55865
56777
?
58256
58256
?
51203
51203
?
a) Enter the data into a worksheet and format the table as shown. Format the cells as accounting
numbers with no decimal places.
b) Create a formula to calculate the missing values in the table denoted by a question mark using the
SUM function, and format the results to match the other numbers.
VI
c) Use a formula to calculate total debt as a percentage of total assets, and a similar formula showing
total shareholder’s equity as a percentage of total assets as of the end of 2016. A?
d) Create a pie chart showing the proportion of total debt and total equity that Intel used to finance its
assets at the end of 2016. Make sure to title the chart and add data labels.
e) Create a chart showing how debt and equity as a percentage of total assets have changed over time.
Be sure to title the chart, label the axes, and reverse the x-axis so that time flows from left to right.
f) Copy the balance sheet and express each item as a percentage of total assets.
?
g) Create a chart showing current assets, fixed assets, current liabilities, and long term liabilities for
2012–2016. Be sure to add a title and axis labels, and reverse the x-axis so that time flows from left to
right.
A?
VI
TU
_S
ED
OV
PR
AP
,AP
TABLE OF CONTENTS
Solutions Manual: Financial Analysis with Microsoft® Excel®, 8th Edition
PR By Timothy R. Mayes and Todd M. Shank
Chapter 1 Introduction to Excel 2016
Chapter 2
OV The Basic Financial Statements
Chapter 3 Financial Statement Analysis Tools
Chapter 4 The Cash Budget
Chapter 5
ED
Financial Forecasting
Chapter 6 Forecasting Sales with Time Series Methods
_S
Chapter 7 Break-Even and Leverage Analysis
Chapter 8 The Time Value of Money
Chapter 9 Common Stock Valuation
TU
Chapter 10 Bond Valuation
Chapter 11 The Cost of Capital VI
Chapter 12 Capital Budgeting
Chapter 13 Risk and Capital Budgeting A?
Chapter 14 Portfolio Statistics and Diversification
Chapter 15 Writing User-Defined Functions with VBA
Chapter 16 Analyzing Datasets with Tables and Pivot Tables
?
1
,AP CHAPTER 1: SPREADSHEET BASICS
Instructor’s Manual Problem Set
PR
Solutions can be found in the accompanying Excel files. Note that if you wish to see all of the formulas at
once, you may use the CTRL+` (Control plus grave accent) shortcut key to toggle them on or off.
1. The following table contains closing monthly stock prices for Oracle Corporation (ORCL),
Microsoft Corporation (MSFT), and NVidia (NVDA) for the first half of 2017.
OV
Ticker
ORCL
6/30/2017
50.14
5/31/2017
45.39
4/30/2017
44.96
3/31/2017
44.61
2/28/2017
42.59
1/31/2017
40.11
MSFT 68.93 69.84 68.46 65.86 63.98 64.65
NVDA 144.56 144.35 104.3 108.93 101.48 109.18
a) Enter the data, as shown, into a worksheet and format the table as shown.
b) Create a formula to calculate the monthly rate of return during the first semester of 2017 and for each
ED
company. Format the results as percentages with two decimal places.
c) Calculate the total return for the entire holding period, the compound average monthly rate of return,
the average monthly rate of return using the AVERAGE function, and the average monthly rate of
return using the GEOMEAN function.
d) Create a line chart showing the stock prices from January to June 2017 for these companies. Make
_S
sure to title the chart and label the axes. Also present the data from January to June and use Times
New Roman for the title, labels, and numbers. Select a different dash type for each line representing
each company.
TU
2. The following table contains financial information for Intel Corp.
Intel Corporation
Income Statement ($ millions)
Dec-16 Dec-15 Dec-14 Dec-13 Dec-12
Sales 59387 55355 55870 52708 53341
Cost of Goods Sold (COGS) incl. D&A 23425 20651 20522 21418 20507
Gross Income
SG&A Expense
?
21149
?
19835
?
19693
VI ?
18729
?
18117
EBIT (Operating Income) 14813 14869 15655 12561 14717
Nonoperating Income - Net 533 -51 224 595 463
Interest Expense
Unusual Expense - Net
Pretax Income ?
733
1677
?
337
269
?
192
-114
?
A?
244
301
?
90
217
Income Taxes ? ? ? ? ?
Net Income 10316 11420 11704 9620 11005
a) Enter the data, as shown, into a worksheet and format the numbers with a comma separating the
thousands position and no decimal places.
?
b) Create the required formulas to calculate the missing variables, and format the results to match the
other numbers.
c) Calculate the average tax rate, the gross profit margin, and the net profit margin for 2012-2016.
Format the results as percentages with two decimal places.
d) Create a line chart showing the gross profit margin and the net profit margin for 2012-2016. Make
sure to title the chart and label the axes.
e) Create a copy of the income statement and replace each item with a formula that shows it as a
percentage of sales. You should only use one formula that can be copied and pasted to the rest of the
income statement.
1
, AP
2 Chapter 1: Spreadsheet Basics
IM Problem Set & Solutions
3. The following table contains financial information for Intel Corp.
PR Intel Corporation
Balance Sheet ($ millions)
Dec-16 Dec-15 Dec-14 Dec-13 Dec-12
Assets
Cash & Short-Term Investments 17099 25313 14054 20087 18162
Inventories
OV
Short-Term Receivables 5074
5553
5530
5167
4427
4273
3647
4172
4699
4734
Other Current Assets 7782 4346 4976 4178 3763
Total Current Assets ? ? ? ? ?
Net Fixed Assets 77819 62709 64226 60274 52993
Total Assets ED ? ? ? ? ?
Liabilities & Shareholders' Equity
ST Debt & Curr. Portion LT Debt 4634 2634 1604 281 312
Accounts Payable 2475 2063 2748 2969 3023
Income Tax Payable 329 272 443 542 711
Other Current Liabilities 12864 10698 11224 9776 8852
Total Current Liabilities ? ? ? ? ?
Long-Term Debt
Deferred Tax Liabilities
Other Liabilities
_S 20649
1730
3538
20036
2539
2841
12107
3775
3278
13165
4397
2972
13136
3412
3702
Total Liabilities ? ? ? ? ?
Preferred Stock (Carrying Value) 882 897 912 0 0
Common Equity
Total Shareholders' Equity
Total Liabilities & Shareholders' Equity ?
TU
66226
67108
61085
61982
?
55865
56777
?
58256
58256
?
51203
51203
?
a) Enter the data into a worksheet and format the table as shown. Format the cells as accounting
numbers with no decimal places.
b) Create a formula to calculate the missing values in the table denoted by a question mark using the
SUM function, and format the results to match the other numbers.
VI
c) Use a formula to calculate total debt as a percentage of total assets, and a similar formula showing
total shareholder’s equity as a percentage of total assets as of the end of 2016. A?
d) Create a pie chart showing the proportion of total debt and total equity that Intel used to finance its
assets at the end of 2016. Make sure to title the chart and add data labels.
e) Create a chart showing how debt and equity as a percentage of total assets have changed over time.
Be sure to title the chart, label the axes, and reverse the x-axis so that time flows from left to right.
f) Copy the balance sheet and express each item as a percentage of total assets.
?
g) Create a chart showing current assets, fixed assets, current liabilities, and long term liabilities for
2012–2016. Be sure to add a title and axis labels, and reverse the x-axis so that time flows from left to
right.
