CS170 – Computer Applications for Business
Spring 2020 • Assignment 9
Due Date: Before 11:55 p.m. on Friday, April 10th, 2020
Accept Until: Before 11:55 p.m. on Friday, April 17th, 2020
Evaluation: 20 points
Submit to Canvas: Assignment9.xlsx file
This assignment is designed to introduce:
1. The basics of worksheets using MS Excel.
2. Creating simple and complex formulas,
3. Formatting for appeal and emphasis.
4. Creating interpretive charts.
To get credit for this assignment:
1. Submit the assignment9.xlsx file to Canvas on time.
• Follow the requirements listed on the next page.
• Use formulas to complete the Apple, Catalog, and Formulas worksheets of this
assignment using the information listed on the next pages.
• Enter your name on cell H1 of each of three worksheets. (J1 on the Catalog
• Submit your assignment9.xlsx file to Canvas using the Canvas-> Assignments link.
1. Apple spreadsheet
a. Bold the text “Apple Inc.”,”Consolidated Income Statement”,”Gross Profit”,”Total
Operating Expenses”,”Operating Income or Loss”,”Net Income from Continuing
Ops”,”Sep 2017”,”Sep 2018”,”Sep 2019”
b. Set background color to grey on “Sep 2017”,”Sep 2018”,”Sep 2019”
Formulas: Complete the missing information (yellow cells) with accounting formulas:
Page 2 of 4
c. Gross Profit: Net Sales minus Cost of Sales.
d. Total Operating Expenses: The sum of Research and Development and Selling,
general and administrative.
e. Operating Income or Loss: Gross Profit minus Total Operating Expenses.
f. Earnings Before Interest and Taxes: Operating Income or Loss plus Total Other
Income/Expenses Net. (Note: The / symbol is just part of the expression
g. Income Before Tax: Earnings Before Interest and Taxes minus, Interest Expense
plus Other expenses.
h. Add 3 columns to the right which will forecast for 2020, 2021, and 2022. Bold the
titles but set the background color to green. In cell F5, put “Projection” and bold,
center. Background color for E5:G5 set to green.
i. In cell C5, put “Historical” and bold, center. Set background color B5:D5 to grey.
j. Cell A22:G22 set top border to be a single solid line
k. For each forecast year set the Net Sales to increase by 10% year on year, Cost of
Sales increase by 5% year on year, each of operating expenses increases 2% year on
year. Round all numbers to integer.
l. 2020-2022 Total Other Income/Expenses Net, Interest Expense, Other expenses,
Income Tax Expense all project 2% year on year increase. Round all numbers to
m. For above formulas copy forward into the 2020-2022 projections. For example,
extend Gross Profit through to 2022 projection. Round all numbers to integer.
n. 3D Clustered Column Chart: Include Gross Profit and Total Operating Expenses for
the three historical years. Change the chart title to Gross Profit & Operating
Expenses. Move the chart to its own spreadsheet, name it Column Chart. Apply
Chart Style 3 and Layout 1. Set the series labels to be Gross Profit and Operating
Expenses. Label the years accordingly 2017,2018, and 2019
o. 3D Pie Chart: Include the Research and Development and Selling, General and
Administrative data for 2019 (D11:D12). Relocate/resize the chart so that it fits in
the area I9:M19. Apply style 5. Add the chart title Operating Expenses 2019, and
Data Labels center as a Percentage (not value).
p. Line chart: Include Interest Expense and Income Tax Expense for the three historical
years. Change the chart title to Interest & Income Tax Expense. Move the chart to its
own worksheet naming it Line Chart. Apply Chart Style 5 and Layout 2. Label series
for Interest Expense and Income Tax. Label years accordingly 2017,2018, and 2019
2. American Eagle Catalog spreadsheet
Page 3 of 4
As the manager of the local American Eagle Outfitters clothing store, you must generate a
monthly sales report for certain Men’s & Women’s clothing.
1. Apply the following cell styles:
a. Merge & Center cells A1 to I1, and A2 to I2.
b. Accounting style to Columns E, F, G, and I.
c. Heading 3 to the headings on row 5.
d. 40% Accent 3 style to the Merged cells A1 & A2.
2. Complete the missing cells (yellow cells) by creating appropriate formulas with the
For both the Men’s wear and Women’s wear:
a. Use formulas in column G to calculate the Markup for each item by subtracting
the Unit Cost from the Selling Price.
b. Then calculate the Net Profit by multiplying the Markup and the Units Sold.
Place Subtotals in G16 & I16 for the Men’s and G29 & I29 for the Women’s.
c. In G31 enter a formula that adds the two Markup Subtotals. Enter a formula in
I31 that will add the two Net Profit Subtotals.
Page 4 of 4
3. Formulas spreadsheet
Enter a formula in the cells indicated – yellow background – using the formulas listed below.
a. Amortized Loan Payment: 𝐴 = 𝑃
b. Area of a Trapezoid: A = 1
c. Volume of a Sphere: 𝑉 =
Have your formula use relative reference on r
and absolute reference on
1. Chapter 13 of the Fluency7 textbook
2. Recitations and Lectures
EasyDue™ 支持PayPal, AliPay, WechatPay, Taobao等各种付款方式!
E-mail: firstname.lastname@example.org 微信:easydue