Week 1 — instructor solutions

Excel foundations, data types and descriptive summaries

Author

ETX1100 / ETX5900

These answers use the current Week 1 workbook. Worksheet row numbers include the header. Formula cells can be placed in any blank column; adjust output references if you choose another position. This page is the instructor solution key.

Download the Week 1 worked Excel workbook.

Workbook orientation and formulas

Watch me: orient to the workbook / Now you try: find your way around

Solution 1

Stores has 75 observations in rows 2–76 and four data columns: Store ID, Sales, Profit and Open 24 Hrs?. RealEstate has 329 properties in rows 2–330. Store 10’s profit is in C11: AUD 278,625. D51 records whether Store 50 opens 24 hours: No. Freeze the top row with View > Freeze Panes > Freeze Top Row. The requested working-copy name is week1-yourname.xlsx; save it before editing.

Watch me: formula, reference, fill / Now you try: build and check a formula

Solution 2

If E is blank, enter =C2/B2 in E2 for profit as a proportion of sales and format as a percentage. Store 1 gives 24.9944%. A distinct “Profit per $100 sales” column can use =100*C2/B2, giving AUD 24.99 per AUD 100 for Store 1; do not apply percentage formatting to this dollar value. Fill to row 76. Check E2, E38 and E76 against the corresponding Sales and Profit cells. A formula with a mistaken absolute reference may appear correct in the first row but fail after filling.

Watch me: look up Store 50 / Now you try: complete the lookup

Solution 3

=VLOOKUP(50,$A$2:$D$76,2,FALSE) returns AUD 935,257 sales. Change only the column index to 3 for AUD 251,013 profit or 4 for No. An exact-match lookup of Store 500 returns #N/A because no such ID exists.

Watch me: a flexible lookup / Now you try: change the question

Solution 4

=INDEX(C2:C76,MATCH(74,A2:A76,0)) returns Store 74’s AUD 518,998 profit. Changing C2:C76 to D2:D76 returns No. The MATCH lookup range and exact-match 0 stay fixed; the sought ID and result range change. Verify against row 75.

Watch me: make a business flag / Now you try: classify and handle errors

Solution 5

For a profit threshold, use =IF(C2>=300000,"Yes","No"); Store 1 is No, Store 74 Yes. Fill down. =IFERROR(VLOOKUP(500,$A$2:$D$76,2,FALSE),"Store not found") displays the requested message. An error handler must not conceal an incorrect table or column reference.

Watch me: filter the stores / Now you try: answer with filters

Solution 6

Apply filters to the four-column data range. With Profit < 200000, 15 stores remain; 12 of those have Open 24 Hrs? = Yes. Store 37 has the greatest sales in this subset, AUD 1,202,917. Clear filters and confirm 75 store rows. For the demonstration, use Open 24 Hrs? = No and Sales > 1000000; filtering hides rows without changing the source.

Watch me: conditional summaries / Now you try: change the criterion

Solution 7

The demonstration formulas give 75 stores, 63 open 24 hours, and AUD 17,345,036 total profit for those stores. For non-24-hour stores, use =COUNTIF(D2:D76,"No"), =SUMIF(D2:D76,"No",B2:B76), and =SUMIF(D2:D76,"No",C2:C76). Results: 12, AUD 13,938,917 sales, AUD 3,378,485 profit. Check that 12 + 63 = 75 and that the two profit totals sum to AUD 20,723,521. A business interpretation: twelve stores do not open 24 hours and together generated AUD 13.94 million in sales.

Watch me: first PivotTable / Now you try: summarise RealEstate

Solution 8

The Stores PivotTable has 63 Yes and 12 No rows; corresponding sales totals are AUD 76,467,067 and AUD 13,938,917. In RealEstate, put SchoolZone in Rows and use Count of Price and Average of Price in Values. Yes: 262 properties, mean AUD 1,258.79 thousand. No: 67, mean AUD 1,046.04 thousand. The difference is an observed group difference; it does not establish that zoning caused it.

Types and data dictionary

Watch me: classify the first variables / Now you try: complete the classification

Solution 9

Price ($000s) and Area (sqm) are numerical measurements (treated as continuous). Beds, Baths and Cars are discrete counts. SchoolZone, Auction, New and Subdivided are nominal Yes/No categories. The RealEstate sheet is cross-sectional: each row describes a different property, with no regular time index. A code such as Store ID identifies a unit; it is nominal even though written as a number.

Now you try: build a data dictionary

Solution 10

Variable	Meaning	Unit or categories	Type
Store ID	unique store identifier	integer label	nominal ID
Sales	store sales	AUD	continuous measure
Profit	store profit	AUD	continuous measure
Open 24 Hrs?	whether store operates all day	Yes/No	nominal
Price ($000s)	property selling price	AUD thousands	continuous measure
Beds, Baths, Cars	counts of property features	whole-number counts	discrete
Area (sqm)	property area	square metres	continuous measure
SchoolZone, Auction, New, Subdivided	property attributes	Yes/No	nominal

State the observation unit separately: a store in Stores and a property in RealEstate. The workbook does not specify a target population or sampling design, so avoid generalising automatically.

Centre

Watch me: price centre

Solution 11

For RealEstate price in A2:A330, AVERAGE, MEDIAN and MODE.SNGL return 1,215.46, 1,180, and 1,100 in AUD thousands. Thus the average selling price is about AUD 1.215 million, the middle property is AUD 1.180 million, and AUD 1.100 million is the most frequent recorded price. The mean exceeds the median, consistent with some higher-priced properties pulling it upward.

Now you try: compare centre in Stores

Solution 12

Variable	Mean (AUD)	Median (AUD)	Mode
Sales	1,205,413	1,127,332	none; all 75 values are unique
Profit	276,314	265,014	none; all 75 values are unique

Use =AVERAGE(B2:B76) and =MEDIAN(B2:B76) for Sales, then column C for Profit. MODE.SNGL returns #N/A when there is no repeat. The median is a useful typical-store summary when unusually high values matter; report the mean as well for totals and planning. If one copied value is made extremely large, the mean changes substantially while the median is usually stable. The precise amount depends on the value chosen.

Readiness and closing checks

A cell address combines column and row (for example C11). B2:B101 is the inclusive range of 100 cells in column B. A typed value is stored as entered; a formula begins with = and recalculates from its references. The examples above demonstrate lookup, IF, IFERROR, filters, conditional summaries and PivotTables. Before ToolPak work, confirm Data > Data Analysis is visible in desktop Excel. The Week 1 centre table gives a complete worked closing-check answer: the median is a robust typical-store measure, and the mode is unavailable for Sales and Profit.