Store Invoice

Student Learning Outcomes:

After completing this assignment the student will be able to apply the following important spreadsheet creation skills:

Resize column width

Insert Row

Format Row, Column, Cell (font and alignment)

Sheet Convention $ of 1^st and last row, comma others, no cents on amounts over $100

Print a properly formatted sheet with headings and formula with gridlines, row & column headings, formula/values. (Follow instructions on Print Hints)

Formulas with

AutoSum

subtraction, division, multiplication

IF function

PMT function

Drag formula down columns

Value of spreadsheet is ability to have numbers recalculated quickly and accurately so via alternative can be tried and best solution implemented

Pie Chart, Explode Pie, Patterns in slices

Procedure

Assessment

Naming Cells

IF function

VLOOKUP

PMT

Checklist

Print Hints

Procedure:

Create the sheet below with:

your choice for room sizes and carpet cost for remaining 3 rooms

the correct formula for

	Total
	Discount (10% of total order over $1,000)
	Net Due
	NJ Sales Tax
	Total Due
	Monthly Payment if financed for 1 year at 11%

Add a row for the dining room right after the living room row

Change the length, width, cost per yard, discount percentage, $500 minimum discount and make sure all appropriate numbers change.

First, try it yourself. But if you need help, follow the step by step procedure

	Enter Row 1 which is the column Headings per below
	Enter Row 2, the Master Bed Room per below
	Enter the Labels column A and the values in column B, C and D
	Enter the formula in E2 (the area of the room converted to square yards time the price per square yard)
	Drag the formula in E2 down to E5
	Total the rooms cost in E6
	Highlight the room cost and room names and use the chary wizard to make a pie chart
	Customize the chart legend, title, slice patterns and explode on slice
	Calculate the Discount using a IF function
	Instruct Excel how to calculate the net due, sales tax and Total due using = and then pointing at the appropriate cells and typing + (add), - (subtract) * (multiply) and/or / (divide0
	Calculate the monthly payment using the PMT function (remember there are 12 months in a year and only 1/12^th of the interest is paid each month.)
	Put a page heading on the sheet with a title, your name and the date. Try some interesting fonts.
	Add a row for the dining room right after the living room row
	Change the length, width, cost per yard, discount percentage, $500 minimum discount and make sure all appropriate numbers change.

Make the most of Spreadsheet Hints

Use the Spreadsheet checklist to make sure you haven't forgotten anything.

Upload your spreadsheet with pie chart of the breakdown of room costs to your Briefcase.

Email me to go look at your spreadsheet in the briefcase.

Print the sheet properly formatted with its imbedded chart.
Switch to formula view and set up with Gridlines and Row and Column labels and print again.
This is to be put in your portfolio.

	A	B	C	D	E
1		Length	Width	$per sq yard	Room Cost
2	Master Bed Room	21	22	$33	calculated
3	Kid Room				calculated
4	Living Room				calculated
5	Hall				calculated
6	Total				calculated
7	Discount (10% of total order over $500)	10%	of amount Over	$500	calculated
8	Net Due				calculated
9	NJ Sales Tax				calculated
10	Total Due				calculated
11	Monthly Payment if financed for 1 year at 11%	11%			calculated

Advanced	Proficient	Partially Proficient	Non-Proficient
A	B	C	F
Follows all the Cardinal, formatting, naming, printing and charting rules on the Checklist Sheets is labeled and ordered. Placement of data shows planning and facilitates easy reading & comprehension.	Follows all the cardinal rules on the Checklist. Violates 1 or 2 formatting, printing or rules.	Follows all the cardinal rules on the Checklist. Violates 3 or 4 formatting, printing or rules.	Chart missing. Only formatted values or formula sheet submitted. One cardinal spreadsheet rule or 5 or more formatting or charting rules ignored.

Search this site or the web powered by FreeFind Site search Web search

Student Learning Outcomes:

Procedure:

Assessment

Search this site or the web powered by FreeFind

Site search Web search