这个作业是用Excel统计公司的营收，支出等数据的商科编程代写
CS170 – Computer Applications for Business Spring 2020 • Assignment 9
Evaluation: 20 points
Submit to Canvas: Assignment9.xlsx file
Learning Objective:
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.
Directions:
• 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 worksheet)
• Submit your assignment9.xlsx file to Canvas using the Canvas-> Assignments link.
Requirements:
1. Apple spreadsheet
Formatting:
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:
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 Income/Expenses.)
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 integer.
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.
Charts
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
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.
Formatting
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.
Formulas
2. Complete the missing cells (yellow cells) by creating appropriate formulas with the information provided.
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.
3. Formulas spreadsheet
Enter a formula in the cells indicated – yellow background – using the formulas listed below.
a. Amortized Loan Payment: ? = ??(1+?)?(1+?)?−1
b. Area of a Trapezoid: A = 12h(b1 +b2)
c. Volume of a Sphere: ? =43??3
Have your formula use relative reference on rand absolute reference on
References:
1. Chapter 13 of the Fluency7 textbook
2. Recitations and Lectures