Integrated Computer Application - Income Report

Objective:

To create a comprehensive report of Nike’s quarterly income from sales, showcasing key financial metrics. This report will include starting balance, sales, costs, net income, and ending balance for each quarter, as well as statistical information such as the highest, lowest, and average figures. This assignment will help you become proficient in using Microsoft Excel to enter data, format cells, and use statistical formulas.

Instructions:

1. Set Up the Worksheet:

   • Open Microsoft Excel and create a new workbook.
   • Save the workbook as "Nike_Quarterly_Income_Report_LastName_FirstName.xlsx".

2. Format:

   • Adjust: Title style, heading style, total style, font size, merge & center, cell color, font color, column width/row height, formulas: MIN, MAX, AVG 

3. Enter Data:

   • Change the worksheet name from Sheet1 to Income from Sales.

   • Enter the worksheet title, Nike, in cell A1 and the worksheet subtitle, Sales Income Report, in A2. 

   • Apply the title style to cells A1 and A2. Change the font size in A1 to 24 point and A2 to 14 point.

   • Merge and center the worksheet title and subtitle across columns A through F. 

   • Change the background color of cells A1 and A2 to the standard color blue and the font color white.

   • Using Figure 1, enter the column headings in row 3, the row labels in the range A4:A15.

   • Wrap and center the headings. If necessary, enter a line break to wrap the text.

   • Set the width of column A to 20 and the width of 14 for columns B through F.

   • Use a row height of 30 for row 3 and row 12.

   • Beginning in row 4, enter the starting balance and the quarterly data for the Sales and Costs columns displayed in Figure 1.

   • Format the range B5:F11 using the Comma Style.

   • Apply the Accounting Style to the ranges B4:F4 and B12:F15.

   • Format the column headings with the heading 3 style and the total row with the total style.

   • Enter a formula to calculate the values for the net income column and the ending balance column.

     Net Income = Sales – Costs

     Ending Balance = Starting Balance + Net Income

   • The starting balance is the same value as the ending balance from the previous quarter. Don’t enter the value, use a formula to display the starting balance based upon the ending balance.

   • Enter totals in the total row using the SUM function in cells C12, D12, and E12.

   • Display statistical information that summarizes rows 4 through 11.

      

   • Use the MAX function in row 13, MIN in row 14, and AVERAGE in row 15.

   • For every quarter in the report, use conditional formatting to show when the net income is greater than the average for the net income column.

      

   • Enter conditional formatting in each cell of net income that changes the font color to red if the cell’s value is greater than the average that is calculated in the statistical average for net income. Reference the cell that contains the average value of the average, do not enter the number.

   • Save and close your workbook.

      

   • Submit your Excel file to the assignment in the Canvas assignment space.

By following these steps, you'll create a clear and professional quarterly income report that effectively communicates Nike's financial performance and provides valuable insights through statistical analysis.