Loading...

Messages

Proposals

Stuck in your homework and missing deadline? Get urgent help in $10/Page with 24 hours deadline

Get Urgent Writing Help In Your Essays, Assignments, Homeworks, Dissertation, Thesis Or Coursework & Achieve A+ Grades.

Privacy Guaranteed - 100% Plagiarism Free Writing - Free Turnitin Report - Professional And Experienced Writers - 24/7 Online Support

Penn foster microsoft excel graded project formulas

01/01/2021 Client: saad24vbs Deadline: 7 Days

Computer Applications : Microsoft Excel 2016 Graded Project


Lesson 4 Overview


In this lesson, you'll create a spreadsheet that tracks the cost of


elementary school supplies distributed to teachers over a two-month


period in order to illustrate correct usage of Microsoft Excel 2016.


4.1 Design a spreadsheet that tracks the cost of elementary school supplies distributed to teachers over a two-month period Excel 2016


READING ASSIGNMENT


Your project must be submitted as an Excel workbook (.xlsx, .xls). Your project will be individually graded by your instructor and therefore may take up to five to seven days to grade. Be sure that each of your files contains the following information:


Your name Your student ID number The exam number Your email address


Note: If you have more than 10 attachments, you’ll need to collect all of your files into a compressed folder. To do this, follow these instructions based on


Page 1Copyright Penn Foster, Inc. 2019 Course Version: 1


your operating system:


Windows: Select the files you want to compress, right-click and select Send to. . . and then Compressed (zipped) folder Mac: Select the files you want to compress, control-click and select Compress


To submit your graded project, follow these steps:


Log in to your student portal. Click on Take Exam next to the lesson you’re working on. Find the exam number for your project at the top of the Project Upload page. Follow the instructions provided to complete your exam.


Be sure to keep a backup copy of any files you submit to the school!


Introduction


This project requires you to develop a spreadsheet that tracks the cost


of elementary school supplies distributed to teachers over a two-


month period. You’ll assume the role of Office Manager, who has


multiple responsibilities, including distributing and maintaining


classroom supplies. For this project, you must prepare an Excel


spreadsheet and chart that will be sent to the school District Manager.


Instructions


Create a Spreadsheet


1. Start Excel and create a new workbook.


2. Enter data as shown in Figure below, sizing columns as


necessary, and then save your file, naming it School Supplies.


Page 2Copyright Penn Foster, Inc. 2019 Course Version: 1


An image of a new excel workbook with data entered and named


School Supplies.


3. Merge and center cells A1 through H1. Format the school name


as Title style.


4. Merge and center cells A2 through H2. Format the label as


Heading 1 style.


5. Bold the labels in row 4 and right align the labels above numeric


values.


6. Format the Cost/Unit values as Currency with 2 decimal places.


Your spreadsheet should look like Figure below.


An image of the School Supplies excel workbook with formatted data.


Page 3Copyright Penn Foster, Inc. 2019 Course Version: 1


7. In column D, enter appropriate formulas to calculate the


September costs for each school supply. Your formulas must use


cell references. Format the calculated values as Currency with 2


decimal places.


8. In cell A16, enter the label “Grand Total” and then format it as


bold.


9. In cell D16, enter a formula that uses a function to calculate the


total of the column.


10. In column E, enter formulas that calculate the percentage of the


grand total for each type of school supply (total cost of each item


divided by the grand total). Your formulas must use appropriate


absolute cell references. Format the calculated values as


Percentage with 0 decimal places.


11. In cell E16, enter a formula that uses a function to calculate the


total of the column (it should total 100%).


12. Repeat the process you used in steps 7–11 to calculate values


for October.


Page 4Copyright Penn Foster, Inc. 2019 Course Version: 1


13. Sort the school supplies alphabetically. (Be sure to select cells


A5:H14 to sort the entire row of data.) Your spreadsheet should


look similar to Figure below.


An image of the School Supplies excel workbook with the data sorted


in alphabetical order.


14. Save the modified spreadsheet.


Create a Chart


1. Create a chart by first selecting the cell ranges for the chart. Drag


from cell A4 to A14 to select the range and then press and hold


the Ctrl key while you drag from cell D4 to D14. Continue to press


and hold the Ctrl key while dragging from cell G4 to G14. Your


selected cells should look like Figure below.


An image of selected cell ranges in the School Supplies excel


workbook.


Page 5Copyright Penn Foster, Inc. 2019 Course Version: 1


2. Insert a 3-D Clustered Column chart.


3. Change the chart title to read “Cost of Supplies.”


4. Position the chart with the upper-left corner in cell A18 and then


size the chart until it extends to column H, as shown in Figure


below.


An image of a chart made with the selected cell ranges in the School


Supplies excel workbook.


Page 6Copyright Penn Foster, Inc. 2019 Course Version: 1


