Execl Spreadsheet assignment

Assignment 3 COMP 1260 Fall Session 2014

Page 1 of 7

Advanced Spreadsheet Concepts

Spreadsheet Skills examined:

 Importing data from an Access database

 Using advanced features in Excel to create a multi-worksheet workbook

 using formulas and advanced functions to organize and present data

 using advanced formatting to create a publishable page.

Due Date:

Assignments are due at 10 pm on Sunday, November 16, 2014. No late submissions will be accepted.

Assignment Context

Assignment #3 is based on the book club brochure you created for your Assignment 2. The book club

publishes fine quality illustrated versions of old books that are no longer covered by copyright (e.g., the works

of Shakespeare or Dickens). The club has been running well for several months with lots of sales. You need to

create a spreadsheet to analyze your sales, and monitor costs and income.

General Spreadsheet Specifications In this assignment you will be preparing an Excel spreadsheet that accesses the MS Access database to obtain

raw data. It then analyzes this data and presents it as formatted text and numbers, and in charts. The

spreadsheet also contains other calculations related to running the book club. General rules:  Demonstrate good spreadsheet design principles as discussed in class and in the notes. All tables must,

at a minimum, have most of their data visible (by properly sizing column widths), have column labels

(heading text) that is formatted differently from data, use consistent styles for font, shading, borders,

and these formatting elements must be used thoughtfully to enhance the readability of the table and

highlight important parts. Data must use appropriate and readable formats (e.g. currency amounts

should be in currency or accounting format)

 Save the spreadsheet in Excel 2010/2013 (.xlsx) format, with a filename of the form

“Lastname_Firstname_A3_vX.xlsx”; e.g., Dent_Stew_A3_v2.xlsx.

 The spreadsheet must be no larger than 500 KB. So you will still need to be careful in the design of

your spreadsheet.

Steps to Completing the Assignment: 1. Import data from Access: Import tabular data from a database. Combine data from several tables to

produce detailed records. Add both horizontal and vertical calculations.

2. Analyze purchase data in a PivotTable: Calculate sales numbers and sales totals for each author

offered by the book club, grouped by age range. Display the results in a chart.

3. Calculate book prices based on the cost of production: Determine how many copies must be sold to

keep prices in line.

4. Submit the final spreadsheet through the submission form available on the ICM portal.

Step 1: Import data from Access In this scenario, your new book club has been operating for several months, and it has completed hundreds of

transactions. Each transaction is limited to one member buying one book from the book club. The details of all

the transactions have now been stored in an Access database. You want to analyze this data, but quantitative

Assignment 3 COMP 1260 Fall Session 2014

Page 2 of 7

analysis in Access is rather cumbersome. To simplify the analysis, you will move the data to a spreadsheet.

Fill the Excel spreadsheet with data by:

 Starting Excel, and creating a new blank workbook with the required name.

 On the Page Layout tab, applying a Theme that is different from the default, and that uses a sans serif

font for data cells. You may change this Theme later if you don’t like the appearance of your

worksheets.

 Importing the Purchases, Members, and Books tables from the database Dent_Designs.accdb, which

you can download from the COMP1260 website on ICM portal (for the import click From Access in

the Get External Data grouping of the Data tab).

 Read each table into the top left corner of a worksheet and rename it to have the same name as

the Access table. In each case, put a prominent label in Heading 1 style above the table to say

what it is. The layout look for Books should be like Figure 1 on the next page.

 Excel automatically formats the imported data as tables, with headers above the columns and

alternating colours for the rows. Change the table formats (using Format as Table) so that

Purchases table is in black and white, with borders between the columns (see Figure 2 below),

the Members table has two shades of background colour, neither of them white or grey, and the

Books tables has white as the background colour for every second row as in Figure 1.

 Apply Wrap Text to the header row of each table, so that word wrap will occur in these cells.

Then make all columns suitably narrow, using word wrap to reduce the horizontal space needed

for multi-word headers (see Figure 1). Remember to adjust row height appropriately so the full

header can be seen.

 Any formula reference to a field in these tables gets a very long name, based on the name of the

Access file from which they came. Use the Name Manager on the Formulas tab to change the

names of these tables to Purchases, Members, and Books.

 In the Books table, apply a Right (indent) alignment to the Year of Publication, a medium date

format (e.g. 21-Sep-2014) to the Release Date values and Accounting Format to the Price

values. Add a Column for a Short Title as shown in Fig. 1

Figure 1: Books Table (after addition of a Short Title)

 If necessary, re-arrange the columns of the Books and Members tables so that the key field

(ISBN and Member_ID, respectively) are in the first column (and still part of the table). The

tables must be sorted on these fields for the Lookups described below.

 Ensuring you can see the column headers in the Purchases and Members tables when you are looking

at records past the first page. Use Freeze Panes on one of them and Split Table on the other. Make sure

