# 1.1 Assignment: Case Problem 1

June 5, 2016

Question
1.1 Assignment: Case Problem 1

Directions

Complete Case Problem 1 on pages EX61 and EX62. You will need to use thePacific.xlsxfile for this assignment.

LP1.2 Assignment: Sparrow & Pond

This assignment will assess the skills for competency 1. Assemble Data Using MicrosoftExcel.

Directions

Download the Student Data files and save them to your computer. You will use these files throughout the course and in order to modify the file it must be stored on your hard drive or a flash (USB) drive. Throughout this course, the assignments will be to complete the workbook activities presented in the Tutorial. Follow the instructions written in the beige text boxes throughout each Tutorial. For this assignment you will use theSP Finances.xlsxfile.

LP2.1 Assignment: Case Problem 3

This assignment will assess the skills for competency 2. Enhance data interpretation by modifying workbook appearance.

Directions

Complete case problem 3Winds of Changeon pages EX127 and EX128. This activity requires theWind.xlsxworkbook found in the Student Data Files.

LP2.2 Assignment: Sales Report

This assignment will assess the competency 2. Enhance data interpretation by modifying workbook appearances.

Directions

For this assignment, you will modify the formatting of a sales report. The Documentation page has been correctly modified by changing cell and font colors and sizes, adding borders and inserting a background. On the Sales worksheet, you will modify text and fill colors, modify cell alignment, merge cells, add columns and rows, format numbers, modify borders, and add conditional formatting with a legend.Complete the Sales Report workbook by following the steps listed in the beige boxes throughout the tutorial. You will use theSales.xlsxfile found in the Student Data Files

LP3.1 Assignment: Case Problem 1

This assignment will assess the skills for competency 3. Integrating formulas and functions into Excel workbooks.

Directions

Complete the Hernandez Budget on pages EX184 and EX185. Use

theHernandez.xlsx

P3.2 Assignment: Fitness Tracker

This assignment will assess the competency 3. Integrate Formulas and Functions into Excel Workbooks.

Directions

For this assignment you will work with sum, running totals, average, IF, and VLOOKUP functions. You will also apply styles to cells. Complete the workbook steps listed in the beige boxes throughout Tutorial 3 pages EX131 to EX182. For this assignment you will use theFitness.xlsxdata file found in the Student Data Files.

LP4.1 Assignment: Case Problem 1

This assignment will assess the skills for competency 4. Depict data relationships using charts & graphs.

Directions

Complete the Bertrand Family Budget on pages EX249 to EX250. Use theBertrand.xlsxfile in the Student data files (Excel 4, Case 1).

LP4.2 Assignment: Levitt Winery

This assignment will assess the skills for competency 4. Depict data relationships using charts & graphs.

Directions

You will learn how to use several different types of charts and graphs to illustrate the financial data in the workbook. You will use the Chart Elements tool to modify the chart style, area, labels, legend, axes, and axis labels. For this assignment you will use theLevitt.xlsxfile found in the student data files. Complete the workbook by following the steps in the beige boxes throughout Tutorial 4.

LP5.1 Assignment: Case Problem 3

This assignment will assess the skills for competency 5. Reorganize data into information using Excel Lists.

Directions

Complete the Healthy Cooking case on pages EX322 and EX323. Use theCooking.xlsxfile in the Student data files.

LP5.2 Assignment: Cash Reciepts January

This assignment will assess the skills for competency 5. Reorganize data into information using Excel Lists.

Directions

For this assignment, you will be using theJanuary.xlsxfile found in the student data files. Sort and filter data using a variety of tools, sort function, filter, autofilter, slicer, PivotTables, and Pivot charts. You will use subtotals to breakdown grand total numbers to analyze the parts of the whole. Format the data as a table and create two worksheets: Daily Receipts PivotTable containing a PivotTable and Pivot Chart, and Weekly Summary PivotTable worksheet containing a PivotTable and slicer.

6.1 Assignment: Case Problem 4

This assignment will assess the skills for competency 6. Improve productivity by incorporating worksheet grouping and external referencing.

Directions

Complete the Cubed Ice Sales case on pages EX387 and EX388. Use the following files from the Student data files:Midwest.xlsx,Northeast.xlsx,Southeast.xlsx,Ice Summary.xlsx,New Midwest.xlsx, andIce Template.xltx.

6.2 Assignment: Better World Recycling

This assignment will assess the skills for competency 6. Improve productivity by incorporating worksheet grouping and external referencing.

Directions

For this assignment you will be using theBetter World.xlsx,Kansas.xlsx,Marion.xlsx,Richmond.xlsx, andRecycle Memofiles found in the student data files. You will format multiple worksheets by grouping, create 3-D references by linking worksheets, link workbooks, insert hyperlinks, and create a template.

LP7.1 Assignment: Learning Express
This assignment will assess the skills for competency 7. Develop an Excel application.

Directions

Complete the Excel Tutorial found in the Learning Express website in NAU’s online Library.

Click on Learning Express.
Create an account.
Once you have created an account, enter Learning Express. From the list on the left hand pane select “Popular Software Tutorials.”
Scroll to the center of the Window and select Microsoft Excel Courses. Select Excel 2010 for Windows. Add Excel Course 3: Advanced to My Center.
Click the My Center link on the right of the menu bar at the top of the window.
Select the course and click Start.
When the video tutorial opens, select 5.2 “Correcting Formulas” & 5.3 “Correcting Formulas Lab” from the list in the left pane.
Watch video 5.2 and complete lab 5.3 and submit a screenshot of the “Completed Lab” screen.

LP7.2 Assignment:Youth Soccer
This assignment will assess the skills for competency 7. Develop an Excel application.

Directions

For this assignment you will be using theSoccer.xlsx, file found in the student data files. You will create cell and range names, use data validation for input and error checking, create macros and command buttons to run the macros, protect worksheets, and add comments to clarify input information.

P8.1 Assignment: EVG Employees
This assignment will assess the skills for competency 8. Incorporate Advanced Logical Functions.

Directions

For this assignment you will be using theEVG.xlsx, file found in the student data files. You will create formulas using advanced IF, VLOOKUP, and IFERROR functions. You will create a rule to look for duplicate values using conditional formatting.

LP8.2 Assignment: Analysis of Student Data Project

This assignment will assess the skills for competency 8. Incorporate Advanced Logical Functions.

Directions
Mountainview Language School in Denver, CO, is a small school that provides language training in English, Spanish, and French to students from all over the world. The school keeps track of its student records in Excel. You’ve been asked to work with the current spreadsheet and improve it so that information and issues can be analyzed and tracked. You use advanced IF functions and a VLOOKUP table to calculate data related to student payments, and then you use the COUNTIF, SUMIF, and AVERAGEIF functions to analyze the payment data.

Complete this “Analysis of Student Data” Project by following the instructions. You will need theTutorial 08-StudentAccounting.xlsxfile for this project and theproject rubricas a guideline for expectations.