5. Put the following information into cell A41:


Your name


Your email address


Your student number


Course name and number


Project number (584801)


6. Save and close the workbook.


Scoring Guidelines


Rubric


Page 7Copyright Penn Foster, Inc. 2019 Course Version: 1


Skill/ Grading Criteria


Exemplary (4)


Proficient (3)


Fair (2)


Poor (1)


Not Evident (0)


Enter data


The correct data has been entered into the appropriate cells.


Most of the correct data has been entered into the appropriate cells.


Some of the correct data has been entered into the appropriate cells.


Minimal correct data has been entered anywhere.


No attempt has been made to enter data into the spreadsheet.


Size columns


All columns are sized to appropriately display labels and values.


Some columns are sized to appropriately display labels and values.


Few columns are sized to appropriately display labels and values.


Only one column is sized to appropriately display labels and values.


No attempt has been made to change column widths.


Apply an Excel style


All cells are in the indicated style.


All cells have a style applied, but they are in the wrong style.


One cell has an applied style, in the indicated style.


One cell has an applied style, but it is the wrong style.


No attempt has been made to apply Excel styles.


Merge and center cells


Labels in rows 1 and 2 have been merged and centered above the appropriate cells.


The label in one row has been merged and centered.


Labels in either row 1 or 2 have been merged without centering.


An attempt has been made to center labels above the data.


No attempt to merge cells or center labels has been made.


Format labels


The appropriate labels have been formatted as instructed.


Some of the appropriate labels have been formatted as instructed.


Appropriate labels have some formatting but may not be as instructed.


Some labels have been formatted.


No attempt to format labels has been made.


Format values as Currency


The appropriate values have been formatted as Currency with 2 decimal places.


The appropriate values have been formatted as Currency without 2 decimal places.


The appropriate values display a $ but have not been formatted as Currency.


The wrong values have been formatted as Currency.


No attempt has been made to format values as Currency.


Page 8Copyright Penn Foster, Inc. 2019 Course Version: 1


Format values as


Percentage


The appropriate values have been formatted as Percentage with 0 decimal places.


The appropriate values have been formatted as Percentage displaying a decimal portion.


The appropriate values display a % but have not been formatted as Percentage.


The wrong values have been formatted as Percentage.


No attempt has been made to format values as Percentage.


Use formulas


with relative cell references


All Cost values have been calculated using 2 relative cell references.


Cost values have been calculated using 1 relative cell reference.


N/A N/A Cost values do not use any cell references.


Use formulas


with functions


All Grand Total values have been calculated using a SUM function.


N/A


Grand Total values have been calculated using a SUM function but did not include all cell references.


N/A


Grand Total values have not been calculated with a SUM function.


Use formulas


with absolute


cell references


The % of Grand Total values have been calculated with a formula that includes an absolute cell reference.


N/A N/A N/A


The % of Grand Total values have not been calculated using absolute cell references.


Sort data


Supply data (rows 5 through 14) has been sorted alphabetically by supply name.


Supply data (rows 5 through 14) has been sorted in reverse alphabetical order or by something other than supply name.


N/A


Only the Supply data in column A has been sorted, making the spreadsheet data inaccurate.


No attempt has been made to sort the data.


Page 9Copyright Penn Foster, Inc. 2019 Course Version: 1


Create a chart


A 3-D Column chart has been created using the correct data.


A chart of any type has been created using the correct data.


A chart has been created using some of the correct data.


An attempt to create a chart has been made.


No attempt has been made to create a chart.


Edit a chart title


The chart title has been appropriately changed.


The chart title has been changed to unspecified text.


N/A


An attempt has been made to change the chart title.


No attempt has been made to change the chart title.


Move and size a chart


The chart has been moved and sized to the specified location and dimensions.


The chart has been moved to the specified location or sized, but not both.


N/A N/A


No attempt has been made to move or size the chart.


Submission Checklist


Before submitting your project, make sure you’ve correctly completed


the following:


Create, save, and name an Excel file.


Enter data and labels.


Format labels with indicated styles.


Merge and center labels.


Format data with Currency and Percentage formats to the correct


number of decimal places.


Size columns appropriately.


Use formulas that include a function where applicable.


Use relative and absolute cell references in formulas.


Sort data.


Create a chart from nonadjacent data.


Page 10Copyright Penn Foster, Inc. 2019 Course Version: 1


Edit a chart.


Be sure to keep a backup copy of any files you submit to the school!


Key Points


READING ASSIGNMENT


Key Points


You resize column widths by dragging with your mouse or by


entering a numeric value in the Column Width dialog box, which


is accessed from the Format, Column Width menu on the Home


Ribbon.


You align cell contents using the Align Left, Center, and Align


Right buttons on the Home Ribbon.


You use formulas to perform mathematical calculations on


