Assignment is subject to corrections and changes! If printing this assignment, please be sure to check the online version for changes and to access any links

POINTS: 40 points

SUMMARY: Modify an income & expenses spreadsheet for a company. This assignment stresses the creation of formulas and charts.

SKILLS NEEDED:

  • Critical & Logical Thinking!
  • Use formulas: Create formulas by referring to cells. Utilize sum function.
  • Work with worksheets: Insert rows/columns. Copy & delete worksheets. Refer to cells on other worksheets. Insert images.
  • Create Charts: Create a column chart & Add tiles, labels. Create a pie chart selecting disjointed ranges and add labeling and percentages.
  • Formatting: Change: font size, bold, italic, text color, number formats, row height, column widths.
  • Basic Math Skills: Working with percents. Recommended prep for this class is college algebra. If you have forgotten how to work with percents, please review See Khan Academy's: Taking Percent video or see my quick review of percents


Helpful hint: Have Excel and this page both viewable on the screen. For Windows, drag one window to the middle left of the screen and the other windwo to the middle right of the screen.. For Mac, size the windows and position them so that you can see both on the screen. One can also view this page on a tablet or smart phone and work on Excel on a computer.

All work must reflect INDIVIDUAL effort. If you need help, ask the instructor. Copying formulas from fellow students will be considered academic dishonesty.

NOTE: The learning exercises must be completed and submitted BEFORE this assignment. If software assignment is turned in without the learning exercises being completed, the learning excises will be give a score of 0. Even if you think you know how to do some tasks, you might not know the most efficient method and you are being graded on using the most efficient method. Do the learning exercises and learn!

Preliminary: From your ICS101Assignments - Spreadsheet folder open the file, IncomeExpenses-yourname

  1. Concept: Do the lessons BEFORE doing the assignment. Somewhere in the lessons are codes.
    1. (1 pt) In cell M1 enter the code for spreadsheet lesson - Introduction to Spreadsheets
    2. (1 pt) In cell M2 enter the code for spreadsheet lesson - Building Formulas & Functions
    3. (1 pt) In cell M3 enter the code for spreadsheet lesson - Spreadsheet Charts
  2. Concept: (3 pts) Editing and format a spreadsheet
    1. Format the range B4:E19 as $
    2. Make editing and formatting changes so that your assignment looks like the spreadsheet pictured below. Hints: Pay attention to formatting (bold, italic), inserting rows. Change in font size does not need to be exact. (Your numbers may be slightly different. - Do not change the numbers).

  3. Concept: Create formulas to accomplish the calculations as specified below.
    No points will be awarded for formulas that use functions inappropriately, contain numbers where there should be cell references, or do not use the most efficient method. In Column B next to each label create formulas to accomplish what is described below. Use the most efficient means to create the formula which may include using functions, but not every formula requires a function.
    1. Income
      • (2 pts) Net sales: Sales minus merchandise cost
      • (2 pts) Total Income: Net sales plus other income
    2. Expense
      • (3 pts) Salary & Commissions: 20000 plus 9% of this month's sales
      • (4 pts) Rent: 1500 plus 2% of this month's sales, rounded to 0 decimal places. (Rounding is NOT formatting to 0 decimal places.)
      • (2 pts) Total Expenses: Sum of the all expenses
    3. Net Income
      • (2 pts) Net Income: Total Income minus Total Expenses
      • Copy each formula above across columns C & D
    4. (2 pts) Create formulas in Column E to Sum across each row of numbers..
  4. Concept: Insert a picture from file and increase a row height.
    1. (0.5 pt) Insert a row at the top of the file above the row with the words "Income & Expenses"
    2. (0.5 pt) Insert the image file CCC-logo (This file should have been copied into the spreadsheets folder as part of the file management assignment) Do not change the size of the picture.
    3. (0.5 pt) Increase the row height to be the same height as the image.
  5. Concept: Create charts
    1. (2 pts) Create a 3D stacked column chart that shows net sales and other income across the three months.   Be sure to label and identify chart items. Include an appropriate title. Move the chart so that is next to the income area of the spreadsheet.
    2. (2 pts) Create a pie chart that shows the break down of the total quarterly expenses excluding salary & commissions.  Be sure to label and identify chart items. Include an appropriate title and percentages. Move the chart so that it is below the income chart.
  6. Concept: Work with worksheets
    1. (0.5 pt) Rename Sheet1: 1st Quarter
    2. (0.5 pt) Copy 1st Quarter worksheet so that the copy is before Sheet 2 and rename the copied sheet: 2nd Quarter (Copy the sheet, do NOT copy & paste the data)
    3. (1 pt) On 2nd Quarter sheet delete contents of cells which contain actual numbers BUT DO NOT delete cells with formulas. (Blue highlighted area shown below is the cells that contain numbers and not formulas -your spreadsheet is not highlighted, this is just to show you which cells.) Note: Right click on the selected cells, then select Clear Content.
      Spreadsheet with rows highlighted
    4. (0.5 pt) Copy the 2nd Quarter worksheet so that it is to the right of the 2nd Quarter sheet and rename the copy 3rd Quarter.
    5. (0.5 pt) Copy the 2nd Quarter worksheet so that it is to the right of the 3rd Quarter sheet and rename the copy 4th Quarter.
    6. (0.5 pt) Copy the 3rd Quarter worksheet so that it is to the right of the 4th Quarter sheet and rename the copied sheet: Summary
    7. (0.5 pt) Delete the two blank worksheets (named Sheet 2 and Sheet 3)
  7. Concept: Enter data on new sheet
    1. (0.5 pt) On 2nd Quarter sheet change the months to read April, May, June
    2. (0.5 pt) On 3rd Quarter sheet change the months to read July, August, September
    3. (0.5 pt) On 4th Quarter sheet change the months to read October, November, December.
    4. (1 pts) Enter in April's figures on 2nd Quarter sheet
      1. Sales: 249000
      2. Merchandise Cost: 178750
      3. Other Income: 45750
      4. Office Expenses: 3170
      5. Utilities: 2100
      6. Maintenance: 1900
      7. Miscellaneous: 1975
        In a business, each month would be filled in after the figures become available. May would be filled in after the end of May and a month later June. We are not going to worry about rest of the months for this exercise - they will be left blank.
  8. Concept: Build formulas to refer to numbers on Summary sheet.
    1. (0.5 pt) Insert a column for between February & March.
    2. (0.5 pt) Change the column headings to 1st Quarter, 2nd Quarter, 3rd Quarter, 4th Quarter
    3. (0.5 pt) In B column, enter a formula to the right of Sales to refer to 1st Quarter total sales.
    4. (0.5 pt) In C column, enter a formula to the right of Sales to refer to 2nd Quarter total sales.
    5. (0.5 pt) In D column, enter a formula to the right of Sales to refer to 3rd Quarter total sales
    6. (0.5 pt) In E column, enter a formula to the right of Sales to refer to 4th Quarter total sales
    7. (1 pt) Copy those 4 cells to the rows below down the rows through to the row labeled Net Income. Then delete the formula in the 2 rows after Total Income and the row between Total Expenses and Net Income.
  9. Concept: Experiment and learn something new.
    1. (2 pts) Learn a new feature that we haven't covered such as a different chart type or special formatting and add it to the document. Label or somehow indicate what feature you added.
  10. SAVE and close the file.

Please submit the file on Laulima in the Assignments, tests & Surveys section for this exercise.

Need Help?
Contact the instructor
via UH email
Please attach your file to the email.