Assignment 3 COMP 1260 Fall Session 2014

Page 3 of 7

all the column headers and at least one line of data are visible above the division. (You want a data row

above the line so you can click on a cell in the first row and shift-click on the bottom cell in the same

column to quickly select the data in that column.)

 Inserting extra columns into the tables to make them more useful

 Insert an extra column for Age in the Members table, to the right of the Birth Date column. It

should contain a whole number (no fractions) giving the Member’s age on December 19, 2014

(the last date for purchase orders). Using the DATE( ) function, find the number of days from

the birth date to December 19, 2014, divide by 365.25 and apply the FLOOR function. Excel has

a very good Help facility that explains how to use each function.

 Use VLOOKUP in the Purchases table to add columns for Short Title and Price to the right of

the ISBN column, and Member Name and Member Age to the right of Member_ID. In the

Member Name column, combine Title, First Name and Last Name from the Members table

using the & operator to make one field (The & operator joins character strings together. For

example, a cell that contains the formula ="The"&" "&"end" would show the value The end ).

Remember in each case that the tables containing the values you look up must have the key field

in the first column and have keys sorted in increasing order

 With all the extra fields, the Purchases table is quite wide. To ensure you can see the entire

width, change the Zoom on the Purchases worksheet to 90% (or less if necessary).

Figure 2: Purchases Table (your numbers will differ slightly)

Step 3: Analyze the Purchase data You are interested in investigating who is buying your books so you can better target your advertising. As a

start, you can look at the age of your members using the data in the Purchases Table, as shown in Figure 2

above, by:

Assignment 3 COMP 1260 Fall Session 2014

Page 4 of 7

 Creating a summary box as shown, surrounded by a Thick Box Border. Entries in this box use Heading

2, Heading 3 and Normal styles. Some of the cells have been merged together horizontally to provide

more room.

 Calculating the count of all purchases using the COUNT function, the total of all purchases using the

SUM function, and the average value using the AVERAGE function. (The count you see will differ

from that shown in Figure 2, which includes all the original transactions…you are analyzing the subset

of purchases up to December 19, 2014).

 Calculating the count of purchases, the total of purchases and the average purchase for members who

are under or over a given age cut-off…these will also provide checks that the Pivot Table that you will

do later gives correct results. The method is:

 The age cut-off is shown to the right of the headings “Members under” and “Members at and

over” in Figure 2. Using this as a parameter for the logic condition (to make it easy to change the

age cut-off).

 To count only some of the records, use the COUNTIF function, and to sum only some of the

records use the SUMIF function. In both cases you must provide the condition that the records

must meet in the argument list when you call the function. For example, if you use the formula

=COUNTIF(T20:T40,"<30"), the result will be the count of the number of entries in the range

from T20 to T40 that are under 30. If you use =COUNTIF(T20:T40,"<"&T1) the cell will

contain the count of entries in the range T20 to T40 that have values less than that of cell T1

 Calculate the average value by dividing the total by the count. (Note: although there is an

AVERAGEIF function, it makes more sense to do one simple divide)

 Show all dollar amounts in the Purchase Summary in Currency format.

 Extend the age-based sales analysis with a PivotTable. Click anywhere in the Purchases table and

insert a PivotTable on a new worksheet to be called Sales by Age. Make sure this worksheet is the first

one in the workbook. Make your PivotTable look like Figure 3 below (with slightly different numbers).

You do this by dragging and dropping the correct fields into the Column Labels, Row Labels and

Values panes of the PivotTable Field List dialog box. The Pivot Table will be demonstrated by your

instructor in class.

 Charting the data from the count part of the PivotTable.

 First copy the Row Labels and the Counts and Sums in the last (Grand Total) column of the Pivot

Table. Use Paste Values to create a table below the PivotTable with 3 columns (Age Group,

Count of Sales, Total Sum of Sales). This keeps a permanent snapshot of the data as of right

now. Label it as Purchases by Age as of 2014-Dec-19.

 Format this data set (borders, fonts, background fills, alignment) the same as the Books table on

the Books worksheet without using Format as Table or otherwise defining this region as a

named table. (It is important to know how to do this formatting without creating a table since

tables are not always appropriate. For example, one should, in general, not use a table when there

are calculations between the rows, since sorting by a column could make the calculations fail.)

 Select this data and insert a chart below or beside the table. Use a combination chart with a

secondary axis for Total Value of Sales. Label all axes.

 Make sure there is a chart title and that all three data series (age group, count, sum) are clearly

labelled as axis labels or in a legend.

Assignment 3 COMP 1260 Fall Session 2014

Page 5 of 7

Figure 3: PivotTable (Your numbers will differ slightly)

Step 4: Computing book prices The original book prices were set by gut feel. As the costs mount, you realize that you will have to be more