numbers in your spreadsheet.


To select non-contiguous cells for inclusion in a chart, hold down


the Ctrl key while you drag.


You position and size charts by dragging the chart handles with


your mouse.


You can apply formatting directly to the components of a chart by


clicking on them.


Page 11Copyright Penn Foster, Inc. 2019 Course Version: 1


Lesson 4 Review


Self-Check


1. You created a budget in Excel. You'd like to quickly see how each


budget category compares to the others to make up the total. What is


the best way to accomplish this?


a. A pie chart.


b. Sorting the category totals low to high or high to low.


c. The @SUM function to automatically generate category totals.


d. A formula calculating category percentages.


2. You entered an Excel function to perform a mathematical


calculation on several columns of numbers in your spreadsheet. The


cell where the function resides displays #####. What is the reason for


this?


a. The format of the cell is not set to display a numeric value.


b. There is an error in the data the formula is pulling from the


columns.


c. There is an error in the formula that you entered.


d. The column width is too small to display the formula's resulting


value.


3. You have a spreadsheet with data in rows 4 through 10 in columns


B, C, E, and F. How would you select the data to create a chart?


a. Drag to select B4 through C10. Hold down the Shift key and drag


to select E4 through F10.


b. Click the Select All button in the upper-left corner of the


spreadsheet.


Page 12Copyright Penn Foster, Inc. 2019 Course Version: 1


c. Drag to select B4 through F10.


d. Drag to select B4 through C10. Hold down the Ctrl key and drag


to select E4 through F10.


4. The title of your worksheet is 2017 Budget. You've created a pie


chart to show the budget category percentages, but the chart title


reads a generic Chart Title. How do you change it to match the


worksheet title of 2017 Budget?


a. Delete and recreate your chart making sure you include a header


row with the title 2017 Budget in it.


b. Select the Chart Title text box and delete it. Insert the Excel


TEXT function and link it to a cell in the worksheet with the text 2017


Budget in it.


c. Click on and then select the text Chart Title in the chart and link


to the worksheet name.


d. Click on and then select the text Chart Title in the chart and type


2017 Budget.


5. A colleague gives you a spreadsheet containing the personnel


records of all department employees. Employees are added to a row


at the bottom of the spreadsheet as they are hired. As a result, you


have to scan the entire spreadsheet to find the information for


employees. What is the best way to resolve this problem?


a. Request that the colleague restructure the spreadsheet so that


employees are added in alphabetical order instead of by hire date.


b. Search for each employee's data you want to work with.


c. Apply a filter to display just the employee data you want to work


with.


Page 13Copyright Penn Foster, Inc. 2019 Course Version: 1


d. Apply a data sort to your copy of the spreadsheet, so that


employees are in alphabetical order by name.


Self-Check Answer Key


1. A pie chart.


Explanation: A pie chart is a graphical representation of data


where the entire circle equals the whole and the individual


sections are graphically represented as sections showing their


proportion of the whole.


Reference: Section 4.1


2. The column width is too small to display the formula's resulting


value.


Explanation: When Excel displays ##### in a cell, it means one


of two things:


1. The column is not wide enough to display the information in


the cell.


2. There is a negative date or time in the cell.


Reference: Section 4.1


3. Drag to select B4 through C10. Hold down the Ctrl key and drag


to select E4 through F10.


Explanation: To select non-contiguous cells in Excel, drag to


select your first range of cells. Then hold down the Ctrl key while


Page 14Copyright Penn Foster, Inc. 2019 Course Version: 1


you drag to select the additional cell ranges.


Reference: Section 4.1


4. Click on and then select the text Chart Title in the chart and type


2017 Budget.


Explanation: A text box contains the title of the chart. It is editable


just like any other text box.


Reference: Section 4.1


5. Apply a data sort to your copy of the spreadsheet, so that


employees are in alphabetical order by name.


Explanation: The Sort feature in Excel allows you to change the


view of your data, rearranging it into a specified order. For


example, text can be sorted alphabetically or reverse


alphabetically, and numbers can be sorted in ascending or


descending numeric order.


Reference: Section 4.1


Flash Cards


1. Term: Chart


Definition: Visual representation of data


2. Term: Formula


Definition: Instructions to perform mathematical calculations on


specified values in a spreadsheet


Page 15Copyright Penn Foster, Inc. 2019 Course Version: 1


3. Term: Function


Definition: A preset formula in Excel


Page 16Copyright Penn Foster, Inc. 2019 Course Version: 1


Lesson 4 Overview

Introduction

Instructions

Create a Spreadsheet

Create a Chart

Scoring Guidelines

Rubric

Submission Checklist

Key Points

Lesson 4 Review

Homework is Completed By:

Writer Writer Name Amount Client Comments & Rating
Instant Homework Helper

ONLINE

Instant Homework Helper

$36

She helped me in last minute in a very reasonable price. She is a lifesaver, I got A+ grade in my homework, I will surely hire her again for my next assignments, Thumbs Up!

Order & Get This Solution Within 3 Hours in $25/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 3 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

Order & Get This Solution Within 6 Hours in $20/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 6 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

Order & Get This Solution Within 12 Hours in $15/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 12 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

6 writers have sent their proposals to do this homework:

University Coursework Help
Top Essay Tutor
Helping Hand
Writer Writer Name Offer Chat
University Coursework Help

ONLINE

University Coursework Help

Hi dear, I am ready to do your homework in a reasonable price.

$37 Chat With Writer
Top Essay Tutor

ONLINE

Top Essay Tutor

I have more than 12 years of experience in managing online classes, exams, and quizzes on different websites like; Connect, McGraw-Hill, and Blackboard. I always provide a guarantee to my clients for their grades.

$40 Chat With Writer
Helping Hand

ONLINE

Helping Hand

I am an Academic writer with 10 years of experience. As an Academic writer, my aim is to generate unique content without Plagiarism as per the client’s requirements.

$35 Chat With Writer

Let our expert academic writers to help you in achieving a+ grades in your homework, assignment, quiz or exam.

Similar Homework Questions

Discussion - Week 1 COLLAPSE Current Personnel Psychology Trends and Social Responsibility in Human Resource Management - Introduction to computer programming mq - Oxidation lab report - Is bio greek or latin - Discussion 1 clinical - Phi 208 week 3 discussion 2 - A bar of steel has the minimum properties - Weekly Post #2 - Illusions richard bach chapter summaries - Pg community college pool - Nco strategy 2020 - Clawson's diamond model of leadership - 2.1 prepare income statement for may - Description of a walk robert gray annotations - Wk 1, HCS/430: DQ - Iaru global summer program - Valuation report cover page - The rich eat three full meals - Plagiarism certification test answers - Case Study - Youreconportal - Two questions need two answers ( number the answers for each question) - Stage 3 english units of work - Measuring internal resistance using potentiometer - What is a code 9 police - The manager of weiser is given a bonus - Bill gates characteristics as an entrepreneur - Lens of a cow eye - Working capital problems with answers - Script of the movie titanic - Sample observation report child care - Chemical name of ag2so3 - Percent of nahco3 in alka seltzer - 10 column worksheet adjusted trial balance - Ligation of an intraoral salivary duct cpt code - Https honda garmin com honda site generalinstructions - Genius g pen f610 ultra slim tablet - Using accrual accounting expenses are recorded and reported only - Last month when holiday creations inc sold - Acme anvils has a newly patented - Worldventures policies and procedures - Teacher aide award wage qld - How to get a customer reference number - Parking permit newcastle university - Faraday law of induction lab report - Week 6 lab summit ridge mountain resort student - Finance - North hinksey parish council - Lindley methodist church huddersfield - Written assignment - 7Ass ( Kim Woods) only - Child case study assignment - Australian standard safety glasses - Microsoft pinyin windows 7 - Week 5 - Saum song bo - Keirsey temperament sorter questionnaire - What sets christianity apart from other religions - How do the effects of insulin resistance compound to decrease cellular responses to insulin? - Executive Program Practical Connection Assignment - Bermingham v corrective services commission of nsw 1988 - Answer one question - Film: Stonewall Uprising ( 200 words or more) - Weber unit of magnetic flux - 1: Describe at least 4 things that the earliest civilizations - Malapportionment and gerrymandering difference - Which of the sdlc models is the most rigid in its use in developing an information system? - A crm software database system provides sport marketers - John harrox primary school - Kissin kate barlow drawing - Read and respond-3 - Career connection values and strategy paper str 581 - Lancashire grid for learning email - Computer security threats and solutions ppt - Tri component model psychology - Sc1040 week 4 assignment worksheet - Half of a4 size in cm - Venom and spiderman fanfiction - City of monroe continuous problem solution - Australian air force defence guard - Scott bonnar green paint - The rocky mountain district sales manager of rath publishing inc - Pathophysiology Knowledge Check - 3526 islay lane crete il - Apply a circle style cell bevel to the first row - Fuel injector circuit malfunction - Excel assignment , statistics and probablity, report needs to be word file but i have attached excel file to get information - Iqtest - What obligations the us government has towards its citizens - Deliver in 10hrs ip pm - Burger king case analysis - Wonders of the solar system - What is the primary objective of the financial manager - Hose spray gun wilko - Which of the following is equivalent to the expression below - What malfunctioned organelle causes tay sachs disease - An early bebop bass player whose walking bass lines may be heard on "koko" and "leap frog." - The Big Stage - Breaking tradition by janice mirikitani analysis - Michelin fleet solutions from selling tires to selling kilometers