analytical about setting prices, or you could lose money on your book club. Figure 4 shows a preliminary

analysis of costs and profits based on page count and expected sales. You should first replicate this analysis,

on a new worksheet labeled Pricing, and then improve it, by:

 Setting up two tables as shown in Figure 4 (next page).

 Set up the bottom Assumptions table as shown, with the values shown. Format it by hand, and do

NOT make it into a named table. Use Currency and Percentage formats as shown. Any formula

references to values in the Assumptions table should have $ on both the column and row

reference. E.g., a reference to Binding Cost would use $B$20 with the layout shown in Figure 4.

 Set up the general structure of the top table, including the column headers and first three

columns, and then make it a named table using Format as Table. There are two main advantages

of doing so: automatic formatting, and automatic propagation of changes (when you change the

formula in one cell of a column, it is automatically used in other cells in the column). You do not

have to use the colour scheme or formatting shown in the example. The extra headers Costs and

Prices above and the statistics rows Total, Min and Max below are NOT part of the table.

 Use Paste Link to set up the book titles, with the links referring back to the Books table (Short

Title). This approach ensures there is no error in inserting the book titles. Use the Number of

pages and Expected sales numbers shown in Figure 4.

 To calculate the Design cost (per copy), multiply the Page design cost (per page) by the Number

of pages, and add the Book design cost. Then divide by the Expected sales. As with most

retailing, if you sell more copies, the cost of initial design goes down per unit sold.

Assignment 3 COMP 1260 Fall Session 2014

Page 6 of 7

Figure 4 Pricing Calculations Before Goal Seek

 To calculate the Publishing cost (per copy), add the Publishing cost: cover to the Binding cost

and then add the cost of publishing the pages, which is the product of the Number of pages and

the Publishing cost (per page).

 After some consideration, you decide to divide up the Overhead cost (providing space in your

basement, telephone, electricity, internet, etc.) equally among all the books sold. The Overhead

(per copy) is found by dividing the Overhead costs by the Total Expected sales.

 Total (per copy) cost for each book is the sum of the Design cost (per copy), Publishing cost

(per copy) and Overhead (per copy). Make sure to sum over a range here, in case another cost

column is added later.

 The Profit (per copy) is just a fixed markup by the Profit percentage on the Total (per copy)

cost; this is the amount you need to make so that your book club is worth doing.

 The Optimal price of each book is the sum of the Total (per copy) cost and Profit (per copy). But

nobody sells a book for $10.21; they raise the price to $10.49. This is the Marketing price. If the

Optimal price ends in .00 to 0.49, set the cents to 49; if it is over 0.49 set the cents to 99. You can

do this calculation using the MOD function to find the number of cents, the FLOOR or TRUNC

functions to remove the cents from the price, and the IF function to handle the two cases.

 Apply check marks and X’s to the Marketing prices with Conditional Formatting. Select all the

Marketing price entries and click on Conditional Formatting. Add a New Rule to apply the

Limits on good and acceptable price in the Assumptions. Choose Icon Sets as the format for the

rule. Then explore the options till you can get a check mark when the price is below the Limit on

good price, an ! when the price is below the Limit on acceptable price, and a red X otherwise.

 Use the AutoSum feature, or just code in the function values, to get the Min and Max rows at the

bottom of the table. For the Total, you need to multiply the Expected sales for each book by the

per-copy cost and add these up for all books. The SUMPRODUCT function does this task

handily. Put the correct formulas for these summary rows in one column, and drag them across to

Assignment 3 COMP 1260 Fall Session 2014

Page 7 of 7

the other columns. Make sure you do or do not have absolute references in your formulas as

required.

 Format the cells above the table (you must merge some cells) and the cells below the table with

suitable fill colours and borders.

 Correcting the pricing. The books as priced are too expensive, as one of them is marked with an X.

 You decide to try to get a better Publishing cost (per page) by negotiating with several printers.

Use Goal Seek (under What If Analysis on the Data tab) to determine what price per page you

would need so that the maximum price for any of the books in the table is $19.99. Leave the

result you get as your Publishing cost (per page), displayed to 4 decimal places, when you hand

in your assignment.

 Charting the Expected sales versus the Market Price in a scatter plot to see what relationship there is

between these variables

 Make sure you have a chart title and labels on both axes

 Adjust the range of the horizontal axis (from 0 to 30 to 10 to 30)

 Do not join the points representing individual books by lines

 Remove the legend

 Add a linear trendline to see if it tends upwards (sales increase with price) or downwards (sales

decrease with price).

 Deleting any additional sheets in the workbook that are not required by these instructions.

Step 5: Submit the Assignment for Marking

The assignment should be submitted in the assignment submission form that will be made available on

the portal (http://learning.icmanitoba.ca) shortly. These forms are time sensitive and will not accept

submissions after the deadline.

END OF DOCUMENT