FOR ORDER THIS AND ANY OTHER SOLUTION MANUAL
CONTACT US AT
WHISPERHILLS@GMAIL.COM
Chapter 1 EXCEL TOUR
Questions
Identify some new features in the 2013 version of Excel.
Why would you use conditional formatting?
Explain the difference between a worksheet and a workbook.
Describe the nature of the commands found on the File and Home tabs.
What and where is the Quick Access Toolbar?
Describe the three basic components of the Ribbon.
How do you modify the Quick Access Toolbar to add the Open command?
What is the Page Layout view?
What is the quickest way to get help while in Excel?
What are the key accounting topics addressed in this text that relate to your learning Excel?
Chapter 2 EXCEL BASICS
Chapter 2 Questions
1 Explain how information is entered into an Excel worksheet.
2 Identify the difference between labels and values.
3 What does it mean when a cell or cells in a worksheet include a series of
# signs?
4 Explain the process for changing column width or row height.
5 How do you control the appearance of data in a worksheet?
6 What does the Format Painter tool do?
7 How are formulas written in Excel?
8 Explain the arguments used in the SUM function.
9 Explain the importance of parentheses in formulas.
10 How does the AutoFill feature of Excel help the worksheet user?
11 Why would you want to use absolute references instead of relative
references?
12 How do you change a relative reference to an absolute reference?
13 Describe the procedure used to insert a column or row into a worksheet.
14 How do you change the name of a sheet in a workbook?
15 What is the recommended procedure for printing a workbook or worksheet?
Chapter 2 Assignments
1 Create a trial balance for What SUP as of March 31, 2016
You are to create a trial balance in a format identical to that created in
this chapter. Include a debit and credit column, totals for each column,
and appropriate labels and formatting. Start with blank spreadsheet and
once you have created the spreadsheet save the file as ch2-03_student_
name (replacing student_name with your name). An alphabetical listing
of account information as of March 31, 2016, follows.
Accounts payable 75,182.10
Accounts receivable 90,432.41
Accumulated depreciation: Building 65,000.00
Accumulated depreciation: Equipment 11,000.00
Advertising expense 14,265.74
Building 300,000.00
Cash 32,511.34
Common stock 10,000.00
Cost of goods sold 341,876.41
Depreciation expense 8,000.00
Equipment 72,000.00
Inventory 118,348.94
Land 125,000.00
Long-term debt 420,000.00
Other expense 1,500.00
Payroll expense 69,421.72
Prepaid expenses 13,842.11
Retained earnings 192,697.36
Sales revenue 434,925.13
Supplies 8,434.19
Utilities expense 13,171.73
a. Print the completed worksheet in Value view, with your name and
date printed in the lower left footer and file name in the lower right
footer.
b. Print the completed worksheet in Formula view, retaining gridlines
and row and column headings, with your name and date printed in
the lower left footer and file name in the lower right footer.
2 Modify What SUP Sales Worksheet
Using the student file Ch2-04.xlsx, add appropriate formulas to the existing
Fanatic SUP Sales worksheet to calculate price, total sales for each
ring for the week, and total sales for all Fanatic SUP. Complete this
worksheet in a manner consistent with the Weekly Badfish SUP Sales
worksheet completed in this chapter. Prior to entering your formulas, the
worksheet should look like Figure 2.21. Save the file as ch2-04_student_
name (replacing student_name with your name).
Figure 2.21
Partially Completed
Diamond Ring Sales
Worksheeta. Print the completed worksheet in Value view, with gridlines and row
and column headings and with your name and date printed in the
lower left footer and file name in the lower right footer.
b. Print the completed worksheet in Formula view, retaining gridlines
and row and column headings, with your name and date printed in
the lower left footer and file name in the lower right footer.
Chapter 2 Case Problem 1:
KELLY’S BOUTIQUE
Kelly’s Boutique, located in Pewaukee, Wisconsin, sells a unique combination
of books and women’s shoes. Customers love to peruse her book inventory
while trying on the latest in shoe fashions, often buying both books and shoes
even though they came in to buy only one type of merchandise. Casey, Kelly’s
youngest son, a college student studying accounting, is home for the holidays
and can’t wait to help his mom come in from the dark ages and use computers
in her business. Throughout this text Casey will make every attempt to bring
his mom up to speed by teaching her the use of Excel and Access as they apply
to her accounting and business needs.
To begin, Casey suggests that Kelly use a worksheet to make a list of her
book inventory. She doesn’t maintain a large inventory, but she does carry
books that she thinks moms in the community might be interested in reading or
buying as gifts for their children or friends.
Start with worksheet ch2-05. Format this worksheet using bold and italics
formatting for the titles and bold and border formatting for the column names.
Add formulas for column E to compute the sales price as list price less the discount
specified. Add the following two books and related information to the list.
(Be sure the list maintains its alphabetic organization by inserting rows in the
correct place.)
Dept Product Author List Price
Children Make Way for Ducklings McClosky 17.99
Adult Snow Falling on Cedars Guterson 21.95
Insert a new column D with the title ‘‘On Hand’’ in bold, centered, and center
justified. Change the formatting of the column to no decimals. Add the following
on hand values to column D.
Product Author On Hand
Angela’s Ashes McCourt 5
Betsy – Tacy Lovelace 2
Blueberries for Sal McCloskey 2
Caddie Woodlawn Brink 1
Deep End of the Ocean Mitchard 4
Divine Secrets of the YaYa Sisterhood Wells 3
Green Eggs and Ham Seuss 1
Harry Potter and the Chamber of Secrets Rowling 4
Harry Potter and the Prisoner of Azkaban Rowling 3
Harry Potter and the Sorcerer’s Stone Rowling 2Product Author On Hand
Hop on Pop Seuss 1
Horse Whisperer Evans 2
Lentil McCloskey 2
Make Way for Ducklings McCloskey 2
Memoirs of a Geisha Golden 4
Message in a Bottle Sparks 2
One Morning in Maine McCloskey 1
Snow Falling on Cedars Guterson 2
The Cat in the Hat Seuss 1
The Notebook Sparks 3
Add a ‘‘Total’’ label in cell B27 and a SUM function in cell D27 to add up
the quantity of books on hand. Change the name of Sheet1 to ‘‘15% Discount.’’
Create a copy of this worksheet and place it before Sheet2. Change the name of
the newly created worksheet to ‘‘20% Discount.’’ Change the discount in cell
F1 of this worksheet to 20 %. Save the file as ch2-05_student_name (replacing
student_name with your name).
a. Print the completed 15% Discount worksheet in Value view, with
your name and date printed in the lower left footer and file name in
the lower right footer.
b. Print the completed 20% Discount worksheet in Value view, with
your name and date printed in the lower left footer and file name in
the lower right footer.
c. Print the completed 15% Discount worksheet in Formula view, with
gridlines and row and column headings, landscape orientation, scaling
to fit to 1 page wide by 1 page tall, and with your name and date
printed in the lower left footer and file name in the lower right footer.
d. Print the completed 20% Discount worksheet in Formula view, with
gridlines and row and column headings, landscape orientation, scaling
to fit to 1 page wide by 1 page tall, and with your name and date
printed in the lower left footer and file name in the lower right footer.
Chapter 2 Case Problem 2:
WINE DEPOT
The Wine Depot, located in Santa Barbara, California, imports and sells highquality
wine from around the world. Owner Barbara Fairfield is curious to see
how Excel might help her manage the business and account for its inventory.
Her husband, Bud, is the accountant in the family but hasn’t had much experience
with Excel. He’s hired you to help him construct several worksheets to
help the business better understand the financial and managerial aspects of the
company.
To begin, you decide to create a worksheet of some of Barbara’s wine inventory.
Start with worksheet ch2-06. Format the company name and worksheet
title bold and italics. Format the title of each column of data bold with a bottom
border. Format all values for the column Price as Accounting Number Format. Add two column labels, Quantity and Value, to the right of the existing data
and then insert quantity amounts as shown below [Note: SKU (Stock Keeping
Unit) is a unique identifier for each of the distinct products and services that
can be ordered from a supplier.]:
SKU Quantity
17521 4
16716 10
16528 12
16739 5
15347 7
17539 3
11599 1
14539 5
17840 12
13883 12
15966 24
17454 24
17024 10
16554 8
17425 7
17549 3
17578 2
17275 1
Insert a formula to calculate value as quantity times price in cell I9, then filldown
this formula for all cells in the table. Insert a text label ‘‘Total’’ in cell H28
and a formula to calculate the total value in cell I28. Insert two additional rows
after row 27. Add two American Syrah wines in those two rows: Carhatt, $34
(750 ml, year 2000, SKU 16769), quantity 10; and Cafaro, $35 (750 ml, year
2000, SKU 16874), quantity 15. Delete the row containing the American Merlot
from Wildhorse. Change the formatting of the Price and Value columns to
include no decimals. After using Excel’s Help function to learn how to sort items
in a worksheet, sort the information on your worksheet by location and then by
type, in ascending order. Change the name of Sheet1 to ‘‘Pricing.’’ Create a copy
of this worksheet and place it before Sheet2, then change the name of this sheet
to ‘‘Cost.’’ Add a label at cell C4 of the Cost sheet called Cost % in Bold, Italics.
Type 60% as the cost percentage in cell D4 of the Cost sheet. Change the name
of the column ‘‘Value’’ to ‘‘Cost.’’ Create a new formula in cell I9 to calculate
cost as Price times Cost % times Quantity. (Be sure to use absolute or relative
references where appropriate.) Fill the formula in cell I9 down to all items. Save
the file as ch2-06_student_name (replacing student_name with your name).
a. Print the completed Pricing worksheet in Value view, with portrait orientation,
scaling to fit to 1 page wide by 1 page tall, and with your
name and date printed in the lower left footer and file name in the
lower right footer.
b. Print the completed Cost worksheet in Value view, with portrait orientation,
scaling to fit to 1 page wide by 1 page tall, and with your
name and date printed in the lower left footer and file name in the
lower right footer. Print the completed Pricing worksheet in Formula view, with gridlines
and row and column headings, portrait orientation, scaling to fit to 1
page wide by 1 page tall, and with your name and date printed in the
lower left footer and file name in the lower right footer.
d. Print the completed Cost worksheet in Formula view, with gridlines
and row and column headings, portrait orientation, scaling to fit to 1
page wide by 1 page tall, and with your name and date printed in the
lower left footer and file name in the lower right footer.
Chapter 2 Case Problem 3:
SNICK’S BOARD SHOP
Snick’s Board Shop is located in La Jolla, California, and specializes in complete
and longboard skateboards and equipment. Owner Casey ‘‘Snick’’ Miller
is curious to see how Excel might help him manage the business and account
for its inventory. His girlfriend Caitlin handles the financial aspects of the business
but hasn’t had much experience with Excel. They have hired you to help
him construct several worksheets to help the business better understand the financial
and managerial aspects of the company.
To begin, you decide to create a worksheet of Snick’s inventory. Start with
worksheet ch2-07 and make the following changes:
1 Format the company name and worksheet title bold and italics.
2 Format the title of each column (located in row 5) bold with a bottom
border.
3 Format all values for the column Price as Accounting Number Format.
4 Resize column C to 250 pixels and column D to 925 pixels.
5 Use Excel’s Help feature to learn how to wrap text in a cell. Format all
cells containing data in column C and D to wrap text.
6 Add one column (titled Quantity) to the right of column Product # and
then type 10 as the quantity for all items. Add an additional column (titled
Retail Value) to the right of column Quantity. Format both column
titles as all other column titles.
7 Insert a formula to calculate the retail value as quantity times price in cell
I6, then fill down this formula for all cells in the table.
8 Insert a text label ‘‘Total’’ in cell H19 and a formula to calculate the total
retail value of all products in cell I19.
9 Format cell I19 with a top and bottom double border.
10 Insert two additional rows after row 9. 11 Add two products in rows 10 and 11 as follows.
Category Ramp Ramp
Manufacturer Element Mojo
Product Name Element Launch Ramp Mojo Wedge Ramp
Description Blast over any and everything in
your path. 1600 tall _ 4600 long _ 19
3/400 wide. Folds to 2300 long _ 19
3/400 wide _ 600 high. Steel kick plate
for smooth transition. Rubber
capped feet to avoid slippage.
Durable, weatherproof, and powdercoated.
Made from partially recycled
materials. 2 reflective Element logo
stickers for night visibility. Connects
easily to other Element Drop Spot
obstacles. Imported.
Skate the unskateable or
simply pop your tricks
higher and further. 1100
tall. Imported.
Price 179.99 179.99
Style Black/Red Black/Blue
Product # 65-01837 65-00011
Quantity 5 8
12 Change the name of Sheet1 to ‘‘Retail Value.’’
13 Create a copy of this worksheet and place it before Sheet2, then change
the name of this sheet to ‘‘Cost.’’
14 Add a label at cell F2 of the Cost sheet called Cost % in Bold, Italics.
15 Type 45% as the cost percentage in cell G2 of the Cost sheet. Change
the name of the column ‘‘Retail Value’’ to ‘‘Cost.’’ Center the Cost title.
16 Create a new formula in cell I6 to calculate cost as Price times Cost %
times Quantity. (Be sure to use absolute or relative references where
appropriate.) Fill the formula in cell I6 down to all items.
17 Save the file as ch2-07_student_name (replacing student_name with your
name).
18 Print the completed Pricing worksheet in Value view, with landscape orientation,
scaling to fit to 1 page wide by 1 page tall, and with your name
and date printed in the lower left footer and file name in the lower right
footer.
19 Print the completed Cost worksheet in Value view, with landscape orientation,
scaling to fit to 1 page wide by 1 page tall, and with your name
and date printed in the lower left footer and file name in the lower right
footer.
20 Print the completed Pricing worksheet in Formula view, with gridlines
and row and column headings, landscape orientation, scaling to fit to1 page wide by 1 page tall, and with your name and date printed in the
lower left footer and file name in the lower right footer. Resize column
D to 330 pixels and all other cells so that formulas are viewable. Don’t
save these changes.
21 Print the completed Cost worksheet in Formula view, with gridlines and
row and column headings, landscape orientation, scaling to fit to 1 page
wide by 1 page tall, and with your name and date printed in the lower
left footer and file name in the lower right footer. Resize column D to
330 pixels and all other cells so that formulas are viewable. Don’t save
these changes.
Chapter 2 Case Problem 4:
ROSEY’S ROSES
Rosey’s Roses is located in Savannah, Georgia, and specializes in the sale of
roses. You, as the manager of the business, are curious to see how Excel might
help you manage the business and account for its inventory. To that end, you
have hired a local college student to construct several worksheets to help the
business better understand the financial and managerial aspects of the company.
To begin, the two of you decide to create a worksheet of Rosey’s inventory.
Start with worksheet ch2-08 and make the following changes:
1 Format the company name and worksheet title bold and italics.
2 Move the company name and worksheet title from column C to column A.
3 Format the title of each column (located in row 4) bold with a bottom
border.
4 Add a column (titled Quantity) to the right of column Description.
5 Format the Quantity title aligned centered, bold with a bottom border.
6 Format the values under the Quantity title as comma style with no decimals.
7 Resize column C to 95 pixels.
8 Add an additional column (titled Cost/Unit) to the right of column Quantity.
9 Format the Cost/Unit title aligned centered, bold with a bottom border.
10 Format the values under the Cost/Unit title as currency.
11 Resize column D to 115 pixels.
12 Add an additional column (titled Cost) to the right of column Cost/Unit.
13 Format the Cost title aligned centered, bold with a bottom border. 14 Format the values under the Cost title as currency.
15 Resize column E to 115 pixels.
16 Enter the following values for Quantity and Cost/unit
Type Description Quantity Cost/Unit
Shrub Abraham Darby #5 25 $39.99
Shrub Be My Baby #5 30 $18.99
Shrub Deja Blu #5 18 $25.99
Shrub Koko Loko #7 17 $17.99
Shrub Peach Drift #10 33 $12.99
Shrub Red Drift #10 15 $ 6.99
Shrub Sedona #5 3 $14.99
Shrub Sweet Intoxication #5 17 $ 9.99
Climber Climbing Orange Crush #7 16 $32.99
Climber Don Juan Climber #5 25 $37.99
Tree Barbara Streisand 36in Tree 50 $52.99
Tree Firefighter 36in Tree 14 $55.99
Tree Trumpeter 36in Tree 4 $65.99
17 Insert a formula to calculate the cost as quantity times cost/unit in cell E5.
18 Format this cell aligned right.
19 Fill down this formula for all cells in the table.
20 Insert a row below the last shrub listed.
21 Add one additional item into your newly added row with a type ‘‘Shrub,’’
description ‘‘Wing Ding #5,’’ quantity ‘‘30,’’ and cost/unit ‘‘11.99.’’
22 Type Total in cell A20.
23 Insert a formula to calculate the total cost of all roses into cell E20.
24 Format cell E20 to align right with a top and double bottom border.
25 Save the file as ch2-08_student_name (replacing student_name with your
name).
26 Print the completed worksheet in Value view, with portrait orientation
with your name and date printed in the lower left footer and file name in
the lower right footer.
CHAPTER 3
FINANCIAL STATEMENT ANALYSIS
Chapter 3 Questions
1 What is another term for vertical analysis?
2 What base amount is used for vertical analysis of an income statement?
3 What base amount is used for vertical analysis of a balance sheet?
4 What base amount is used for horizontal analysis of both income statements
and balance sheets? Give an example.
5 What do profitability ratios represent?
6 What do liquidity ratios represent?
7 What do solvency ratios represent?
8 Why is the point-and-click method of cell referencing easier than typing
references directly into worksheet cells?
9 Which ratios are usually formatted as percentages?
10 Which ratios are usually formatted as numbers?
Chapter 3 Assignments
1 Create a Financial Analysis for What SUP, Inc. as of December 31, 2016.
You are to create this financial analysis as of December 31, 2016, from
the information found on file ch3-02.xls. This file contains three worksheets,
which are labeled Income Statement, Balance Sheet, and Ratios.
Following the Chapter 3 examples, create a vertical analysis of both the
income statement and balance sheet for December 31, 2016, only. Then
create a horizontal analysis of both the income statement and balance
sheet. (Note: The horizontal analysis will compare December 31, 2015,
with December 31, 2016.) In addition, create a pie chart of expenses
for the year ended December 31, 2016; a column chart (in a format of
your choice) of expenses for the years ended December 31, 2015, and
2016; and a ratio analysis as of December 31, 2016. Save the file as
ch3-02_student_name.xls (replacing student_name with your name).
a. Print each completed worksheet in Value view, with your name and date
printed in the lower left footer and the file name in the lower right footer.
b. Print each completed worksheet in Formula view, with your name
and date printed in the lower left footer and the file name in the
lower right footer.
32 Create a Financial Analysis for What SUP, Inc. as of December 31,
2017.
You are to create What SUP’s financial analysis as of December 31, 2017,
from the information found on file ch3-03.xls. This file contains three
worksheets, which are labeled Income Statement, Balance Sheet, and
Ratios.
Following the Chapter 3 examples, create a vertical analysis of both the
income statement and balance sheet for December 31, 2017, only. Then
create a horizontal analysis of both the income statement and balance
sheet. (Note: The horizontal analysis will compare December 31, 2016,
with December 31, 2017.) In addition, create a pie chart of expenses
for the year ended December 31, 2016; a column chart (in a format of
your choice) of expenses for the years ended December 31, 2016, and
2017; and a ratio analysis as of December 31, 2018. Save the file as
ch3-03_student_name.xls (replacing student_name with your name).
a. Print each completed worksheet in Value view, with your name and
date printed in the lower left footer and the file name in the lower
right footer.
b. Print each completed worksheet in Formula view, with your name
and date printed in the lower left footer and the file name in the
lower right footer.
Chapter 3 Case Problem 1:
KELLY’S BOUTIQUE
You are to create Kelly’s Boutique’s financial analyses as of December 31,
2014, and as of December 31, 2015. Following the Chapter 3 examples, use the
student file ch3-04.xls to create a vertical and horizontal analysis of the balance
sheet and income statement in the columns provided. (Note: The horizontal
analysis will compare December 31, 2014, with December 31, 2015.) Also create
a pie chart of expenses for the year ended December 31, 2015; a column
chart of expenses for the years ended December 31, 2014, and December 31,
2015; and a ratio analysis as of December 31, 2015. Save the file as
ch3-04_student_name.xls (replacing student_name with your name).
a. Print each completed worksheet in Value view, with your name and
date printed in the lower left footer and the file name in the lower
right footer.
b. Print each completed worksheet in Formula view, with gridlines and
row and column headings, portrait orientation, scaling to fit to 1 page
wide by 1 page tall, and with your name and date printed in the lower
left footer and the file name in the lower right footer.
Chapter 3 Case Problem 2:
WINE DEPOT
You are to create Wine Depot’s financial analyses as of December 31, 2015,
and as of December 31, 2016. Following the Chapter 3 examples, use the student
file ch3-05.xls to create a vertical and horizontal analysis of the balance
sheet and income statement in the columns provided. (Note: The horizontal
analysis will compare December 31, 2015 with December 31, 2016.) Also create
a pie chart of expenses for the year ended December 31, 2016; a column
chart of expenses for the years ended December 31, 2015, and December 31,
2016; and a ratio analysis as of December 31, 2016. Save the file as
ch3-05_student_name.xls (replacing student_name with your name). Note: You
will have to use Excel’s help feature to create the column chart, because the
columns are not adjacent to one another as in the chapter example. Use whatever
chart layout you prefer.
a. Print each completed worksheet in Value view, with landscape orientation,
scaling to fit to 1 page wide by 1 page tall, and with your
name and date printed in the lower left footer and the file name in the
lower right footer. Use Excel’s help feature to move charts on your
worksheet so that they print on a separate page.
b. Print each completed worksheet in Formula view, with gridlines and
row and column headings, landscape orientation, scaling to fit to 1
page wide by 1 page tall, and with your name and date printed in the
lower left footer and the file name in the lower right footer.
Chapter 3 Case Problem 3:
SNICK’S BOARD SHOP
You are to create a financial analysis for Snick’s Board Shop as of December
31, 2011, and December 31, 2012. Following the Chapter 3 examples, use the
student file ch3-06.xls to create a vertical and horizontal analysis of the balance
sheet and income statement in the columns provided. (Note: The horizontal
analysis will compare December 31, 2011, with December 31, 2012.) Also create
a pie chart of expenses for the year ended December 31, 2012, formatted in
a manner similar to your chapter work; a column chart of expenses for the years
ended December 31, 2011, and December 31, 2012, formatted in a manner similar
to your chapter work; and a ratio analysis as of December 31, 2012. Save
the file as ch3-06_student_name.xls (replacing student_name with your name).
Note: You will have to use Excel’s help feature to create the column chart,
because the columns are not adjacent to one another as in the chapter example.
Use whatever chart layout you prefer.
a. Print each completed worksheet in Value view, with landscape orientation,
scaling to fit to 1 page wide by 1 page tall, and with your
name and date printed in the lower left footer and the file name in the
lower right footer. Print each completed worksheet in Formula view, with gridlines and
row and column headings, landscape orientation, scaling to fit to 1
page wide by 1 page tall, and with your name and date printed in the
lower left footer and the file name in the lower right footer.
Chapter 3 Case Problem 4:
ROSEY’S ROSES
You are to create a financial analysis for Rosey’s Roses as of December 31,
2013, and December 31, 2014. Following the Chapter 3 examples, use the student
file ch3-07.xls to create a vertical analysis of the balance sheet and income
statement as of December 31, 2013, and December 31, 2014—as well as a horizontal
analysis of both the income statement and balance sheet comparing
December 31, 2013, with December 31, 2014. Place the vertical analysis in
columns D and G on the income statement labeled % of Sales in cells D5 and
G5. Place the vertical analysis in columns D and G on the balance sheet labeled
% of Assets in cells D5 and G5. Place the horizontal analysis in column I
labeled % Change in cell I5 for both the income statement and balance sheet.
Also create a pie chart of expenses for the year ended December 31, 2014,
formatted in a manner similar to your chapter work; a column chart of expenses
for the years ended December 31, 2013, and December 31, 2014, formatted in a
manner similar to your chapter work; and a ratio analysis as of December 31,
2014. Note: You will have to use Excel’s help feature to create the column
chart, because the columns are not adjacent to one another as in the chapter
example. Use whatever chart layout you prefer.
Save the workbook as ch3-07_student_name (replacing student_name with
your name). Print all worksheets in Value view, with your name and date
printed in the lower left footer and the file name in the lower right footer
CHAPTER 4 DEPRICIATION
Chapter 4 Questions
1 What function is built-into Excel to help you calculate straight-line
depreciation?
2 What arguments does the SLN function take?
3 Should the arguments in the SLN function be values or references?
4 Why should the arguments in the SLN function be values or references?
5 Should references used as arguments to the SLN function be absolute or
relative?
6 What is the purpose of a depreciation summary?
7 What other depreciation functions are built into Excel?
8 What additional argument is added for these depreciation functions?
9 How do you add a new series of data to an existing chart?
10 Describe the shape of the three lines in a depreciation comparison chart
depicting straight-line, double declining balance, and sum-of-the-year’s
digits depreciation.
Chapter 4 Assignments
1 Modify What SUP’s Straight-Line Depreciation Schedule
You are to add another depreciation schedule for What SUP. Use the
ch4-01_SLN_student_name.xls worksheet you created in this chapter. Add
a new depreciation worksheet, labeled Asset 1003, using the Move or
Copy Shortcut menu. The asset to be depreciated is a forge purchased on
January 1, 2015, for $65,000. It has an estimated salvage value of $6,150
and is to be depreciated over 4 years using the straight-line method.
Update the summary worksheet to include information on this new asset.
(Be careful to note the date this asset was acquired.) Save the file as
ch4-02_SLN_student_name (replacing student_name with your name).
a. Print the newly completed worksheet for Asset 1003 and the modified
Summary worksheet in Value view, with your name and date printed
in the lower left footer and the file name in the lower right footer.
b. Print each worksheet from part a (above) in Formula view, with your
name and date printed in the lower left footer and the file name in
the lower right footer.
2 ModifyWhat SUP’s Double Declining Balance Depreciation Schedule
You are to add another depreciation schedule for What SUP. Use the
ch4-01_DDB_student_name worksheet you created in this chapter. Add a
new depreciation worksheet, labeled Asset 1003, using the Move or
Copy Shortcut menu. The asset to be depreciated is the forge purchased
on January 1, 2015, for $65,000 (again, its estimated salvage value is
$6,150). The forge is to be depreciated (over 4 years) using the double
declining balance method. Update the summary worksheet to include
information on this asset. (Be careful to note the date this asset was
acquired.) Save the file as ch4-02_DDB_student_name (replacing
student_name with your name).
a. Print the newly completed worksheet for Asset 1003 and the modified
Summary worksheet in Value view, with your name and date printed in
the lower left footer and the file name in the lower right footer.
b. Print each worksheet from part a (above) in Formula view, with your
name and date printed in the lower left footer and the file name in
the lower right footer.
3 Modify What SUP’s Sum-of-the-Year’s Digits Depreciation Schedule
You are to add another depreciation schedule for What SUP. Use the
ch4-01_SYD_student_name worksheet you created in this chapter. Add a
new depreciation worksheet, labeled Asset 1003, using the Move or
Copy Shortcut menu. The asset to be depreciated is the forge purchased
on January 1, 2015, for $65,000 (again, its estimated salvage value is
$6,150). The forge is to be depreciated (over 4 years) using the sumof-
the-year’s digits method. Update the summary worksheet to include
information on this asset. (Be careful to note the date this asset was
acquired.) Save the file as ch4-02_SYD_student_name (replacing
student_name with your name).
a. Print the newly completed worksheet for Asset 1003 and the modified
Summary worksheet in Value view, with your name and date
printed in the lower left footer and the file name in the lower right
footer.
b. Print each worksheet from part a (above) in Formula view, with your
name and date printed in the lower left footer and the file name in
the lower right footer.
4 What SUP’s Depreciation Comparison Chart for Asset 1002
You are to add another depreciation chart for What SUP. Use the
ch4-01_Chart_student_name workbook you created in this chapter. Add
a new depreciation chart by changing the name of Sheet2 to Asset 1002.
Following the steps shown in the chapter, create a new chart comparing
straight-line, double declining balance, and sum-of-the-year’s digitdepreciation amounts for Asset 1002. Save the workbook as ch4-02_Asset
1002_Chart_student_name (replacing student_name with your name). Print
then newly created chart.
5 What SUP’s Depreciation Comparison Chart for Asset 1003
You are to create a depreciation chart for What SUP for Asset 1003. After
completing assignments 1, 2, and 3, and following the steps shown in the
chapter, create a new chart comparing straight-line, double declining balance,
and sum-of-the-year’s digits depreciation amount for Asset 1003.
Save the workbook as ch4-02_Asset 1003_Chart_student_name (replacing
student_name with your name). Then print the newly created chart.
Chapter 4 Case Problem 1:
KELLY’S BOUTIQUE
Kelly’s Boutique owned the following fixed assets as of December 31, 2015:
Description
Asset # 101 102 103
Asset Building Computer System Phone System
Date acquired 1/1/12 1/1/14 1/1/14
Cost $700,000 $75,000 $96,500
Salvage value $22,000 $2,000 $6,000
Estimated useful life 25 years 4 years 5 years
You are to create a fixed asset depreciation summary and individual depreciation
worksheets for Kelly’s Boutique using the straight-line depreciation
method based on the information tabulated above. Be sure to pay close attention
to the date of purchase for each asset so that your summary sheet is correct.
The summary sheet need only include depreciation from 2012 through 2015.
Individual assets must show depreciation over their entire useful life. Follow the
text examples for formatting. Label your worksheets as follows: Summary SL,
Asset 101 SL, Asset 102 SL, and Asset 103 SL.
In the same workbook, create Kelly’s Boutique’s fixed asset depreciation
summary and individual depreciation worksheets using the double declining balance
method and based on the tabulated information. Again, pay close attention
to the date of purchase for each asset so that your summary sheet is correct.
Label your worksheets: Summary DDB, Asset 101 DDB, Asset 102 DDB, and
Asset 103 DDB.
In the same workbook, next create Kelly’s Boutique’s fixed asset depreciation
summary and individual depreciation worksheets using the sum-of-theyear’s
digits method and based on the tabulated information. Pay close attention
to the date of purchase for each asset so that your summary sheet is correct.
Label your worksheets: Summary SYD, Asset 101 SYD, Asset 102 SYD, and
Asset 103 SYD.
In the same workbook, you should now create a chart of Asset 101’s depreciation
(over its 25-year estimated useful life) that compares the straight-line, double declining balance, and sum-of-the-year’s digits methods of calculating
depreciation. Label this worksheet: Chart. Choose any 2D line chart and chart
layout that you like.
Save the workbook as ch4-03_student_name (replacing student_name with
your name). Print all worksheets in Value view, with your name and date
printed in the lower left footer and the file name in the lower right footer.
Chapter 4 Case Problem 2:
WINE DEPOT
The Wine Depot owned the following fixed assets as of December 31, 2015:
Description
Asset # 101 102 103
Asset Equipment Truck Portable
Date acquired 1/1/12 1/1/13 1/1/14
Cost $925,000 $150,500 $200,000
Salvage value $25,000 $4,500 $12,000
Estimated useful life 5 years 10 years 15 years
You are to create Wine Depot’s fixed asset depreciation summary and individual
depreciation worksheets using the straight-line depreciation method and
based on the information in the preceding table. Be sure to pay close attention
to the date of purchase for each asset so that your summary sheet is correct.
The summary sheet need only include depreciation from 2012 through 2015.
Individual assets must show depreciation over their entire useful life. Follow the
text examples for formatting. Label your worksheets as follows: Summary SL,
Asset 101 SL, Asset 102 SL, and Asset 103 SL.
In the same workbook, create Wine Depot’s fixed asset depreciation summary
and individual depreciation worksheets using the double declining balance
method and based on the same tabulated information. Again, pay close attention
to the date of purchase for each asset so that your summary sheet is correct.
Label your worksheets: Summary DDB, Asset 101 DDB, Asset 102 DDB, and
Asset 103 DDB.
In the same workbook, next create Wine Depot’s fixed asset depreciation
summary and individual depreciation worksheets using the sum-of-the-year’s
digits method and based on the tabulated information. Be sure to pay close
attention to the date of purchase for each asset so that your summary sheet is
correct. Label your worksheets: Summary SYD, Asset 101 SYD, Asset 102
SYD, and Asset 103 SYD.
In the same workbook, you should now create a chart of Asset 103’s depreciation
(over its 15-year estimated useful life) that compares the straight-line, double
declining balance, and sum-of-the-year’s digits methods of calculating
depreciation. Label this worksheet: Chart. Choose any 2D line chart and chart
layout that you like.
Save the workbook as ch4-04_student_name (replacing student_name with
your name). Print all worksheets in Value view, with your name and date
printed in the lower left footer and the file name in the lower right footer
. Chapter 4 Case Problem 3:
SNICK’S BOARD SHOP
Snick’s Board Shop owned the fixed assets shown below as of December 31,
2012:
Asset # Shelving Cash Register Computer
Date acquired 1/1/11 1/1/11 1/1/11
Cost $2,000 $7,000 $1,900
Salvage value $0 $500 $400
Estimated useful life 10 years 8 years 3 years
You are to create Snick’s fixed asset depreciation worksheets using the
straight-line depreciation method and based on the information in the preceding
table. No summary sheet is required. Individual assets must show depreciation
over their entire useful life. Follow the text examples for formatting. Label each
worksheet Shelving SL, Cash Register SL, and Computer SL.
In the same workbook, create Snick’s fixed asset depreciation worksheets
using the double declining balance method and based on the same table. No
summary sheet is required. Individual assets must show depreciation over their
entire useful life. Follow the text examples for formatting. Label each worksheet
Shelving DDB, Cash Register DDB, and Computer DDB.
In the same workbook, create Snick’s fixed asset depreciation worksheets
using the sum-of-the-year’s-digits method and based on the same table. No
summary sheet is required. Individual assets must show depreciation over their
entire useful life. Follow the text examples for formatting. Label each worksheet
Shelving SYD, Cash Register SYD, and Computer SYD.
In the same workbook, you should now create a chart of each asset’s depreciation
that compares the straight-line, double declining balance, and sum-ofthe-
year’s digits methods of calculating depreciation. Label each worksheet
Shelving Chart, Cash Register Chart, and Computer Chart. Choose any line
chart and chart layout that you like.
Save the workbook as ch4-05_student_name (replacing student_name with
your name). Print all worksheets in Value view, with your name and date
printed in the lower left footer and the file name in the lower right footer.
Chapter 4 Case Problem 4:
ROSEY’S ROSES
Rosey’s Roses owned the fixed assets shown below as of December 31, 2014:
Asset Building #1 Building #2 Equipment
Date acquired 1/1/13 1/1/14 1/1/13
Cost $55,000 $20,000 $7,500
Salvage value $4,000 $1,000 $500
Estimated useful life 15 years 5 years 4 years
This assignment has no beginning student file. You are to create Rosey’s fixed
asset depreciation worksheets from a blank worksheet using the straight-linedepreciation method and based on the information in the preceding table. No
summary sheet is required. Individual assets must show depreciation over their
entire useful life. Follow the text examples for formatting. Label each worksheet
Building #1 SL, Building #2 SL, and Equipment SL.
In the same workbook, create Rosey’s fixed asset depreciation worksheets
using the double declining balance method and based on the same table. No
summary sheet is required. Individual assets must show depreciation over their
entire useful life. Follow the text examples for formatting. Label each worksheet
Building #1 DDB, Building #2 DDB, and Equipment DDB.
In the same workbook, create Rosey’s fixed asset depreciation worksheets
using the sum-of-the-year’s-digits method and based on the same table. No
summary sheet is required. Individual assets must show depreciation over their
entire useful life. Follow the text examples for formatting. Label each worksheet
Building #1 SYD, Building #2 SYD, and Equipment SYD.
In the same workbook, you should now create a chart of each asset’s depreciation
that compares the straight-line, double declining balance, and sum-ofthe-
year’s-digits methods of calculating depreciation. Label each worksheet
Building #1 Chart, Building #2 Chart, and Equipment Chart. Choose any line
chart and chart layout that you like.
Save the workbook as ch4-06_student_name (replacing student_name with
your name). Print all worksheets in Value view, with your name and date
printed in the lower left footer and the file name in the lower right footer.
CHAPTER 5 Loan and Bond
Amortization
Chapter 5 Questions
1 Explain the difference between a loan and a bond.
2 What function is used to calculate a loan payment?
3 What arguments does the loan payment function take?
4 Why does the payment function result in a negative number?
5 What does a loan amortization schedule do?
6 Describe the formula for computing interest expense each period for a loan.
7 What function is used to calculate bond proceeds, and what arguments
does that function take?
8 Describe the formula computing interest expense each period for a bond.
9 Describe the goal seek process.
10 Describe the Scenario Manager.
Chapter 5 Assignments
1 Create a new What SUP Loan Amortization Schedule
Using the ch5-03 file to start your work, create a worksheet (similar to
the one created in this chapter) that calculates the required annual payment
for a $945,500, 7-year loan at 4 percent and includes a loan amortization
schedule. (Be sure to use names in the worksheet, as illustrated in
the chapter. You will also need to format the cells.) Save your file as
ch5-03_student_name (replacing student_name with your name).
a. Print the newly completed worksheet in Value view, with your name
and date printed in the lower left footer and the file name in the
lower right footer.
b. Print the worksheet from part a (above) in Formula view, with your
name and date printed in the lower left footer and the file name in
the lower right footer.
c. Use the Scenario Manager to create a scenario using rates of 10, 9,
7, and 6 percent to calculate alternative payments. Print the resulting
summary worksheet.
d. Use Excel’s goal seek feature to calculate the amount the company
could borrow if it could afford a payment of $100,000 per year assuming
the original loan assumptions. Print the resulting worksheet in Value
view, with your name and date printed in the lower left footer and the
file name in the lower right footer.
2 Create a new What SUP Bond Amortization Schedule
Using the ch5-04 file to start your work, create a worksheet (similar to
the one created in this chapter) that calculates the bond proceeds for a
$1,500,000, 10-year, 3 percent stated interest bond issued when the market
interest rate is 2.8 percent. Include a bond amortization schedule and
use names in the worksheet, as illustrated in the chapter (You will also
need to format the cells.) Save your file as ch5-04_student_name
(replacing student_name with your name).
a. Print the newly completed worksheet in Value view, with your name
and date printed in the lower left footer and the file name in the
lower right footer.
b. Print the worksheet from part a (above) in Formula view, with your
name and date printed in the lower left footer and the file name in
the lower right footer.
c. Use the Scenario Manager to create a scenario using stated rates of
2, 2.5, 3.1, and 3.5 percent to calculate alternative annual interest
payments and bond proceeds. Print the resulting summary worksheet.
d. Use Excel’s goal seek feature to calculate the stated interest rate it
would have to pay under the original bond analysis (in part a) to
achieve bond proceeds of $1,450,000. Print the resulting worksheet
in Value view, with your name and date printed in the lower left
footer and the file name in the lower right footer.
Chapter 5 Case Problem 1:
KELLY’S BOUTIQUE
Kelly’s Boutique is contemplating several alternative means of financing an
expansion. One alternative is to borrow $300,000 from a local bank; another alternative
is to borrow this amount from investors by issuing bonds. Both alternatives
involve a 5-year debt period. Modify the workbook file ch5-05 to
compute a loan and bond analysis. Name and format cell ranges as appropriate.
Assume an initial loan rate of 3 percent, an initial bond stated rate of 3 percent,
and a market interest rate of 2.9 percent.
a. Print the newly completed loan and bond worksheets in Value view,
with your name and date printed in the lower left footer and the file
name in the lower right footer.
b. Print the worksheets from part a (above) in Formula view, with your
name and date printed in the lower left footer and the file name in the
lower right footer.
c. Use the Scenario Manager to create two loan scenarios called Best
Case and Worst Case. In the Best Case, the rate would be 2.8 percent
and the loan amount would be $325,000; in the Worst Case, the
rate would be 3.2 percent and the loan amount would be $280,000. (Hint: You’ll need to place two cell references, separated by a comma,
in the Changing cells: text box.) The resulting comparison values
you’re trying to predict are Payment, Total Payments, and Total Interest.
Print the resulting summary worksheet.
d. Use the Scenario Manager to create two bond scenarios called Best
Case and Worst Case. In the Best Case, the market rate would be 2.5
percent and the stated rate would be 2 percent; in the Worst Case, the
market rate would be 3.2 percent and the stated rate would be 3.1 percent.
(Again, you’ll need to place two cell references, separated by a
comma, in the Changing cells: text box.) The resulting comparison
values you’re trying to predict are Proceeds, Total Interest Payments,
and Total Interest Expense. Print the resulting summary worksheet.
e. Use Excel’s goal seek feature to calculate the interest rate that the
company would have to negotiate under the original loan analysis (in
part a) to achieve a payment of $63,000. Round the interest rate to
two decimal places. Print the resulting worksheet in Value view, with
your name and date printed in the lower left footer and the file name
in the lower right footer.
f. Use Excel’s goal seek feature to calculate the market rate necessary to
achieve bond proceeds of $310,000. Round the interest rate to two
decimal places. Print the resulting worksheet in Value view, with your
name and date printed in the lower left footer and the file name in the
lower right footer.
Chapter 5 Case Problem 2:
WINE DEPOT
Wine Depot is considering several alternative means of financing an expansion.
One alternative is to borrow $300,000 from a local bank, but another alternative
is to borrow this amount from investors by issuing bonds. Both alternatives
involve a 3-year debt period with quarterly payments. Modify the workbook
ch5-06 to compute a loan and bond analysis, naming and formatting cell ranges
as appropriate. Assume an annual loan rate of 4 percent, an annual bond stated
rate of 4 percent, and a market annual interest rate of 3.5 percent.
a. Print the newly completed loan and bond worksheets in Value view,
with your name and date printed in the lower left footer and the file
name in the lower right footer.
b. Print the worksheets from part a, above, in Formula view, with your
name and date printed in the lower left footer and the file name in the
lower right footer.
c. Use the Scenario Manager to create two loan scenarios called Best
Case and Worst Case. In the Best Case, the rate would be 3 percent
and the loan amount would be $350,000; in the Worst Case, the
rate would be 5 percent and the loan amount would be $200,000. (Hint: You’ll need to place two cell references, separated by a comma,
in the Changing cells: text box.) The resulting comparison values
you’re trying to predict are Payment, Total Payments, and Total Interest.
Print the resulting summary worksheet.
d. Use the Scenario Manager to create two bond scenarios called Best
Case and Worst Case. In the Best Case, the market rate would be
3.75 percent and the stated rate would be 5 percent. In the Worst
Case, the market rate would be 4.5 percent and the stated rate would
be 3.25 percent. (Again, you’ll need to place two cell references, separated
by a comma, in the Changing cells: text box.) The resulting
comparison values you’re trying to predict are Proceeds, Total Interest
Payments, and Total Interest Expense. Print the resulting summary
worksheet.
e. Use Excel’s goal seek feature to calculate the interest rate the company
would have to negotiate under the original loan analysis (in part
a) to achieve a quarterly payment of $26,000. Round the interest rate
to two decimal places. Print the resulting worksheet in Value view,
with your name and date printed in the lower left footer and the file
name in the lower right footer.
f. Use Excel’s goal seek feature to calculate the market rate necessary to
achieve bond proceeds of $315,000. Round the interest rate to two
decimal places. Print the resulting worksheet in Value view, with your
name and date printed in the lower left footer and the file name in the
lower right footer.
Chapter 5 Case Problem 3:
SNICK’S BOARD SHOP
Snick’s Board Shop is considering several alternative means of financing an
expansion. One alternative is to borrow $400,000 from a local bank, but another
alternative is to borrow this amount from investors by issuing bonds. Both alternatives
involve a 2-year debt period with monthly payments. Modify the workbook
ch5–07 to compute a loan and bond analysis, naming and formatting cell
ranges as appropriate. Assume an initial loan rate of 8 percent, an initial bond
stated rate of 8 percent, and a market interest rate of 8.5 percent.
a. Print the newly completed loan and bond worksheets in Value view,
with your name and date printed in the lower left footer and the file
name in the lower right footer.
b. Print the worksheets from part a, above, in Formula view, with your
name and date printed in the lower left footer and the file name in the
lower right footer.
c. Use the Scenario Manager to create two loan scenarios called Best
Case and Worst Case. In the Best Case, the rate would be 7 percent
and the loan amount would be $350,000; in the Worst Case, the ratewould be 9 percent and the loan amount would be $450,000. (Hint:
You’ll need to place two cell references, separated by a comma, in the
Changing cells: text box.) The resulting comparison values you’re trying
to predict are Payment, Total Payments, and Total Interest. Print
the resulting summary worksheet.
d. Use the Scenario Manager to create two bond scenarios called Best
Case and Worst Case. In the Best Case, the market rate would be
7.75 percent and the stated rate would be 7 percent. In the Worst
Case, the market rate would be 8.75 percent and the stated rate would
be 8.25 percent. (Again, you’ll need to place two cell references, separated
by a comma, in the Changing cells: text box.) The resulting
comparison values you’re trying to predict are Proceeds, Total Interest
Payments, and Total Interest Expense. Print the resulting summary
worksheet.
e. Use Excel’s goal seek feature to calculate the interest rate that the
company would have to negotiate under the original loan analysis (in
part a) to achieve a monthly payment of $17,000. Round the interest
rate to two decimal places. Print the resulting worksheet in Value
view, with your name and date printed in the lower left footer and the
file name in the lower right footer.
f. Use Excel’s goal seek feature to calculate the market rate necessary to
achieve bond proceeds of $410,000. Round the interest rate to two
decimal places. Print the resulting worksheet in Value view, with your
name and date printed in the lower left footer and the file name in the
lower right footer.
Chapter 5 Case Problem 4:
ROSEY’S ROSES
Rosey’s Roses is considering several alternative means of financing an expansion.
One alternative is to borrow $600,000 from a local bank, but another alternative
is to borrow this amount from investors by issuing bonds. Both
alternatives involve a 10-year debt period with annual payments. Modify the
workbook ch5–08 to compute a loan and bond analysis, naming and formatting
cell ranges as appropriate. Assume an initial loan rate of 7 percent, an initial
bond stated rate of 7 percent, and a market interest rate of 6.5 percent.
a. Print the newly completed loan and bond worksheets in Value view,
with your name and date printed in the lower left footer and the file
name in the lower right footer.
b. Use the Scenario Manager to create two loan scenarios called Best Case
and Worst Case. In the Best Case, the rate would be 6 percent and
the loan amount would be $550,000; in the Worst Case, the rate would
be 8 percent and the loan amount would be $650,000. The resulting
comparison values you’re trying to predict are Payment, Total Payments,
and Total Interest. Print the resulting summary worksheet. c. Use the Scenario Manager to create two bond scenarios called Best
Case and Worst Case. In the Best Case, the market rate would be
6 percent and the stated rate would be 6.5 percent. In the Worst Case,
the market rate would be 8.25 percent and the stated rate would
be 8 percent. The resulting comparison values you’re trying to predict
are Proceeds, Total Interest Payments, and Total Interest Expense.
Print the resulting summary worksheet.
d. Use Excel’s goal seek feature to calculate the interest rate that the
company would have to negotiate under the original loan analysis (in
part a) to achieve a monthly payment of $80,000. Round the interest
rate to two decimal places. Print the resulting worksheet in Value
view, with your name and date printed in the lower left footer and the
file name in the lower right footer.
e. Use Excel’s goal seek feature to calculate the market rate necessary to
achieve bond proceeds of $650,000. Round the interest rate to two
decimal places. Print the resulting worksheet in Value view, with your
name and date printed in the lower left footer and the file name in the
lower right footer.
CHAPTER 6 Cash Budgeting
Chapter 6 Questions
1 Why should you locate budget assumptions in a special section of the
worksheet?
2 What information is provided in the operating cash receipts portion of
the cash budget?
3 Why does the typical cash budget spread collection of sales revenue over
more than one month?
4 What information is provided in the operating cash payments portion of
the cash budget?
5 What factors determine monthly purchases in the cash budget?
6 Why does the typical cash budget spread payment of purchases over
more than one month?
7 What information is provided in the investing activities portion of the
cash budget?
8 What information is provided in the financing activities portion of the
cash budget?
9 How is the goal seeking feature used in this chapter?
10 How is grouping used with the cash budget?
Chapter 6 Assignments
1 Create a new What SUP Operating Cash Receipts Budget
Using the ch6-02 file to start your work, create a worksheet similar to
the one created in this chapter to budget operating cash receipts by
month for 3 months. Place assumption information in the cells provided,
and define and use names extensively. The company expects to incur
repair hours of 600, 700, and 800 in January, February, and March
(respectively) at an expected hourly rate of $75. Product sales revenue of
$150,000, $165,000, and $175,000 is expected in January, February, and
March, together with interest revenue of $14,000, $13,000, and $17,000,
respectively. Professional service revenue and interest revenue are all collected
in the month earned. It is anticipated that 75 percent of product
sales revenue will be collected in the same month as earned, 20 percent
collected in the next month, and 5 percent in the following month. Product
sales revenue last November was $180,000 and last December was
$190,000. (Hint: Use these amounts in your budget to determine cash
collections in the current 3 months of January, February, and March as a
function of prior product sales. Use Excel’s grouping feature to group
‘‘revenues’’ rows together and to group the three monthly columns together.
Save your file as ch6-02_student_name (replacing student_name
with your name).
a. Print the newly completed worksheet in Value view, with your name
and date printed in the lower left footer and the file name in the
lower right footer.
b. Print the worksheet from part a, above, in Formula view, with your
name and date printed in the lower left footer and the file name in
the lower right footer. Print only the January cash budget, no
assumptions.
c. Collapse all rows and columns and then print the worksheet in Value
view, with your name and date printed in the lower left footer and
the file name in the lower right footer. Print only the total cash
budget, no assumptions.
d. Expand all rows and columns. Use what-if analysis to calculate operating
cash receipts if 85 percent of product sales revenue were collected
in the same month as earned, 10 percent in the next month,
and 5 percent in the following month. Print the resulting worksheet
in Value view, with your name and date printed in the lower left
footer and the file name in the lower right footer.
e. Reset the collection expectations to their original values (of 75, 20,
and 5 percent). Use goal seek to determine what number of repair
hours must be worked in January in order to achieve operating cash
receipts of $180,000 in January. Print the resulting cash budget, no
assumptions in Value view, with your name and date printed in the
lower left footer and the file name in the lower right footer.
2 Create a new What SUP Operating Cash Payments Budget
Using the ch6-03 file to start your work, create a worksheet similar to
the one created in this chapter to budget operating cash payments by
month for 3 months. Place assumption information in the cells provided,
and define and use names extensively. Product sales revenue of
$150,000, $165,000, $175,000, and $180,000 is expected in January,
February, March, and April respectively. Purchases cost 65 percent of
product sales. The company would like to maintain an ending inventory
equal to 60 percent of the next month’s cost of sales. At the beginning
of January, the company had $75,000 in inventory and $15,000 in
accounts payable; 85 percent of a month’s purchases are paid in the current
month, with the remaining paid in the following month. Selling
expenses are expected to be $6,000, $7,000, and $8,000 in January, February,
and March (respectively), while general and administrativeexpenses are expected to remain constant at $40,000 per month. All
expenses are paid in the month incurred. Use Excel’s grouping feature to
group purchases, payments for purchases, and expense rows together and
to group the three monthly columns together. Save your file as ch6-
03_student_name (replacing student_name with your name).
a. Print the newly completed worksheet in Value view, with your name
and date printed in the lower left footer and the file name in the
lower right footer.
b. Print the worksheet from part a, above, in Formula view, with your
name and date printed in the lower left footer and the file name in
the lower right footer. Print only the January cash budget, no
assumptions.
c. Collapse all rows and columns and then print the worksheet in Value
view, with your name and date printed in the lower left footer and
the file name in the lower right footer.
d. Expand all rows and columns. Use what-if analysis to calculate operating
cash payments if 70 percent of purchases are paid in the current
month and 30 percent are paid in the following month. Print the
resulting worksheet in Value view, with your name and date printed
in the lower left footer and the file name in the lower right footer.
e. Reset the payments expectations to the original 85 percent and
15 percent values. Use goal seek to determine what cost of expected
sales percentage would cause the operating cash payments to be
$500,000 for the quarter. Print the resulting worksheet in Value
view, with your name and date printed in the lower left footer and
the file name in the lower right footer.
Chapter 6 Case Problem 1:
KELLY’S BOUTIQUE
Kelly’s Boutique is contemplating several means of financing their acquisition
of $200,000 in special equipment. One alternative is to borrow $200,000 from a
local bank for 10 years at 12 percent per annum. The bank has asked them to
produce a 1-year cash budget broken down by months (January through December).
Sales of $40,000 are expected in the first month, with each month thereafter
increasing 2 percent. Purchases are based on an expected cost of sales of
55 percent and a required ending inventory of 70 percent of next month’s cost
of sales. Beginning inventory was $11,000. Sales for January next year are
expected to be $50,000. Sales in the previous November and December were
$29,000 and $28,000, respectively. Expenses include advertising expense of
$900, depreciation expense of $800, interest expense of $1,000, payroll expense
of $8,000, supplies expense of $500, and utilities expense of $600 per month
throughout the year. All expenses except depreciation are paid in the month
during which they are incurred. Collections in the month of sale are expected tobe 60 percent, collections in the first month following a sale 30 percent, and in
the second month 10 percent. Payments in the month of purchase are expected
to be 75 percent, payments in the first month following a purchase 15 percent,
and payments in the second month to be 10 percent. Purchases in the previous
November and December were $16,000 and $17,000, respectively. Proceeds
from the $200,000 loan are expected in June, and $200,000 of equipment will
be purchased in July. Monthly payments of $1,400 on the loan also begin in
July. The beginning cash balance in January was $15,000.
Using the ch6-04 file to start your work, create a cash budget (as you did in
the chapter) that is based on the assumptions listed in the previous paragraph.
Use Excel’s grouping feature to group operating cash receipts, operating cash
payment, cash from (to) operating activities, cash from (to) investing activities,
and cash from (to) financing activities and also to group the twelve monthly
columns together. Save your file as ch6-04_student_name (replacing student_
name with your name).
a. Print the newly completed worksheet in Value view, with your name
and date printed in the lower left footer and the file name in the lower
right footer.
b. Print the worksheet from part a, above, in Formula view, with your
name and date printed in the lower left footer and the file name in the
lower right footer. Print only columns A and B of the cash budget, no
assumptions.
c. Collapse rows to level 2 and columns to level 1, and then print the
worksheet in Value view with your name and date printed in the
lower left footer and the file name in the lower right footer. Print cash
budget only, no assumptions.
d. Expand rows to level 3 and columns to level 2, and then use what-if
analysis to calculate end-of-year cash if the sales growth each month
were 4 percent and payroll expense were $18,000 per month. Print the
resulting worksheet in Value view, with your name and date printed
in the lower left footer and the file name in the lower right footer.
Print cash budget only, no assumptions.
e. Undo the what-if analysis performed in part d. Use goal seek to determine
what sales growth would be needed to produce an ending cash
balance of $150,000. Print the resulting worksheet in Value view with
your name and date printed in the lower left footer and the file name
in the lower right footer. Print cash budget and assumptions.
Chapter 6 Case Problem 2:
WINE DEPOT
The Wine Depot is contemplating several alternative means of financing their
annual acquisition of $70,000 in equipment. One option is to borrow $300,000
from a local bank for 5 years at 11 percent per annum. The bank has asked
them to produce a 4-year cash budget broken down by year (2015 through2018). Sales of $750,000 are expected in 2015, with sales increasing each year
thereafter by 15 percent. Sales in 2014 were $600,000. Purchases are based on
an expected cost of sales of 45 percent and a required ending inventory of 10
percent of next year’s sales. Purchases in 2014 were $200,000, and beginning
inventory was $32,000. Annual expenses include advertising expense of
$10,000, marketing expense of $6,000, depreciation expense of $8,000, interest
expense of $35,000, salaries expense of $250,000, wages expense of $65,000,
supplies expense of $7,500, and utilities expense of $10,000. All expenses
except depreciation are paid in the year in which they are incurred and are
expected to increase 5 percent each year. Collections in the year of sale are
expected to be 92 percent, with the remaining 8 percent collected in the next
year. Payments in the year of purchase are expected to be 93 percent, with the
remaining 7 percent paid in the next year. Proceeds from the $300,000 loan are
expected in 2015, and $70,000 of facilities will be purchased each year. Proceeds
from expected equipment sales each year are expected to amount to
$10,000. Annual payments of $81,171 on the loan also begin in 2015. The beginning
cash balance in 2015 was $20,000.
Using the ch6-05 file to start your work, create a cash budget (as you did in
the chapter) based on the assumptions just provided. Use Excel’s grouping feature
to group operating cash receipts, operating cash payment, cash from (to)
operating activities, cash from (to) investing activities, and cash from (to)
financing activities. Save your file as ch6-05_student_name (replacing student_
name with your name).
a. Print the newly completed worksheet in Value view, with your name
and date printed in the lower left footer and the file name in the lower
right footer.
b. Print the worksheet from part a, above, in Formula view, with your
name and date printed in the lower left footer and the file name in the
lower right footer. Print only columns A, B, and C of the cash budget,
no assumptions.
c. Collapse rows to level 2; then print the worksheet in Value view, with
your name and date printed in the lower left footer and the file name
in the lower right footer. Print cash budget only, no assumptions.
d. Collapse rows to level 2, and then use what-if analysis to calculate
end-of-year cash if the sales growth each year were 10 percent. Print
the resulting worksheet in Value view, with your name and date
printed in the lower left footer and the file name in the lower right
footer. Print cash budget only, no assumptions.
e. Undo the what-if analysis performed in part d. Collapse rows to level
2, and then use goal seek to determine what annual sales growth
would be needed to produce an ending cash balance of $100,000 in
2018. Print the resulting worksheet in Value view, with your name
and date printed in the lower left footer and the file name in the lower
right footer. Print cash budget and assumptions.
Chapter 6 Case Problem 3:
SNICK’S BOARD SHOP
Snick’s Board Shop is contemplating several alternative means of financing
their acquisition of $350,000 in new equipment in year 1. One option is to borrow
$300,000 from a local bank. The bank has asked them to produce a 3-year
cash budget broken down by year (Year 1, 2, and 3). Sales in the prior year
were $240,000 and are expected to increase 10 percent each year. Purchases are
based on an expected cost of sales of 40 percent and a required ending inventory
of 25 percent of next year’s sales. Prior year expenses included advertising
expense of $2,500, depreciation expense of $1,000, wages expense of $56,000,
supplies expense of $450, and utilities expense of $1,600. All expenses except
depreciation are paid in the year in which they are incurred and are expected to
increase 8 percent each year. Interest expense is expected to remain constant at
$15,000 each year for years 1–3. Collections in the year of sale are expected to
be 90 percent, with the remaining 10 percent collected in the next year. Payments
in the year of purchase are expected to be 85 percent, with the remaining
15 percent paid in the next year. Proceeds from the $300,000 loan are expected
in year 1, and $350,000 of equipment will be purchased during year 1. In subsequent
years equipment purchases are expected to be $1,000 each year. Proceeds
from projected equipment sales each year are expected to amount to
$200. Annual payments of $110,000 on the loan also begin in year 1.
Using the ch6-06 file to start your work, create a cash budget (as you did in
the chapter) based on the assumptions just provided. Use Excel’s grouping
feature to group operating cash receipts, operating cash payment, cash from
(to) operating activities, cash from (to) investing activities, and cash from (to)
financing activities. Define names as appropriate.
Save your file as ch6-06_student_name (replacing student_name with your
name).
a. Print the newly completed worksheet in Value view, with your name
and date printed in the lower left footer and the file name in the lower
right footer. Print only columns A through E of the cash budget, no
assumptions.
b. Print the worksheet from part a, above, in Formula view, with your
name and date printed in the lower left footer and the file name in the
lower right footer. Print only columns A through D of the cash
budget, no assumptions.
c. Collapse rows to level 2, then print the worksheet in Value view, with
your name and date printed in the lower left footer and the file name
in the lower right footer. Print only columns A through E of the cash
budget, no assumptions.
d. Collapse rows to level 2, and then use what-if analysis to calculate
end-of-year cash if the sales growth each year were 25 percent. Print
the resulting worksheet in Value view, with your name and date
printed in the lower left footer and the file name in the lower rightfooter. Print only columns A through E of the cash budget, no
assumptions.
e. Undo the what-if analysis performed in part d. Collapse rows to level
2, and then use goal seek to determine what annual sales growth would
be needed to produce an ending cash balance of $0 in year 3. Print the
resulting worksheet in Value view, with your name and date printed in
the lower left footer and the file name in the lower right footer. Print
only columns A through E of the cash budget, no assumptions.
Chapter 6 Case Problem 4:
ROSEY’S ROSES
Rosey’s Roses is contemplating several alternative means of financing their acquisition
of $100,000 in new equipment in year 1. One option is to borrow
$80,000 from a local bank. The bank has asked them to produce a 3-year cash
budget broken down by year (Year 1, 2, and 3). Sales of $150,000 were earned
in the prior year and are expected to increase each year thereafter by 15 percent.
Purchases are based on an expected cost of sales of 60 percent and a required
ending inventory of 20 percent of next year’s sales. Prior year expenses
included advertising expense of $15,000, depreciation expense of $1,000, wages
expense of $20,000, supplies expense of $1,000, and utilities expense of
$3,300. All expenses except depreciation and interest expense are paid in the
year in which they are incurred and are expected to increase 10 percent each
year. Interest expense is paid in the year incurred and is expected to remain
constant at $4,000 each year for years 1–3. Collections in the year of sale are
expected to be 85 percent, with the remaining 15 percent collected in the next
year. Payments in the year of purchase are expected to be 90 percent, with the
remaining 10 percent paid in the next year. Proceeds from the $80,000 loan are
expected at the beginning of year 1, and $100,000 of equipment will be purchased
during year 1. In subsequent years, equipment purchases are expected to
be $2,000 each year. Proceeds from projected equipment sales each year are
expected to amount to $500. Annual payments of $10,360 on the loan occur at
the end of each year.
Using the ch6–07 file to start your work, create a cash budget (as you did in
the chapter) based on the assumptions just provided. Use Excel’s grouping
feature to group operating cash receipts, operating cash payment, cash from
(to) operating activities, cash from (to) investing activities, and cash from (to)
financing activities. Define names as appropriate.
Save your file as ch6-07_student_name (replacing student_name with your
name).
a. Print the newly completed worksheet in Value view, with your name
and date printed in the lower left footer and the file name in the lower
right footer. Print only columns A through E of the cash budget, no
assumptions.
b. Collapse rows to level 2, and then print the worksheet in Value view,
with your name and date printed in the lower left footer and the filename in the lower right footer. Print only columns A through E of the
cash budget, no assumptions.
c. Collapse rows to level 2, and then use what-if analysis to calculate
end-of-year cash if the sales growth each year were 0 percent. Print
the resulting worksheet in Value view, with your name and date
printed in the lower left footer and the file name in the lower right
footer. Print only columns A through E of the cash budget, no
assumptions.
d. Undo the what-if analysis performed in part d. Collapse rows to level
2, and then use goal seek to determine what annual sales growth
would be needed to produce an ending cash balance of $50,000 in
year 3. Print the resulting worksheet in Value view, with your name
and date printed in the lower left footer and the file name in the lower
right footer. Print only columns A through E of the cash budget, no
assumptions.
CHAPTER 7 Other Topics: Present/
Future Values, Predicting
Costs, and Allowance for
Uncollectible Accounts
Chapter 7 Questions
1 Identify the five variables in present value problems.
2 Describe the PV function in Excel and its arguments.
3 Describe the PMT function in Excel and its arguments.
4 Describe the FV function in Excel and its arguments.
5 Describe the Excel functions used in the Hi-Lo method of predicting
costs.
6 Describe the Excel functions used in the Least Squares/Regression
method of predicting costs.
7 Describe how to create a trend line for a chart.
8 Describe how Excel can be used to calculate an allowance for uncollectible
accounts using the percent of sales method.
9 Describe how Excel can be used to calculate an allowance for uncollectible
accounts using the aging method.
10 Compare the percent of sales and aging methods of determining uncollectible
accounts expense and the ending allowance for the uncollectible
accounts balance.
Chapter 7 Assignments
1 Create a new What SUP Present Value Analysis
a. Using the ch7-01 file to start your work, modify that worksheet to
do the following:
i. Calculate the annuity payment required at the end of each year
for the next 4 years such that the value in 4 years is $75,000
and illustrate the annual value of that investment.
ii. Calculate the future value of an investment of $23,000 invested
for 4 years at a rate of 6 percent and illustrate the annual value
of that investment.
iii. Calculate the future value of an investment of $13,500 each
year for 4 years at a rate of 5 percent and illustrate the annual
value of that investment.
iv. Calculate the initial investment required to obtain a future value
of $16,500, assuming a rate of 7 percent for 4 years and illustrate
the annual value of that investment. b. Save the file as ch7-01_student_name (replacing student_name with
your name).
c. Print the resulting worksheet in value view.
d. Print the resulting worksheet in formula view. (Type Ctrl ~ to
switch views.)
2 Create a new What SUP Cost Analysis
a. Using the ch7-02 file to start your work, create a worksheet to do
the following:
i. Calculate the Hi-Lo Method variable cost/hour open and fixed
cost.
ii. Predicted expense under the Hi-Lo Method assuming 500 open
hours.
iii. Calculate the Least Squares/Regression Method variable cost/
hour open and fixed cost.
iv. Predicted expense under the Least Squares/Regression Method
assuming 500 open hours.
v. Display a chart of Expense/Hours Open with a trend line. (be
sure to modify each axis so your scatter diagram is better displayed
as you did in the chapter)
b. Save the file as ch7-02_student_name (replacing student_name with
your name).
c. Print the resulting worksheet in Value view.
d. Print the resulting worksheet in Formula view. (Type Ctrl ~ to
switch views.)
3 Create a new What SUP Allowance for Uncollectible Accounts Analysis
a. Using the ch7-03 file to start your work, create a worksheet to do
the following:
i. Calculate uncollectible accounts expense and the allowance for
uncollectible accounts using the percent of sales method.
ii. Calculate uncollectible accounts expense and the allowance for
uncollectible accounts using the aging method.
b. Save the file as ch7-03_student_name (replacing student_name with
your name).
c. Print the resulting worksheet in Value view.
d. Print the resulting worksheet in Formula view. (Type Ctrl ~ to
switch views.)
Chapter 7 Case Problem 1:
KELLY’S BOUTIQUE
Kelly’s Boutique has several questions for you that Excel can help answer.
Kelly is planning for the future and would like you to prepare a present value
analysis. Using the file ch7-04 complete a present value analysis for the following
situations. Save the file as ch7-04_student_name (replacing student_name
with your name). Print both a Value view and Formula view of this completed
worksheet. Kelly would like to know the following:
a. How much she would have to pay at the end of each year, assuming a
4 percent rate of return, to yield $150,000 at the end of 10 years.
b. How much she would have at the end of 10 years if she invested
$75,000 today, earning 3 percent per year.
c. How much she would have at the end of 10 years if she invested
$4,785 at the end of each year, earning 2 percent per year.
d. How much she would have to invest today to have $204,530 in 10
years, earning 4 percent per year.
Kelly has a very fluctuating workforce based on seasonal demand. She’s ranged
from having 10 employees in one month to 35 employees in another month.
Some employees are paid a salary, others are paid hourly. She would like to
know more about how these costs behave. Use the file ch7-05 to complete a
cost prediction worksheet. Save the file as ch7-05_student_name (replacing student_
name with your name). The worksheet should do the following:
a. Calculate variable cost per employee, fixed costs, and a prediction of
payroll cost with 20 employees using the Hi-Lo method.
b. Calculate variable cost per employee, fixed costs, and a prediction of
payroll cost with 20 employees using the Least Squares/Regression
method.
c. Display a chart of payroll/employees with a trend line. (Be sure to
modify each axis so your scatter diagram is better displayed, as you
did earlier in this chapter)
During a recent year Kelly’s Boutique had sales on account of $6,000,000, collections
of $5,800,000, write-offs of $45,000, a beginning balance in accounts
receivable of $500,000, and a beginning balance in the allowance for uncollectible
accounts of $37,000. At year end, $580,000 of accounts receivable were
current, $39,000 were 0–30 days past due, $18,000 were 31–60 days past due,
$10,000 were 61–90 days past due, and $8,000 were over 90 days past due.
The company believes .7 percent of sales will not be collected. They also have
experience that suggests that 4 percent of all current receivables, 8 percent of
receivables 0–30 days past due, 20 percent of receivables 31–60 days past due,
25 percent of receivables 61–90 days past due, and 50 percent of receivables
over 90 days past due will not be collected. Using the file ch7-06, complete the
allowance for uncollectible accounts analysis for both standard methods. Savethe file as ch7-06_student_name (replacing student_name with your name). Print
both a Value view and Formula view of this completed worksheet.
Chapter 7 Case Problem 2:
WINE DEPOT
The Wine Depot is planning for the future and would like you to prepare a
present value analysis. Using the file ch7-07, complete a present value analysis
for the following situations. Save the file as ch7-07_student_name (replacing
student_name with your name). Print both a Value view and Formula view of
this completed worksheet. The Wine Depot would like to know the following:
a. How much they would have to pay at the end of each year, assuming a
4 percent rate of return, to yield $86,421 at the end of 7 years.
b. How much they would have at the end of 7 years if they invested
$8,000 today earning 4 percent per year.
c. How much they would have at the end of 7 years if they invested $550
at the end of each year earning 5 percent per year.
d. How much they would have to invest today to have $12,500 in 7 years,
earning 2 percent per year.
The Wine Depot is trying to better understand the behavior of their selling
expenses. They have accumulated selling expenses over the last 6 months and
believe units sold are a good predictor of expense behavior. Selling expenses
include commissions on sales and advertising. Use the file ch7-08 to complete a
cost prediction worksheet. Save the file as ch7-08_student_name (replacing student_
name with your name). The worksheet should do the following:
a. Calculate variable cost per unit sold, fixed costs, and a prediction of
selling expense when 10,000 units are sold using the Hi-Lo method.
b. Calculate variable cost per unit sold, fixed costs, and a prediction of
selling expense when 10,000 units are sold using the Least Squares/
Regression method.
c. Display a chart of selling expense/units sold with a trend line. (Be sure
to modify each axis so your scatter diagram is better displayed as you
did earlier in this chapter.)
During a recent year, the Wine Depot had sales on account of $1,601,542, collections
of $1,523,541, write-offs of $23,487, a beginning balance in accounts
receivable of $758,271, and a beginning balance in the allowance for uncollectible
accounts of $25,121. At year end, $734,539 of accounts receivable were
current, $45,812 were 0–30 days past due, $21,012 were 31–60 days past due,
$6,422 were 61–90 days past due, and $5,000 were over 90 days past due. The
company believes 1.6 percent of sales will not be collected. They also have experience
that suggests that 4 percent of all current receivables, 10 percent of
receivables 0–30 days past due, 15 percent of receivables 31–60 days past due,
22 percent of receivables 61–90 days past due, and 45 percent of receivablesover 90 days past due will not be collected. Using the file ch7-09, complete the
allowance for uncollectible accounts analysis for both standard methods. Save
the file as ch7-09_student_name (replacing student_name with your name). Print
both a Value view and Formula view of this completed worksheet.
Chapter 7 Case Problem 3:
SNICK’S BOARD SHOP
Snick’s Board Shop has several questions for you that Excel can help answer.
The company is planning for the future and would like you to prepare a present
value analysis. Using the file ch7-10, complete a present value analysis for the
following situations. Save the file as ch7-10_student_name (replacing student_
name with your name). Print both a Value view and Formula view of this completed
worksheet. They would like to know the following:
a. How much they would have to pay at the end of each year, assuming a
3 percent rate of return, to yield $80,000 at the end of 5 years.
b. How much they would have at the end of 5 years if they invested
$73,500 today, earning 3 percent per year.
c. How much they would have at the end of 5 years if they invested
$1,850 at the end of each year, earning 3 percent per year.
d. How much they would have to invest today to have $25,100 in 5 years,
earning 3 percent per year.
Snick’s Board Shop is trying to better understand the behavior of their utility
expenses. They have accumulated utility expenses over the last 9 months and
believe hours open per month are a good predictor of expense behavior. Utility
expenses include electricity, gas, and water. Use the file ch7-11 to complete a
cost prediction worksheet. Save the file as ch7-11_student_name (replacing student_
name with your name). The worksheet should do the following:
1. Using the Hi-Lo method, calculate variable cost per unit sold, fixed
costs, and a prediction of utility expense when they are open 195 hours
in a month.
2. Using the Least Squares/Regression method, calculate variable cost per
unit sold, fixed costs, and a prediction of utility expense when they are
open 195 hours in a month.
3. Display a chart of selling expense/units sold with a trend line. (Be sure
to modify each axis so your scatter diagram is better displayed, as you
did earlier in this chapter.)
During a recent year, Snick’s Board Shop had sales on account of $45,000, collections
of $45,500, write-offs of $800, a beginning balance in accounts receivable
of $5,000, and a beginning balance in the allowance for uncollectible
accounts of $300. At year end $2,400 of accounts receivable were current, $700
were 0–30 days past due, $300 were 31–60 days past due, $200 were 61–90days past due, and $100 were over 90 days past due. The company believes 1.3
percent of sales will not be collected. They also have experience suggesting that
3 percent of all current receivables, 11 percent of receivables 0–30 days past
due, 16 percent of receivables 31–60 days past due, 25 percent of receivables
61–90 days past due, and 50 percent of receivables over 90 days past due will
not be collected. Using the file ch7-12, complete the allowance for uncollectible
accounts analysis for both standard methods. Save the file as ch7-12_student_
name (replacing student_name with your name). Print both a Value view and
Formula view of this completed worksheet.
Chapter 7 Case Problem 4:
ROSEY’S ROSES
Rosey’s Roses has several questions for you that Excel can help answer. Use
student file ch7-13 to solve 3 of these problems. This file has three worksheets
labeled: Present and Future Values, Cost Prediction, and Allowance for Uncollectibles.
Complete the file answering the questions below and then save the file
as ch7-13_student_name (replacing student_name with your name). Print a
Value view of each worksheet. (Note: This file has three worksheets.)
a. Present and Future Values (Calculate the answer and then provide a
table for each question. Use the first worksheet of the workbook to
answer this problem.):
1. How much they would have to pay at the end of each year, assuming
a 6 percent rate of return, to yield $15,000 at the end of 4
years.
2. How much they would have at the end of 4 years if they invested
$1,000 at the end of each year, earning 5 percent per year.
3. How much they would have to invest today to have $15,000 in
4 years, earning 6 percent per year.
4. How much they would have at the end of 4 years if they invested
$4,000 today, earning 5 percent per year.
b. Rosey’s Roses is trying to better understand the behavior of their delivery
expenses. They have accumulated the delivery expenses over the
last 24 months and believe that miles driven per month are a good predictor
of expense behavior. Use the second worksheet of the workbook
to answer this problem:
1. Using the Hi-Lo method, calculate variable cost per mile, fixed
costs, and a prediction of delivery expense when they travel 10,000
miles in a month.
2. Using the Least Squares/Regression method, calculate variable cost
per mile, fixed costs, and a prediction of delivery expense when
they travel 10,000 miles in a month. 3. Display a chart of delivery expense and miles driven with a trend
line. (Be sure to modify each axis so your scatter diagram is better
displayed, as you did earlier in this chapter.)
c. During a recent year, Rosey’s Roses had sales on account of $345,000,
collections of $337,000, write-offs of $3,700, a beginning balance in
accounts receivable of $37,000, and a beginning balance in the allowance
for uncollectible accounts of $1,200. At year end, $34,000 of
accounts receivable were current, $4,900 were 0–30 days past due,
$1,500 were 31–60 days past due, $500 were 61–90 days past due, and
$400 were over 90 days past due. The company believes 1.3 percent of
sales will not be collected. They also have experience suggesting that 3
percent of all current receivables, 10 percent of receivables 0–30 days
past due, 15 percent of receivables 31–60 days past due, 20 percent of
receivables 61–90 days past due, and 30 percent of receivables over 90
days past due will not be collected. Use the third worksheet of the
workbook to answer this problem:
1. Complete an allowance for uncollectible accounts analysis using
the percentage of sales method.
2. Complete an allowance for uncollectible accounts analysis using
the aging method.
CHAPTER 8 Access TOUR
Chapter 8 Questions
1 What is a DBMS?
2 What is a relational database?
3 How are tables related?
4 Identify some new features in the 2013 version of Access.
5 Why would you use the File menu in Access?
6 What and where is the Quick Access Toolbar?
7 Describe the Ribbon.
8 What and where is the navigation pane?
9 What is the quickest way to access help while in Access?
10 Describe ways in which Access is used in Accounting.
CHAPTER 9 ACCESS BASICS
Chapter 9 Questions
1 Why isn’t all information entered into a single table?
2 Identify and describe the first three steps followed in the creation of a
database.
3 Which tool is used to establish relationships between tables?
4 What are queries?
5 What facts must be identified for each query?
6 What questions are asked by the Simple Query Wizard?
7 What are the purposes of forms?
8 From where does most of the information in a form come?
9 How are controls used to create forms?
10 Are headers and footers also a part of the record source?
11 What questions does the Simple Form Wizard ask?
12 What are reports?
13 To be useful, how should reports be organized?
14 What questions does the Report Wizard ask?
15 What are the five steps necessary to create a report?
16 How do you switch between the Design view and the Layout view when
creating reports?
Chapter 9 Assignments
1 Create new queries for What SUP (use the Ch9-02 file)
a. Create and print a query for What SUP that lists the supplier name,
contact name, and phone number of every supplier. (Note: Be sure
to use the supplier table to locate field names.) Save the query as
Supplier Query 1.
b. Create and print another query for What SUP that lists the supplier name,
address, city, and state of every supplier. (Note: Be sure to use the supplier
table to locate field names.) Save the query as Supplier Query 2.
2 Create new forms for What SUP (use the Ch9-02 file)
a. Create and print a form for What SUP that lists all fields from the
supplier table in a columnar format. (Note: Once again, use the supplier
table to locate field names.) Save the form as Supplier Form,
and print the first record only.
b. Create and print a form for What SUP that lists all fields from the
category table in a columnar format. (Note: Use the category table to
locate field names.) Save the form as Category Form, and print the
first record only.
3 Create new reports for What SUP (use the Ch9-02 file)
a. Create and print a report for What SUP that lists the supplier ID,
supplier name, and city from the supplier table. (Use the supplier table
to locate field names.) The report should have no grouping, be
sorted by supplier ID in ascending order, and be formatted in a tabular
layout with portrait orientation. Save the report as Suppliers by
Supplier ID. Make sure all field information is visible.
b. Create and print a report for What SUP that lists the supplier name,
contact name, contact title, and phone number from the supplier table.
(Use the supplier table to locate field names.) The report should
have no grouping, be sorted by supplier name in ascending order,
and be formatted in a tabular layout with portrait orientation. Save
the report as Suppliers by Supplier Name. Make sure all field information
is visible.
4 Create and edit tables for What SUP (use the Ch9-02 file)
a. Create a new table for What SUP that includes fields BuyerID,
BuyerName, and BuyerPhone. The first field (BuyerID) is a Number
data type; the rest are Short Text data types. All should have captions
that separate each word (e.g., Buyer ID). Set the Buyer ID field
as the Primary Key and save the new table as Buyers Table. Enter
the following buyer information into the Buyers table, and then print
the new table.
Buyer ID Buyer Name Buyer Phone
601 Lopez 555-7894
602 Ng 555-1324
603 Fortier 555-9137
b. Add a field to the Product table called BuyerID, a Number data type.
Add the specific BuyerID information to the following products in
the Products table and then print the new table.
Product ID Buyer ID
101 603
102 603
103 602
104 601
105 602
106 603
107 601
108 602
109 603
110 602
111 602
112 601
113 601
114 603
115 601
c. Establish a one-to-many relationship between the Buyers table and
the Product table using BuyerID as the related field. Print the relationships
report.
d. Create and print a query for What SUP that lists the product name,
buyer name, and buyer phone number for every product. (Note: Be
sure to use both the product and buyer tables to locate field names.)
Save the query as Product Buyer Query.
Chapter 9 Case Problem 1:
KELLY’S BOUTIQUE
Recall from Chapter 2 that Kelly’s Boutique sells books as well as women’s
shoes. Kelly’s son Casey, a college accounting student who is home for the holidays,
is eager to help his mom incorporate computers in her business. In this
chapter (and in those that follow), Casey will try teaching Kelly the use of
Access as it applies to her accounting and business needs.
Casey suggests that Kelly use a database to keep a record of her book inventory.
Kelly has a partial list of books she has purchased that she thinks should
be part of the database. This list includes each book’s ISBN, department name,
related supervisor and phone number of the supervisor, book title, publisher,
publisher contact, publisher phone number, author, and list price.
Create a new database file using the following information and name the file
ch9-03_student_name (replacing student_name with your name).
a. Use the information below to create a book table, department table,
and publisher table in Access. Then print each table.
CHABook Table
ISBN Dept Book Title PubNum Author ListPrice
684872153 Adult Angela’s Ashes 7 McCourt $ 7.99
60244151 Children Betsy–Tacy 2 Lovelace $ 12.95
670175919 Children Blueberries for Sal 5 McCloskey $ 16.99
27136701 Children Caddie Woodlawn 4 Brink $ 17.00
140286276 Adult Deep End of the Ocean 3 Mitchard $ 12.95
60173289 Adult Divine Secrets of the YaYa
Sisterhood
2 Wells $ 24.00
394800168 Children Green Eggs and Ham 6 Seuss $ 7.99
439064864 Children Harry Potter and the
Chamber of Secrets
9 Rowling $ 17.95
439136350 Children Harry Potter and the
Prisoner of Azkaban
9 Rowling $ 17.95
590353403 Children Harry Potter and the
Sorcerer’s Stone
9 Rowling $ 17.95
Dept Table
Dept Supervisor Phone
Adult Nancy Wine 555-9754
Children Barbara Manchester 555-1974
Publisher Table
Pub Num Publisher Contact Phone
1 Mass Market Paperback Smith 555-9745
2 Harper Collins Potter 555-7481
3 Penguin Frued 555-8974
4 Simon & Schuster Gonzalez 555-9874
5 Viking Press Hu 555-1654
6 Random House Ouimet 555-9144
7 Scholastic Press Salazar 555-9888
8 Touchstone Books Chi 555-1112
9 Arthur A. Levine Books Robinson 555-5118
b. Establish the appropriate relationship between each table. Print the
relationships report.
c. Create and print a query that lists department, book title, author,
supervisor, publisher, and contact. Save this query as Books by
Department.
d. Create a form that shows all fields from the book table in a columnar
format with. Save it as Book Form, and print the first record.
e. Create and print a report that contains the book title, ISBN, publisher,
and phone number. The report should have no grouping, be sorted by
book title in ascending order, and be formatted in a tabular portrait
layout and the title Book Report. All field information should be
visible.
f. Make sure you keep a copy of this file for use in the next chapter.
Chapter 9 Case Problem 2:
WINE DEPOT
The Wine Depot is interested in more effectively managing their inventory. Barbara
would like you to put together a database of their current wine inventory.
Create a new database file using the following information and name the file
ch9-04_student_name (replacing student_name with your name).
a. Use the following information to create and print a wine products table,
a buyer table, and a winery table. Use the field Name in the
Buyer and Winery tables even when you are warned against using a
reserved name.
SKU Type Winery Price Cost Size Vintage Quantity
13883 Chardonnay 7 41.00 24.60 750 1998 12
14539 Merlot 8 36.00 21.60 750 1999 5
15347 Cabernet Sauvignon 3 120.00 72.00 750 1989 7
15966 Pinot Noir 2 65.00 39.00 750 1999 24
16528 Chardonnay 15 30.00 18.00 750 2001 12
16554 Sauvignon Blanc 18 21.00 12.60 750 2001 8
16716 Chardonnay 11 20.00 12.00 750 2001 10
16739 Chardonnay 17 61.00 36.60 750 1999 5
16769 Syrah 6 34.00 20.40 750 2000 10
16874 Syrah 5 35.00 21.00 750 2000 15
17024 Sauvignon Blanc 4 22.00 13.20 750 2001 10
17275 Sauvignon Blanc 14 16.00 9.60 750 2002 1
17425 Zinfandel 10 28.00 16.80 750 2001 7
17454 Sauvignon Blanc 13 15.00 9.00 750 2002 24
17521 Chardonnay 1 24.00 14.40 750 2001 4
17539 Cabernet Sauvignon 19 75.00 45.00 750 2000 3
17549 Zinfandel 9 22.00 13.20 750 2000 3
17578 Cabernet Sauvignon 12 11.00 6.60 750 2001 2
17840 Red Chianti 16 32.00 19.20 750 1999 12
Buyer Name Phone
101 James Taylor 555-1245
102 Johnny Rivers 555-8794
103 Michael Jackson 555-9743
104 Aaden Mikowicz 555-2914
Winery Name Location Buyer
1 Babcock America 101
2 Bass Phillip Australia 104
3 Beringer America 101
4 Brander America 101
5 Cafaro America 101
6 Carhartt America 101
7 Clarendon Australia 104
8 Gainey America 102
9 Gary Farrell America 102
10 Joel Gott America 102
11 Melville America 102
12 Miguel Torres Chile 104
13 MudHouse New Zealand 104
14 Neil Ellis South Africa 103
15 Neyers America 103
16 San Vincenti Italy 104
17 Talbot America 103
18 Voss America 103
19 Woodward America 103
b. Establish the appropriate relationship between each table and then
print the relationships report.
c. Create and print a query that lists SKU, Type, Winery, Buyer, and
Buyer Phone for all products. (Save as Wine Products Query 1.)
d. Create and print a form for entering new products; use whatever style
you’d like. (Save the form as Wine Products Form 1, and print the
first record only.)
e. Create and print a report with the fields Type, Name (Name of
Winery), Vintage, and Price for all products. The report should be
sorted by Type in ascending order and be formatted in a Tabular layout
with Portrait orientation. (Save the report as Wine Products Report
1.) All field information should be visible.
f. Make sure you keep a copy of this file for use in the next chapter.
Chapter 9 Case Problem 3:
SNICK’S BOARD SHOP
Snick’s Board Shop is interested in more effectively managing their inventory.
Casey and Caitlin would like you to put together a database of their current inventory.
Create a new database file using the information below. Name the file
ch9-05_student_name (replacing student_name with your name).
a. Use the information below to create and print a products table, a manufacturer
table, and a category table as you did earlier in this chapter.
Table Structure:
Table Field Name Data Type
Category CategoryID Number
CategoryName Short Text
Manufacturer ManufacturerID Short Text
ManufacturerName Short Text
Product ProductID Short Text
ProductName Short Text
CategoryID Number
ManufacturerID Short Text
Price Currency
Style Short Text
Quantity Number
Category Table
CategoryID CategoryName
1 Complete
2 Ramp
3 Longboards
4 Protective Gear
5 Rails
Manufacturer Table
ManufacturerID ManufacturerName
ALM Almost
EMT Element
GC Goldcoast
MOJ Mojo
KRO Krooked
SC Santa Cruz
S9 Sector 9
888 Triple 8
0 Zero
Product Table
ProductID ProductName CategoryID ManufacturerID Price Style Quantity
61-16758 Element Flat Bar Grind Rail 5 EMT 109.99 Black 10
61-23116 Zero Switchblade Rail 5 0 119.99 Silver 10
61-36447 Zero 6 Foot Flat Bar Grind Rail 5 0 99.99 Red 10
65-00011 Mojo Wedge Ramp 2 MOJ 179.99 Black/Blue 8
65-00335 Almost Mullen OC Impact V4 1 ALM 119.99 Orange/Teal 10
65-00358 Element Section 1 EMT 89.99 Black/Red 10
65-00981 Almost Mullen Day Glow 1 ALM 119.99 Black/Yellow 10
65-01135 Macon Helmet 4 888 59.99 White 10
65-01400 Krooked Eyes 1 KRO 89.99 Pink/Blue 10
65-01783 Santa Cruz Landshark 3 SC 99.99 Blue/White 10
65-01786 Santa Cruz Tiger Shark 3 SC 129.99 Orange/Black 10
65-01837 Element Launch Ramp 2 EMT 179.99 Black/Red 5
65-01967 Sector 9 Sand Wedge 3 S9 189.99 Black/White 10
65-01970 Goldcoast Venice 3 GC 149.99 Green/Yellow 10
65-23174 Little Tricky Helmet 4 888 34.99 Black 10
b. Establish the appropriate relationship between each table and then
print the relationships report.
c. Create and print a query using the Simple Query Wizard that lists ProductName,
CategoryName, and ManufacturerName for all product.
(Save Query 1.)
d. Create and print a form using the Form Wizard for entering new products
that lists all the available fields in the product table; use whatever
style you’d like. (Save the form as Form 1, and print the first record
only.)
e. Create and print a report using the Report Wizard with the fields ProductID,
Style, Quantity, and ManufacturerName for all products. The
report should be viewed by Manufacturer sorted by ProductID in
ascending order and be formatted in a Tabular layout with Portrait orientation
and no grouping. (Save the report as Report 1.) All field information
should be visible.
f. Make sure you keep a copy of this file for use in the next chapter.
Chapter 9 Case Problem 4:
ROSEY’S ROSES
Rosey’s Roses is interested in more effectively managing their inventory. They
would like you to put together a database of their current inventory. Create a
new database file using the information below. Name the file ch9-06_student_
name (replacing student_name with your name).
a. Use the information below to create and print three tables—Products,
Type, and Grower—as you did earlier in this chapter.
Access Basics Chapter 9 229
Table Structure:
Table Field Name Data Type Primary Key?
Product ID Number Yes
Type Short Text
Description Short Text
Quantity Number
Cost/Unit Currency
Grower ID Number
Type Type Short Text Yes
Description Long Text
Grower Grower ID Number Yes
Grower Name Short Text
Contact Short Text
Products:
ID Type Description Quantity Cost/Unit Grower ID
1 Shrub Abraham Darby #5 25 $39.99 100
2 Shrub Be My Baby #5 30 $18.99 100
3 Shrub Deja Blu #5 18 $25.99 200
4 Shrub Koko Loko #7 17 $17.99 300
5 Shrub Peach Drift #10 33 $12.99 200
6 Shrub Red Drift #10 15 $ 6.99 100
7 Shrub Sedona #5 3 $14.99 200
8 Shrub Sweet Intoxication #5 17 $ 9.99 300
9 Shrub Wing Ding #5 30 $11.99 300
10 Climber Climbing Orange Crush #7 16 $32.99 100
11 Climber Don Juan Climber #5 25 $37.99 200
12 Tree Barbara Streisand 36in Tree 50 $52.99 300
13 Tree Firefighter 36in Tree 14 $55.99 200
14 Tree Trumpeter 36in Tree 4 $65.99 100
Type:
Type Description
Climber Climbing roses that are trained upright will tend to bloom only at the tips, and
will not generate laterals. Other terms that are commonly used are climbers,
ramblers, pillar roses, etc. There is no "official" definition of these terms.
Generally, climbing roses are repeat blooming roses with large, stiff canes.
Shrub Rose shrubs that are not classified under the common varieties are known as
shrub roses. Shrub roses are available in many different varieties, colors, and
sizes.
Tree A rose tree is a rose bush that is pruned and grafted to grow as a tree. It has a
long trunk with foliage and flower growth in a rounded mass at the top of the
trunk.
Grower
:
Grower ID Grower Name Contact
100 Jackson & Perkins Nate Rexford
200 Passion Growers Kyle Said
300 Garden Valley Casey Crawford
b. Establish the appropriate relationship between each table and then
print the relationships report.
c. Create and print a query using the Simple Query Wizard that lists the
product description, type, and grower name for all products. (Save as
Query 1.)
d. Create and print a form using the Form Wizard for entering new products
that lists all the available fields in the product table; use whatever
style you’d like. (Save the form as Form 1, and print the first record
only.)
e. Create and print a report using the Report Wizard with the fields ID,
type, description, and grower name for all products. The report should
be viewed by grower sorted by ID in ascending order and be formatted
in a Stepped layout with Portrait orientation. (Save the report as
Report 1.) All field information should be visible.
f. Make sure you keep a copy of this file for use in the next chapter.
CHAPTER 10 TABLES
Chapter 10 Questions
1 Describe the process for deleting a record from a table.
2 Define OLE.
3 What can you do in Access to change the structure of a database?
4 Why would you want to change the column width and/or row height of a
table?
5 What do validation rules do?
6 What is the process for adding a validation rule to a field?
7 Why would you establish default values for a field?
8 What are input masks?
9 What is referential integrity?
10 What rules must be followed if referential integrity is enforced?
Chapter 10 Assignments
1 Add, change, and delete records to the What SUP database (use the
Ch10-02 file)
a. Add the following record to the Buyers table: BuyerID 605, Farley,
555-7187.
b. Add G as a CategoryID with a CategoryName of Women’s as a
category to the Category table.
c. Delete the following record from the Buyers table: BuyerID 601.
d. Change the buyer for the following Product IDs from buyer 601 to
buyer 605: Product ID 104, 108, 113, and 115.
e. Print the first page of the product, buyers, and category tables as
modified in a landscape orientation.
f. Save your file as Ch10-02_01_student_name (replacing student_
name with your name).
2 Add pictures to the What SUP database (use the Ch10-02 file; do not
use the file you just updated in Assignment 1)
a. Add pictures for the remaining products in the Product table using
the picture files provided. Each product has a picture associated with
it and is labeled with the product ID number.
b. Print the second page of the Product table as modified in landscape
orientation.
c. Save your file as Ch10-02_02_student_name (replacing student_name
with your name).
3 Change the structure of the What SUP database (Use the Ch10-02 file;
do not use the file you just updated in Assignment 1 or 2.)
a. Add the following product to the Product table. ProductID: 117;
ProductName: LadyG; Length: 8.5; SupplierID: 6; CategoryID: F;
ProductNumber: 3983AE; UnitCost: 620; Reorder Level: 1; Lead
Time: 3; Buyer ID: 604; Picture: 117.bmp.
b. Modify the caption and re-size the column width of each field of the
Product table so that the table will fit on one page when printed in
landscape orientation.
c. Print the Product table in landscape orientation.
d. Create a validation rule for the Reorder Level field of the Product
table, making the field a required one and making sure the reorder
level is greater than 0 but less than 25 units. Create your own feedback
statement and insert it as the validation text.
e. Establish 7 as the default value for the Lead Time field of the Product
table.
f. Establish a phone number input mask for the BuyerPhone field of
the Buyers table.
g. Enforce referential integrity between the Product table and the Buyer
and Category tables.
h. Print a Relationships report.
i. Print documentation on the properties of the Reorder Level and Lead
Time fields of the Product table and the BuyerPhone field of the
Buyers table.
j. Save your file as Ch10-02_03_student_name (replacing student_name
with your name).
Chapter 10 Case Problem 1:
KELLY’S BOUTIQUE
Note: You must have completed Case 1 in the previous chapter in order to continue
working on this case.
In Chapter 9 you created a database for Kelly’s Boutique consisting of a
Book table, a Department table, and a Publisher table. You also created an
initial form, query, and report. Kelly would now like you to make some adjustments
to the tables previously created by adding and deleting some records,
adding some OLE fields and pictures, and changing the structure of the database.
Make the following changes for Kelly using the ch9-03_student_name fileyou created in Chapter 9. (Note: Open your ch9-03_student_name file first, save
it as ch10-03_student_name, and then make the listed changes.)
a. Add Warner as the tenth publisher to the Publisher table with a contact
Yee and phone 555-7894.
b. Add the following records to the Book table:
ISBN Dept Book Title Num Author Price
039480029 Children Hop on Pop 6 Seuss 7.99
039480001 Children The Cat in the Hat 6 Seuss 7.99
446676098 Adult The Notebook 10 Sparks 16.95
c. Add a Picture field for OLE objects to the Book table.
d. Add pictures to the Book table for Green Eggs and Ham, Hop on
Pop, Harry Potter and the Prisoner of Azkaban, Divine Secrets, and
Deep End of the Ocean. Picture files are labeled by book name and
are located on your student disk in an Images folder.
e. Resize the column width of each field and the record’s row height of
each table so that they fit on one page when printed.
f. Establish a phone number input mask for the Phone field of the Publisher
and Department tables.
g. Enforce referential integrity between the Book table and the Department
and Publisher tables.
h. Create a validation rule for the List price field of the Book table,
making the field a required one and making sure the price is no less
than $1 but no more than $100. Create your own feedback statement
and insert it as the validation text.
i. Establish Children as the default value for the Department field of the
Book table.
j. Print the Book, Department, and Publisher tables.
k. Print the Relationships report.
l. Print documentation on the properties of the List Price field of the
Book table and the Phone field of the Publisher table.
m. Make sure you keep a copy of this file for use in the next chapter.
Chapter 10 Case Problem 2:
WINE DEPOT
Note: You must have completed Case 2 in the previous chapter in order to continue
working on this case.
In Chapter 9 you created a database for the Wine Depot consisting of a Wine
Products table, a Winery table, and a Buyer table. You also created an initial query,
form, and report. Barbara has now asked you to make some changes to those files.
Make the following changes for Barbara using the ch9-04_student_name file youcreated in Chapter 9. (Note: Open your ch9-04_student_name file first, save it as
ch10-04_student_name, and then make the listed changes.)
a. Add buyer 105, Carly Simon, 555-6874 to the Buyer table.
b. Add winery 20 (Robert Mondovi, American, 105) and winery 21
(Wente, American, 104) to the Winery table.
c. Delete wines with SKU 15966 and 16769.
d. Delete winery 2.
e. Add the following records to the Wine Products table:
SKU Type Winery Price Cost Size Vintage Quantity
12564 Merlot 21 12.99 8.00 750 2000 24
12895 Sauvignon Blanc 4 15.49 9.00 750 2002 12
16900 Syrah 11 30.00 24.00 750 2001 12
11350 Pinot Noir 11 30.00 24.00 750 2001 12
11475 Pinot Noir 1 22.00 17.50 750 2001 48
12380 Pinot Noir 20 21.00 18.00 750 2000 24
12383 Chardonnay 20 18.00 12.00 750 2000 36
12384 Fume Blanc 20 17.49 11.00 750 2001 36
f. Enforce referential integrity between the tables.
g. Print the Relationships report.
h. Add a Picture field for OLE objects to the Wine Products table.
i. Add pictures to the Wine Products table for products 11475, 12384,
12895, 14539, and 15347. (Note: Pictures for these products are provided
in an Images folder on your student disk and are labeled with
the SKU number.)
j. Establish a phone number input mask for the Phone field of the Buyer
table, and then change all the phone numbers in the table to include
an area code of 805.
k. Create a validation rule for the Price field of the Wine Products table,
making the price field required and making sure the price is no less
than $1 but no more than $100. Create your own feedback statement
and enter it as the validation text. Save the changes you made.
l. When creating your validation rule, you might well have checked to
see if the data already stored actually met that rule. If you did check,
you found that the validation rule was in conflict with some of the
existing data. Change the rule so that the price must be no less than
$1 but now no more than $200. Create your own feedback statement
and enter it as the validation text. Save the changes you made.
m. Establish a default value of 750 for the Size field of the Wine Products
table.
n. Resize the column width of the Wine Products table so that it can fit
on one page when printed in landscape orientation. o. Print each table.
p. Print documentation on the properties of the Phone field of the Buyer
table and the Price field of the Wine Products table.
q. Make sure you keep a copy of this file for use in the next chapter.
Chapter 10 Case Problem 3:
SNICK’S BOARD SHOP
Note: You must have completed Case 3 in the previous chapter in order to continue
working on this case.
In Chapter 9 you created a database for the Snick’s Board Shop consisting of
a Products table, a Category table, and a Manufacturer table. You also created
an initial query, form, and report. Caitlin has now asked you to make some
changes to those files.
Make the following changes for Caitlin, using the ch9-05_student_name file
you created in Chapter 9. (Note: Open your ch9-05_student_name file first, save
it as Ch10-05_student_name, and then make the listed changes.)
a. Add CategoryID 6, CategoryName T-shirts to the category table.
b. Add ManufacturerID AH, ManufacturerName Anti-Hero to the manufacturer
table.
c. Add ProductID 62-01296, ProductName Anti-Hero Logo T-Shirt,
CategoryID 6, ManufacturerID AH, Price $14.99, Style Red, Quantity
30 to the product table.
d. Change ProductID 65-23174 to 61-23174
e. Delete ProductID 65-01837 from the product table.
f. Enforce referential integrity between the tables.
g. Print the Relationships report.
h. Add a Picture field for OLE objects to the Products table.
i. Add pictures to the products table. (Note: Pictures for these products
are provided in the Images folder and are labeled with the ProductID.)
j. Add a PhoneNumber field to the manufacturer table with a standard
phone number input mask and then add the following phone numbers
to the manufacturer table.
ManufacturerID PhoneNumber
AH 619-555-8521
EMT 305-555-7861
S9 415-555-7142
k. Create a validation rule for the Price field of the product table, making
the price field required and making sure the price is greater than
$1 but less than $500. Create your own feedback statement and enter
it as the validation text. Save the changes you made. l. Establish a default value of 20 for the Quantify field of the product
table.
m. Resize the column width of the product table so that it can fit on one
page when printed in landscape orientation.
n. Use Access’s help feature to learn how to sort tables alphabetically by
a certain field. Then sort and save each table by its primary key.
o. Print each table.
p. Print documentation on the properties of the PhoneNumber field of
the manufacturer table and the quantity field of the product table.
q. Make sure you keep a copy of this file for use in the next chapter.
Chapter 10 Case Problem 4:
ROSEY’S ROSES
Note: You must have completed Case 4 in the previous chapter in order to continue
working on this case. In Chapter 9, you created a database for Rosey’s
Roses consisting of a Product table, a Type table, and a Grower table. You also
created an initial query, form, and report. You have now been asked to make
some changes to those files.
Make the following changes using the ch9-06_student_name file you created
in Chapter 9. (Note: Open your ch9-06_student_name file first, save it as
Ch10-06_student_name, and then make the listed changes.)
a. Add Type ‘‘Creeper’’, and Description ‘‘Low-growing roses used as
ground cover.’’ to the Type table.
b. Add Grower ID ‘‘400’’, Grower Name ‘‘Owen Farms’’, and Contact
‘‘Maria Lopez’’ to the Grower table.
c. Add ID ‘‘15’’, Type ‘‘Creeper’’, Description ‘‘Sunlight #3’’, Quantity
‘‘35’’, Cost/Unit ‘‘30.00’’, and Grower ID ‘‘400’’ to the Product table.
d. Delete ID ‘‘7’’ from the Product table.
e. Delete the existing relationships between tables.
f. Change the Field Size of the following fields in the Product table:
Type – 10 and Description – 30.
g. Add a Picture field for OLE objects to the Product table.
h. Add pictures to the Product table. (Note: Pictures for these products are
provided in the Images folder and are labeled with the product ID.)
i. Add a Phone Number field to the Grower table with a standard phone
number input mask and a field size of 10, and then add the following
phone numbers: ID 100 – 503-555-1957, ID 200 – 408-555-8741, ID
300 – 212-555-1154, and ID 400 – 805-555-6974.
j. Create a validation rule for the Cost/Unit field of the Product table,
making the Cost/Unit field required and making sure the Cost/Unit isgreater than $1 but less than $100. Create your own feedback statement
and enter it as the validation text.
k. Establish a default value of 10 for the Quantify field of the Product
table.
l. Recreate relationships between tables and enforce referential integrity.
m. Print the relationships report.
n. Print each table.
o. Print documentation on the properties of the Phone Number field of
the Grower table, the Quantity field of the Product table, and the
Cost/Unit field of the Product table.
p. Make sure you keep a copy of this file for use in the next chapter.
CHAPTER 11 QUERIES
Chapter 11 Questions
1 Describe the difference between the Query Wizard and Design view
methods of creating a query.
2 Describe the process for using criteria in a query using the Design view
method.
3 How and why are wildcards used in a query?
4 Describe the process for adding to an existing query a field that exists in
a table not currently included in the query.
5 What are the three key comparison operators used in a query?
6 What will compound criteria allow you to accomplish in Access?
7 Are the results of a calculation stored as a field in a table?
8 What is an action query? Give examples.
9 What is a parameter query?
10 When might you use a delete query?
Chapter 11 Assignments
1 Create select queries for What SUP using specific criteria (use the Ch11-
02 student file).
a. Create and print a select query that lists the buyer name, buyer
phone number, and product name of product number GS233. (Hint:
The product number should not appear on your printed query if you
answered the question correctly.) Save this query as Ch 11 Assignment
1a before you print it.
b. Create and print a select query that lists the category name, product
name, product number, and lead times for all products with a lead
time in excess of 10 days. Save this query as Ch 11 Assignment 1b
before you print it.
2 Create select queries for What SUP. (Use the Ch11-02 student file, or
use the file you used in answering the previous question.)
a. Create and print a select query that lists the product name, quantity,
supplier contact name, and supplier phone number for any River
SUPs. Save this query as Ch 11 Assignment 2a before you print it.
b. Create and print a select query that lists the supplier name, city, and
phone number for any supplier located in the state of New Jersey.
Save this query as Ch 11 Assignment 2b before you print it.
3 Edit select queries for What SUP. (Use the Ch11-02 student file, or use
the file you used in answering the previous question.)
a. Edit Query1 by adding the field PostalCode from the Supplier Table
and deleting the field StateorProvince. Save this query as Ch 11
Assignment 3a before you print it.
b. Edit Query2 to include the field Category Name and to include only
the category Race, sorted by product ID in ascending order. Save
this query as Ch 11 Assignment 3b before you print it.
4 Create select queries for What SUP using comparison operators. (Use the
Ch11-02 student file, or use the file you used in answering the previous
question.)
a. Create and print a select query that lists the product name, unit cost,
supplier name, and supplier contact name for any unit cost between
$300 and $600, sorted in descending order by unit cost. Save this
query as Ch 11 Assignment 4a before you print it.
b. Create and print a select query that lists the product name, unit cost,
and category name for any unit cost less than or equal to $700,
sorted in ascending order by unit cost. Save this query as Ch 11
Assignment 4b before you print it.
5 Create additional select queries for What SUP using computed fields.
(Use the Ch11-02 student file, or use the file you used in answering the
previous question.)
a. Create and print a select query that lists the product name unit price,
unit cost, and unit gross profit for all products. Sorted in descending
order by unit gross profit price. (Hint: You’ll need to create a computed
field titled Unit Gross Profit whose formula is the product’s
unit price minus unit cost.) Save this query as Ch 11 Assignment 5a
before you print it.
b. Create and print a select query that lists the product name, unit price,
quantity, and sales value for all paddles. (Hint: You’ll need to create
another computed field, Sales Value, whose formula is the unit price
times quantity; format the computed field to Currency using the
Property Sheet.) Sort the query by unit price in ascending order.
Save this query as Ch 11 Assignment 5b before you print it.
6 Create additional select queries for What SUP using statistics. (Use file
you used in answering the previous question.) a. Create and print a select query that calculates the total sales value of
all Flatwater SUPs in inventory. (Hint: You’ll need to use your completed
query from assignment 5b as the source for this query. If you
did not complete 5b, do it now. Format this computed field to Currency
with two decimal places with a caption Sales Value of all Flatwater
SUPs.) Save this query as Ch 11 Assignment 6a before you
print it.
b. Create and print a query that includes all products and lists category
name, product name, unit price, unit cost, unit gross profit, quantity,
and gross profit. To do this, create two computed fields: Unit Gross
Profit (unit price less unit cost) and Gross Profit (unit gross profit
times quantity). Both calculated fields should have a Currency format
and a caption with spaces. Save this query as Ch 11 Assignment
6b before you print it.
c. Next create and print a query that sums the gross profit of each category,
using the query just created in assignment 6b as the source.
Format this totaled value as Currency with two decimal places and a
caption of Gross Profit. Save this query as Ch 11 Assignment 6c
before you print it.
7 Create action queries for What SUP. (Use the Ch11-02 student file, or
use the file you used in answering the previous question.)
a. Create and run an action query that increases the unit price for all
products by 6 percent. Save this query as Ch 11 Assignment 7a.
Create and print an additional query listing the product name and
unit price after running the update query. Do not save this query.
b. Create a parameter query that lists products purchased from a particular
supplier. The query should ask ‘‘Enter supplier’s name:’’ and list
the supplier name, product name, quantity, and unit cost. Save this
query as Ch 11 Assignment 7b. Run and print the query entering
Bark as the supplier.
c. Create and run a delete query that deletes all products where
Quantity ¼ 0. Save this query as Ch 11 Assignment 7c. Create and
print a query listing the supplier ID and product name for all products
still in the database.
Chapter 11 Case Problem 1:
KELLY’S BOUTIQUE
Note: You must have completed Case 1 in the previous chapter in order to continue
working on this case.
In the last chapter you added and modified some tables for Kelly’s Boutique.
She would now like you to create, run, and print some select, parameter, and
action queries. Make the following changes for Kelly, using the ch10-03_student_
name file you created in Chapter 10. (Note: Open your ch10-03_student_namefile and then save it as ch11-03_student_name before making the indicated
changes.)
Add a field Quantity (Data Type ¼ Number) to the Book Table and then
enter values as follows:
Book Title Quantity
Angela’s Ashes 18
Betsy - Tacy 4
Blueberries for Sal 5
Caddie Woodlawn 4
Deep End of the Ocean 2
Divine Secrets of the YaYa Sisterhood 3
Book Title Quantity
Green Eggs and Ham 15
Harry Potter and the Chamber of Secrets 10
Harry Potter and the Prisoner of Azkaban 8
Harry Potter and the Sorcerer’s Stone 6
Hop on Pop 8
The Cat in the Hat 4
The Notebook 3
Add a field Markup (Data Type ¼ Number, Field Size ¼ Decimal, Format ¼
Percent, Scale ¼ 2, Decimal Places ¼ 0) to the Dept Table and then enter values
as follows:
Dept Markup
Adult 100%
Children 50%
a. Create and print a select query that lists the author and book title for
all books written by Seuss. Save this query as Ch 11 Kelly Case a
before you print it.
b. Create and print a select query that lists the book title and author for
all books purchased and supervised by Barbara Manchester, sorted in
ascending order by book title. Save this query as Ch 11 Kelly Case b
before you print it.
c. Create and print a select query that lists the book title and list price
for all book titles that start with Harry. Save this query as Ch 11 Kelly
Case c before you print it.
d. Edit the query you just created. Add fields for author and for publisher
and delete the list price field. Change the criteria from ‘‘starting
with Harry’’ to ‘‘containing the word Secrets’’. (Hint: Place a wildcard
character in front of and behind the word.) Save this query as Ch 11
Kelly Case d before you print it.
e. Create and print a select query that lists the book title and the publisher
contact person and phone number for all books with a list pricegreater than $17. Save this query as Ch 11 Kelly Case e before you
print it.
f. Create and print a select query that lists the ISBN, book title, and list
price for all books with a list price greater than $20 or less than $10,
sorted in ascending order by list price. Save this query as Ch 11 Kelly
Case f before you print it.
g. Create and print a select query that lists the book title, list price, quantity,
and retail value (a computed field equal to list price multiplied by
quantity), sorted in descending order by retail value. Be sure to format
the field as Currency. Save this query as Ch 11 Kelly Case g before
you print it.
h. Create and print a select query that lists book title, list price, markup,
unit cost, quantity, and cost. Unit cost is a computed field (list price
divided by 1 plus markup). Cost is another computed field (unit cost
times quantity). Be sure to format both computed fields as Currency
and to sort the query alphabetically by book title. Save this query as
Ch 11 Kelly Case h before you print it.
i. Create and print a select query that sums the total cost of the book inventory.
(Hint: Use the query just created in part h as your source for
this new query and use the currency format.) Save this query as Ch
11 Kelly Case i before you print it.
j. Modify the query created in part h so that it includes the Department
field. Save the query as Ch 10 Kelly Case h 1, and then use that
modified query as the source for a query that sums the cost of inventory
by department. Save this latter query as Ch 11 Kelly Case j
before you print it.
k. Create and run an action query that increases all books’ list price by 5
percent. Be sure to back up your file first! Save this query as Ch 10
Kelly Case k. Create another query that lists the book title and list
price for all books, sorted alphabetically by book title. Save this query
as Ch 11 Kelly Case k 1 and then print it.
l. Create a parameter query that lists books from a particular publisher.
The query should ask ‘‘Enter publisher’s name:’’ and then list the publisher,
book title, and quantity for that publisher. Save this query as
Ch 11 Kelly Case l. Run and print the query after entering ‘‘Harper
Collins’’ as the publisher.
m. Create and run a delete query that deletes all products with a quantity
of 0. Save this query as Ch 11 Kelly Case m.
n. Create a query listing the book title and quantity for all the remaining
books in inventory, sorted by quantity in descending order. Save this
query as Ch 11 Kelly Case n and then print it.
o. Make sure you keep a copy of this file for use in the next chapter.
Chapter 11 Case Problem 2:
WINE DEPOT
Note: You must have completed Case 2 in the previous chapter in order to continue
working on this case.
In the last chapter you modified some tables for the Wine Depot. Now Barbara
would like you to help her create some queries to extract some information
from the database. Make the following changes for Barbara using the ch10-
04_student_name file you created in Chapter 10. (Note: Open your ch10-
04_student_name file and then save it as ch11-04_student_name before making
the indicated changes.)
a. Create a select query that lists the type, winery, and price for all
wines on hand from winery #4. Save this query as Ch 11 Wine
Depot Case a and then print it.
b. Create a select query that lists the type, winery name, and price for
all wines whose buyer name is Michael Jackson. Save this query as
Ch 11 Wine Depot Case b and then print it.
c. Create a select query that lists the SKU and type for wines types
that contain the word ‘‘Blanc’’. Save this query as Ch 11 Wine
Depot Case c and then print it.
d. Edit the query you just created in 2c above. Add fields for cost and
vintage and remove the SKU field. Change the criteria of the query
from ‘‘types that include the word Blanc’’ to ‘‘types that begin with
the letter S’’. Save this query as Ch 11 Wine Depot Case d and
then print it.
e. Create a select query that lists type, price, winery name, buyer
name, and phone number for all wines with a price greater than $35.
Save this query as Ch 11 Wine Depot Case e and then print it.
f. Create a select query that lists type, price, winery name, buyer
name, and phone number for all wines with a price greater than
$35 but less than $50. Save this query as Ch 11 Wine Depot Case
f and then print it.
g. Create a select query that lists SKU, type, winery name, price,
quantity, and retail value (a computed field: quantity times price,
formatted as currency) for all Chardonnay wines, sorted in descending
order by retail value. Save this query as Ch 11 Wine Depot
Case g and then print it.
h. Create an update query that changes the price of all wines to be
150% of cost. (Remember to back up your file first.) Save this
query as Ch 11 Wine Depot Case h.
i. Create a parameter query that lists SKU, type, winery name, and
price. The query should ask ‘‘What type of wine?’’ Sort the query
in descending order by price. Save this query as Ch 11 Wine Depot
Case i. Run the query for Pinot Noir wines and then print it. j. Create a select query that sums the total retail value of the Chardonnay
wine inventory, formatted as currency. Be sure to list the
wine type in your query. (Hint: Use the Ch 10 Wine Depot Case g
query you created previously as the source for this new query.)
Save this query as Ch 11 Wine Depot Case j and then print it.
k. Create a select query that lists SKU, type, winery name, cost, quantity,
and total cost (a computed field: quantity times cost, formatted
as currency) for all wines, sorted in ascending order by SKU. Save
this query as Ch 11 Wine Depot Case k and then print it.
l. Create a select query that sums the cost of wine inventory by type
(in currency format), sorted by type. (Hint: Use the Ch 11 Wine
Depot Case k query as the source for your new query.) Save the
new query as Ch 11 Wine Depot Case l and then print it.
m. Make sure you keep a copy of this file for use in the next chapter.
Chapter 11 Case Problem 3:
SNICK’S BOARD SHOP
Note: You must have completed Case 3 in the previous chapter in order to continue
working on this case.
In the last chapter you modified some tables for the Snick’s Board Shop.
Now Caitlin would like you to help her create some queries to extract information
from the database. Make the following changes for Caitlin, using the ch10-
05_student_name file you created in Chapter 10. (Note: Open your ch10-
05_student_name file and then save it as ch11-05_student_name before making
the indicated changes.)
a. Create a select query that lists the category name, manufacturer
name, product name, and price for all products from Manufacturer
ID 888. Save this query as Query A and then print it.
b. Create a select query that lists the manufacturer name, product
name, and price for all products with a Category ID of 1. Save this
query as Query B and then print it.
c. Create a select query that lists the product ID, category name, and
style for styles that contain the word ‘‘Black’’. Save this query as
Query C and then print it.
d. Edit the query you just created in the previous step. Add the manufacturer
name field and remove the category name field. Change the
criteria of the query from styles that contain the word ‘‘Black’’ to
styles that contain the word ‘‘Blue’’. Save this query as Query D and
then print it.
e. Create a select query that lists product name, price, and quantity
for all products with a price greater than $100. Save this query as
Query E and then print it. f. Create a select query that lists product name, price, and quantity
for all products with a price greater than $100 but less than $130.
Save this query as Query F and then print it.
g. Add a number field ‘‘Discount’’ to the Manufacturer table. Set the
field size of this new field to Single and the Format to Percent. Set
the Discount to 10% for manufacturers EMT and MOJ. All other
manufacturers discount should be set to 0%. Create a select query
that lists Product ID, Price, and Discounted Price (a new computed
field: Price times 1 - Discount, formatted as currency) for all products
sorted in ascending order by Discounted Price. Save this query
as Query G and then print it.
h. Create an update query that increases the price of all products by
10%. (Remember to back up your file first.) Save this query as
Query H. Open Query G again and then print it with the new prices.
i. Create a parameter query that lists the manufacturer name, product
name, price, style, and quantity. The query should state ‘‘Enter
Manufacturer ID’’. Sort the query in ascending order by product
name. Save this query as Query I. Run the query for Manufacturer
ID SC and then print it.
j. Create a select query that sums the total retail value of the complete
boards in inventory, formatted as currency. (Hint: First create
a query with the fields CategoryID, CategoryName, Price, Quantity,
and a computed field titled Retail Value, which is the price times
quantity.) Save this query as Query J – 1, and then create the summation
query. Save this query as Query J - 2 and then print it.
k. Create a select query that sums the retail value of all products by
category (in currency format), sorted by type. (Hint: Use the Query
J - 1 query, modify it by removing the criteria, then save it as
Query K – 1, using Query K – 1 as the source for your new
query.) Your new query should have two fields: CategoryName
and SumOfRetailValue. Save the new query as Query K - 2 and
then print it.
l. Make sure you keep a copy of this file for use in the next chapter.
Chapter 11 Case Problem 4:
ROSEY’S ROSES
Note: You must have completed Case 4 in the previous chapter in order to continue
working on this case. In the last chapter, you modified some tables for
Rosey’s Roses. Now the company would like you to help it create some queries
to extract information from the database. Make the following changes using the
ch10-06_student_name file you created in Chapter 10. (Note: Open your ch10-
06_student_name file and then save it as ch11-06_student_name before making
the indicated changes.) a. Create a select query using the Grower ID field from the Grower
table, the Description field from the Product table, and the Cost/
Unit for all products from Grower ID 100. Do not show the
Grower ID on the resulting query. Save this query as Query A and
then print it.
b. Create a select query that lists the Grower Name, product Description,
and Quantity for all products where the Description field
contains the word ‘‘Drift’’. Save this query as Query B and then
print it.
c. Create a select query that lists the product Description, Grower
Name, Contact, Phone Number, and Cost/Unit for all products with
a Cost/Unit less than $18. Save this query as Query C and then
print it.
d. Create a select query that lists the product Description, Grower
Name, Contact, Phone Number, and Cost/Unit for all products with
a Cost/Unit greater than $18 but less than $50. Save this query as
Query D and then print it.
e. Create and print a new table called Customer as follows:
Customer Table Structure:
Field Name Data Type Field Size/Format Primary Key?
Customer
Number
Text 2 Yes
Customer Name Text 30
Discount Number Single/Percent
Customer Table Data:
Customer Number Customer Name Discount
10 Jan Muller 10%
11 Robert Frost 10%
12 Juliet Inch 0%
f. Add a number field ‘‘Markup’’ to the Grower table. Set the field
size of this new field to Single and the Format to Percent. Set the
Markup to 100% for Grower ID 100 and 200 and then set the
markup to 125% for Grower ID 300 and 400. Print the Grower
table. g. Create and print a new table called Quote as follows:
Quote Table Structure:
Field Name Data Type Field Size Primary Key?
Quote Number Number Yes
Customer
Number
Text 2
ID (from the
Product table)
Number
Quantity
Ordered
Number
Quote Table Data:
Quote Number Customer Number ID Quantity Ordered
100 10 4 10
101 11 11 20
102 12 12 40
h. Establish a relationship between the Quote table and the Product
table (ID) and the Customer table and the Quote table (Customer
Number) enforcing referential integrity. Print the Relationships
report.
i. Create a select query that contains the Quote Number, Customer
Name, Product Table Description, Quantity Ordered, Sales Price
(a new computed field: Cost/Unit _ (1 þ Markup) formatted as
currency, and Quote Amount (a new computed field: Quantity
Ordered _ Sales Price) also formatted as currency. Save as Query
E and then print.
j. Modify the query you created in (i) above to include a new field
called Cost (a computed field: Quantity Ordered _ Cost/Unit) and
a new field called Gross Profit (a computed field: Quote Amount –
Cost). Format both as currency. Save as Query F and then print.
k. Create and run an update query that increases the Cost/Unit of all
products by 7%. (Remember to back up your file first.) Save this
query as Query G. Create a new select query that lists all products
showing the ID, Description, and Cost/Unit fields. Save this query
as Query H and then print it.
l. Create a parameter query that lists the product Description and
Grower Name. The query should state ‘‘For which type of rose?’’
(Hint: Include Type in your query but don’t show it when you run
the query.) Save this query as Query I. Run the query for type Tree
and then print it.
m. Create a select query that sums the total cost of all roses currently
in inventory, formatted as currency. (Hint: First create a query withthe fields product Description, Quantity, and Cost/Unit and a computed
field titled Cost, which is the product of Quantity and Cost/
Unit formatted as currency.) Save this query as Query J – 1, run it,
and then print it. Then create the summation query, save it as
Query J – 2, run it, and then print it.
n. Create a select query that sums the cost of all products by category
(in currency format), sorted by type. (Hint: Modify the Query J – 1
you previously created by adding the field Type. Save the new
query as K – 1.) Your new query should use Query K – 1 as its
source and have two fields: Type and Cost. Save the new query as
Query K – 2, run it, and then print it.
o. Make sure you keep a copy of this file for use in the next chapter
CHAPTER 12 FORMS
Chapter 12 Questions
1 What view of a form is used to edit an existing form?
2 What is the difference between bound and unbound controls?
3 Why are text box controls used on a form?
4 Why are list box controls used on a form?
5 Why are combo box controls used on a form?
6 Why are calculated controls used on a form?
7 Why are check box controls used on a form?
8 When is the Lookup Wizard used?
9 Why must an existing relationship be deleted before modifying a field’s
data type?
10 What type of relationship must exist if a subform is used on a form?
Chapter 12 Assignments
1 Using the Ch12-02 file, modify the Buyers form for What SUP so that it
looks like Figure 12.40. Save this form as Ch 12 Assignment 1, and print
record 1 using this form.
2 Modify the Products form by changing the Category ID text box control
to a list box control that lists the choices as A, B, C, D, E, or F. Save
the form as Ch 12 Assignment 2. Print the newly created form for Product
ID 101.
3 Modify the Product form (not the Ch 12 Assignment 2 form you just created)
by changing the Category ID text box control to a combo box control
that lists choices of A, B, C, D, E, or F. Save the form as Ch 12
Assignment 3. Print the newly created form for Product ID 101.
4 Salespeople earn a 10 percent commission on the price of each sale. Create
a new unbound calculated control to compute the possible commission
on each product. Modify the Products form (not the Ch 12
Assignment 3 form you just created) by adding a calculated control
called Commission. (The expression created should multiply the unit
price by .10.) Format the control in a currency format. Save the form as
Ch 12 Assignment 4. Print record 7 using this form.
5 Add a new field (Taxable) to the Product Table. Make the field a Yes/
No data type. Add this new field to the Products form (not the Ch 12
Assignment 4 form you just created) to the right of the Reorder Level
control. Make record 6 (Glide GS) taxable. Save the form as Ch 12
Assignment 5. Print record 6 using this form.
6 Modify the Products form (not the Ch 12 Assignment 5 form you just
created) by changing the Category ID text box control to a special combo
box control, listing the choices of CategoryID and CategoryName from
the Category table. (Hint: Be sure to first remove the relationship
between the Product and Category tables and then, after you’ve created
the combo box control, to reestablish the relationship and related referential
integrity. Save the form as Ch 12 Assignment 6. Print the newly created
form for Product ID 101.
7 For each buyer name, create a form (using the Forms Wizard) that contains
the product name, quantity, and unit price of each product that the
buyer is responsible for. Use the subform option with a datasheet layout.
Save the form as Ch 12 Assignment 7 and the subform as Ch 12 Assignment
7 Subform. Format the subform so that columns fit in the form created.
Print record 4 (Buyer ¼ Fortier) using this form.
Chapter 12 Case Problem 1:
KELLY’S BOUTIQUE
Note: You must have completed Case 1 in the previous chapter in order to continue
working on this case.
In the last chapter you added and modified some queries for Kelly’s
Boutique. She would now like you to create some new forms. Make the following
changes for Kelly using the ch11-03_student_name file you created in
Chapter 11. (Note: Open your ch11-03_student_name file and then save it as
ch12-03_student_name before making the indicated changes.)
a. Create a form to look like Figure 12.41. Save the form as Ch 12 Kelly
Case a. Print record Hop on Pop.
b. Create a list box control using the form created in part a. This control
should provide the user with a list of departments: Adult, Children.
Move the picture control down to make room for the list box control. Save the modified form as Ch 12 Kelly Case b. Print record 7 (Green
Eggs and Ham).
c. Create a combo box control using the form created in part a. This
control should provide the user with a list of departments (as in b
above) but with a combo box instead of a list box. Dept choices
should be Adult, Children, and Teen. Save the modified form as Ch
12 Kelly Case c. Print the documenter page that documents the combo
box for Dept.
d. Create a special combo box control using the Book Form. This control
should provide the user with a dynamic list of publishers based on the
publishers listed in the Publisher table. The combo box should list
publisher number and name, and referential integrity must be
enforced. Label the combo box column Publisher. Save the modified
form as Ch 12 Kelly Case d. Print the documenter page that documents
the combo box for Publisher.
e. Using the Book Form, create a calculated control to compute the total
retail value of each book in stock. First add the quantity field to the
form and then add the calculated control. Format the control in currency,
and label the control Total Retail Value. Save the modified
form as Ch 12 Kelly Case e. Print the record containing the book title
‘‘Angela’s Ashes’’
f. Using the Book Form, create a check box control to indicate whether
or not the text is a bestseller. Label the field and control Best Seller.
Mark the book Green Eggs and Ham as a bestseller using your new
form. Print the record that contains the book title ‘‘Green Eggs and
Ham’’ and then save the modified form as Ch 12 Kelly Case f.
g. Create a form and subform that shows all books by publisher, listing
ISBN and Book Title in datasheet layout. Then save the modified
form and subform as Ch 12 Kelly Case g and Ch 12 Kelly Case gSubform. Print the record with Arthur A. Levine Books as the publisher.
h. Make sure to keep a copy of this file for use in the next chapter.
Chapter 12 Case Problem 2:
WINE DEPOT
Note: You must have completed Case 2 in the previous chapter in order to continue
working on this case.
In the last chapter you modified some queries for the Wine Depot. Now Barbara
would like you to help her create some new forms. Make the following
changes for Barbara using the ch11-04_student_name file you created in
Chapter 11. (Note: Open your ch11-04_student_name file and then save it as
ch12-04_student_name before making the indicated changes.)
a. Change the Type field in the Wine Products table to a short text type
with a field size of 30. Use the Forms Wizard to create a new form from
the Wine Products table and Winery Table that includes the following
fields: SKU, Cost, Price, Quantity, Size, Vintage, Type, Picture, and
Winery Name. Use a justified layout and entitle the form Wine Products;
then modify the form to look like Figure 12.42. Save this form as
Ch 12 Wine Depot Case a. Navigate to SKU 14539 and print that
selected record only. (Hint: Be sure to set the picture properties field to
size mode ‘‘zoom’’ by right-clicking the picture object in the design
view, selecting properties, and choosing zoom as the size mode.)
b. Modify the form you created and saved in a above by changing the
Type text box to a list box control that provides a list of all wine
types: Cabernet Sauvignon, Chardonnay, Fume Blanc, Merlot, Pinot
Noir, Sauvignon Blanc, Syrah, Red Chianti, and Zinfandel. Save the
modified form as Ch 12 Wine Depot Case b. Print the record containing
SKU 12895 only.
Figure 12.42
New Wine Products
Formc. Modify the form you created in part b by replacing the Winery Name
field with a Winery field (from the Winery Table). Then change the
Winery text box to a combo box control that provides a drop-down
list of all 21 wineries. Your new combo box should contain a value
list that you create listing each winery by number. For example: 1,2,3
etc. Save the modified form as Ch 12 Wine Depot Case c. Print the
documenter page that documents the combo box for Winery.
d. Modify the form you created in part c by deleting the Winery combo
box control and replacing it with a Winery combo box control based
on the winery and name fields in the Winery Table. The field should
use the winery and name fields of the Winery Table and then list the
winery name when requested. (Don’t forget to modify the relationships
window first and then to reestablish referential integrity!) Save
the modified form as Ch 12 Wine Depot Case d. Print the documenter
page that documents the new combo box for Winery.
e. Modify the form you created in part d by adding a check box control
in the lower right-hand corner of the form to indicate if this product
has been recommended by the Wine Spectator (a noted magazine of
wine-tasting professionals). Label the field and control ‘‘Wine Spectator
Recommended’’. (Hint: Don’t forget to create a field in the Wine
Products Table first.) Only three wines currently have that designation:
SKU 11475, 13883, and 15347. Save the modified form as Ch 12
Wine Depot Case e, and print the record containing SKU 15347 only.
f. Modify the form you created in part e by adding a calculated control
below the Winery Number to compute the total cost in inventory of
each product (cost times quantity). Format the control in currency and
label the control Total Cost. Save the modified form as Ch 12 Wine
Depot Case f. Print the record containing SKU 11475 only.
g. Create a new form with a subform using the Winery Table and Wine
Products Table. Use the Winery Name field from the Winery Table
and the SKU and Type fields from the Wine Products table. View the
data by winery in a datasheet layout. Use the default names provided
for the form and subform. Save the modified form as Ch 12 Wine Depot
Case g. Print the record containing the Melville winery only.
h. Make sure you keep a copy of this file for use in the next chapter.
Chapter 12 Case Problem 3:
SNICK’S BOARD SHOP
Note: You must have completed Case 3 in the previous chapter in order to continue
working on this case.
In the last chapter you created some queries for the Snick’s Board Shop.
Now Caitlin would like you to help her create some forms to view information
from the database. Make the following changes for Caitlin using the
ch11-05_student_name file you created in Chapter 11. (Note: Open yourch11-05_student_name file and then save it as ch12-05_student_name before
making the indicated changes.)
a. Note the existing relationships between the Product, Manufacturer, and
Category tables. Now delete those relationships, and then change the
field size of ProductID to 10, ProductName to 35, ManufacturerID to
10, Style to 15, CategoryName to 25, ManfacturerName to 25, and
PhoneNumber to 15. Now reestablish the relationships you previously
deleted enforcing referential integrity. Use the Forms Wizard to create a
new form that includes the ProductID, ProductName, CategoryName,
ManufacturerName, Price, Style, Quantity, and Picture fields. View the
data by Product in a Columnar layout. Name your form Form A and
then navigate to ProductID 62-01296 and print that record only.
b. Modify the form you created and saved in (a) by changing the
CategoryName text box to a list box control that provides a list of all
Categories: Complete, Ramp, Longboards, Protective Gear, Rails, and
T-Shirts. Move the fields below CategoryName down so all are viewable
on the form. Change the title of the form to Form B in the form
header and then save the modified form as Form B. Print the record
containing 65-00335 only.
c. Create a new form containing all of the fields from the product table in
columnar layout with a title of Form C.Make the ManufacturerID a combo
box control based on the ManufacturerID field of the Manufacturer table.
Print the documenter page that documents the new combo box.
d. Add a new data type Yes/No field titled Green to the Manufacturer
table and then add a new form containing all the fields in the Manufacturer
table in columnar format titled Form D. The new Green field
designates whether a manufacturer has qualified for the energy-saving
status granted by the state. Currently only Zero, Anti-Hero, and Goldcoast
are designated ‘‘Green’’ by the state. Place checks in the Green
fields for those three manufacturers. Print the Anti-Hero record.
e. Create a new form containing the ProductID, ProductName, Price,
Discount, and Picture fields viewed by Product in a Columnar layout
titled Form E. Delete the Picture label and move the picture control to
the right hand side of the form. Resize the ProductName and Price
fields so that your form looks like Figure 12.43:
Now add a new calculated control titled DiscountedPrice and formatted
as currency below the Discount field to compute the discounted
price (Price less discount, if any). Print the record for ProductID
65-00358.
f. Create a new form with a subform containing the fields Category-
Name, ProductName, Price, and Style viewing the data by Category is
a Datasheet layout and title Form F. Resize the fields displayed in the
subform and then print the form for the LongBoard category.
g. Make sure you keep a copy of this file for use in the next chapter.
Chapter 12 Case Problem 4:
ROSEY’S ROSES
Note: You must have completed Case 4 in the previous chapter in order to continue
working on this case. In the last chapter, you created some queries for
Rosey’s Roses. Now the company would like you to help it create some forms
to illustrate information from the database. Make the following changes using
the ch11-06_student_name file you created in Chapter 11. (Note: Open your
ch11-06_student_name file and then save it as ch12-06_student_name before
making the indicated changes.)
a. Note the existing relationships between existing tables. Now delete
those relationships, and then change the field size of ID, Quantity,
Quote Number, Quantity Ordered, and GrowerID to integer with 0
decimal places, Cost/Unit to currency with 2 decimal places, Customer
Number to a field size of 5, Type to field size of 10, and Grower
Name and Contact to field size 40. Now reestablish the relationships
you previously deleted enforcing referential integrity. (Hint: Be sure
to change all instances of the fields you just changed.) Use the Forms
Wizard to create a new form that includes the ID, Product Description,
Type, type Description, Grower Name, Quantity, and Picture fields.
View the data in a columnar layout with no grouping. Save your form
as Form A and then navigate to ID 6 and print that record only.
b. Modify the form you created and saved in (a) by changing the Type
field text box to a list box control that provides a list of all Types:
Climber, Creeper, Shrub, and Tree. Move the fields below Type down
so all are viewable on the form. Change the title of the form to Form
B in the form header and then save the modified form as Form B.
Print the record containing product ID 10 only.
Figure 12.43
Form E as Modifiedc. Create a new form containing all of the fields from the Product table
in columnar layout with a title of Form C. Make the Grower ID a
combo box control that provides a list of all Grower IDs: 100, 200,
300, and 400. Print the documenter page that documents the new
combo box control.
d. Modify the form you created and saved in (c) by removing the existing
GrowerID field combo box control and creating a new GrowerID
field special combo box control based on the GrowerID and Grower
Name fields of the Grower table. The field should use the GrowerID
and Grower Name fields of the Grower table and then just list the
Grower Name when activated. (Don’t forget to modify the relationships
first and then re-establish referential integrity.) Change the title
of the form to Form D and then save the modified form as Form D;
then print the documenter page that documents the new special combo
box control.
e. Add a new data type Yes/No field titled Organically Grown to the
Grower table and then create a new form containing all the fields in
the Grower table in columnar format titled Form E. Currently only
Owen Farms is designated ‘‘Organically Grown’’ by the state. Place
checks in the Organically Green field for that grower. Print the Owen
Farms Form E.
f. Create a new form with a subform containing the fields Grower
Name, product Description, Cost/Unit, and Type viewing the data by
Grower in a Datasheet layout and form title Products by Grower and
subform title Product Subform. Resize the fields displayed in the subform
and then print the form for the Jackson & Perkins grower.
g. Make sure you keep a copy of this file for use in the next chapter.
CHAPTER 13 REPORTS
Chapter 13 Questions
1 Why create a report from a query? Why not just print your query
results?
2 Can you always add a field from any table to a report based on a query?
3 When creating a report, what does grouping accomplish?
4 When creating a report, what does the process of summarizing accomplish?
5 Is it possible to group more than one item?
6 What do the [ ] (brackets) signify in an Access formula?
7 How do you determine where information appears in a report?
8 What type of control is used in a report to add values?
9 What function is used (and how is it written) to count records in a
report?
10 Describe the process of adding lines to a report.
Chapter 13 Assignments
1 Using the Ch13-02 file, create a report using all the fields present in the
Buyer Query. View the data by Product Table with no grouping or summarization
in a portrait orientation and adjusting the field width so all
fields fit on a page. Title and save the new report as Ch 13 Assignment
1. Print the new report.
2 Create a report using the BuyerName, ProductName, Quantity, UnitCost,
and TotalCost fields present in the Buyer query. View the data by Buyers
Table and group by BuyerName. Use the summary option feature of
Access to the sum TotalCost, showing detail and summary in a stepped
layout with portrait orientation and adjusting the field width so all fields
fit on a page. Title and save the new report as Ch 13 Assignment 2.
Make sure all values are visible, and then print the new report.
3 Open the report created in Assignment 2. Change the title of the report
to Products by Buyer. Add lines to separate each buyer. Add a line above
the grand total amount. Save the new report as Ch 13 Assignment 3.
Make sure all values are visible and then print the new report. Your
printed report should look like Figure 13.19.
Chapter 13 Case Problem 1:
KELLY’S BOUTIQUE
Note: You must have completed Case 1 in the previous chapter in order to continue
working on this case.
In the last chapter you added and modified some forms for Kelly’s Boutique.
She would now like you to create some new reports. Make the following
changes for Kelly using the ch12-03_student_name file you created in Chapter
12. (Note: Open your ch12-03_student_name file and then save it as ch13-
03_student_name before making the indicated changes.)
Figurea. Kelly would like you to modify an existing query (Ch 11 Kelly Case
h) to include the publisher name and then to save the new query as
Ch 11 Kelly Case o. Use this new query to generate a report listing
all of the field names provided in the query and grouped by publisher,
sorted by book title, with no summarizing, in a stepped layout with
portrait orientation. Save the newly created report as Ch 13 Kelly
Case a and then print the report, making sure that all fields of information
are clearly readable before printing.
b. Next, Kelly would like you to add some grouping to the report created
in Case 1a. Rather than modify the existing report, create a new report
using the Ch 11 Kelly Case o query to generate a report listing only
the publisher, book title, quantity, and cost, grouped by publisher and
sorted by book title, summing cost in a stepped layout with portrait
orientation. Save the newly created report as Ch 13 Kelly Case b and
then print the report, making sure that all fields of information are
clearly readable before printing.
c. Next, Kelly would like you to use the parameter query created previously
(Ch 11 Kelly Case l) to create a report containing the fields
book title and quantify for a specified publisher and viewed by Book
Table, sorted by book title, with portrait orientation. Save the newly
created report as Ch 13 Kelly Case c. Add a field to count the number
of titles for the publisher specified and then sum the quantity of books
for that publisher. Add line controls, change the report title, and move
controls around so that your report looks like Figure 13.20.
Run the report for Random House and then print the report, making sure that
all fields of information are clearly readable before printing.
Chapter 13 Case Problem 2:
WINE DEPOT
Note: You must have completed Case 2 in the previous chapter in order to continue
working on this case.
Figure 13.20
Report Example
for Kelly Case CIn the previous chapter you modified some forms for the Wine Depot. Now
Barbara would like you to help her create some new reports. Make the following
changes for Barbara using the ch12-04_student_name file you created in
Chapter 12. (Note: Open your ch12-04_student_name file and then save it as
ch13-04_student_name before making the indicated changes.)
a. Use the Report Wizard to create and print a new report based on the
query created in Chapter 11 (Ch 11 Wine Depot Case b),which listed
all wine products purchased by Michael Jackson. Include all the fields
available from the query, view the data by wine products with no
grouping, sorted by type in ascending order, with portrait orientation.
Title the report Ch 13 Wine Depot Case a and then print the report,
making sure that all fields of information are clearly readable before
printing.
b. Use the Report Wizard to create and print a new report that lists the
following fields: buyer name, type, winery name, vintage, and quantity.
View your data by buyer and then by winery name, in ascending
order by type, in stepped layout with portrait orientation. Title the
report Ch 13 Wine Depot Case b and then print only page 1 of the
report, making sure that all fields of information are clearly readable
before printing.
c. Create a new report of product total cost by winery. Using your previous
query (Ch 11 Wine Depot Case k) as the basis for this report,
include all the fields in that query and view them by winery. Sort the
detail by type in ascending order, and add a summary option for total
cost (in a Currency format) that shows detail and summary in a
stepped layout with portrait orientation. Title the report Ch 13 Wine
Depot Case c and then print only the last page of the report, making
sure that all fields of information are clearly readable before printing.
d. Modify your previous query (Ch 11 Wine Depot Case k) to be a parameter
query by asking the user to specify a type of wine. Save the
new query as Ch 11 Wine Depot Case m. Use that query to create a
report of costs by type of wine. The report should include all the
fields from the query except type. View the data by winery name, in
ascending order by SKU, and in a stepped layout with portrait orientation.
The report should calculate the sum of total cost for the wine
selected. Run the report for Pinot Noir wines. Modify the report to
look like Figure 13.21 by adding a line and a Grand Total in the
report footer. Then title the report as Ch 13 Wine Depot Case d and
print it, making sure that all fields of information are clearly readable
before printing.
Chapter 13 Case Problem 3:
SNICK’S BOARD SHOP
Note: You must have completed Case 3 in the previous chapter in order to continue
working on this case.
In the previous chapter you modified some forms for Snick’s Board Shop.
Now Caitlin would like you to help her create some new reports. Make the following
changes using the ch12-05_student_name file you created in Chapter 12.
(Note: Open your ch12-04_student_name file and then save it as ch13-05_student_
name before making the indicated changes.)
a. Use the Report Wizard to create and print a new report based on the
query created in Chapter 12 (Query B), which included all products in
CategoryID 1. Include all the fields available from the query, view the
data by products with no grouping, sorted by manufacturer name in
ascending order, in a tabular layout with portrait orientation. Title the
report Report A and then print the report, making sure that all fields
of information are clearly readable before printing.
b. Use the Report Wizard to create and print a new report that lists the following
fields: ManufacturerName, CategoryName, ProductName, and
Price. View your data by manufacturer and then by category, in ascending
order by ProductName, in stepped layout with portrait orientation.
Title the report Report B and then print only page 1 of the report, making
sure that all fields of information are clearly readable before printing.
c. Create a new parameter query that includes the CategoryName, ManufacturerName,
ProductName, Quantity, and Price for a CategoryName
specified when the query is run. Name this query Query L and then
create a new report containing the ManufacturerName, ProductName,
Quantity, and Price for a specified CategoryName and viewed by
ManufacturerName, sorted by ProductName, with portrait orientation.
Be sure to include a summary option which sums the quantity field in
detail and summary. Save the newly created report as Report C. Run
the report for CategoryName Complete and for CategoryName Longboards,
and then print both reports.
Figure 13.21
Total Cost of Wines by
Named. Create a new query that includes the CategoryName, ProductName,
Quantity, Price, and Discount fields. Add a calculated field called DiscountedRetailValue
to this query, which computes the quantity times
the discounted price (Price x (1-Discount)) formatted as currency.
Save the new query as Query M.
e. Now create a report based on Query M that lists the CategoryName,
ProductName, and DiscountedRetailValue grouped by CategoryName,
sorted by ProductName with a sum of DiscountedRetailValue in the
detail and summary portions of the report. Save the new report as
Report D.
Chapter 13 Case Problem 4:
ROSEY’S ROSES
Note: You must have completed Case 4 in the previous chapter in order to continue
working on this case. In the last chapter you created some forms for the
Rosey’s Roses. Now the company would like you to help it create some reports
to extract information from the database. Make the following changes using the
ch12-06_student_name file you created in Chapter 12. (Note: Open your ch12-
06_student_name file and then save it as ch13-06_student_name before making
the indicated changes.)
a. Use the Report Wizard to create and print a new report based on
Query D. Include all the fields available from the query, view the data
by Grower with no grouping, sorted by product Description in ascending
order, in a stepped layout with portrait orientation. Title the report
Report A and then print the report, making sure that all fields of information
are clearly readable before printing.
b. Use the Report Wizard to create and print a new report that lists the following
fields: Grower Name, Type, product Description, and Cost/Unit.
View your data by Grower and then by Type, in ascending order by
product Description, in stepped layout with portrait orientation. Title the
report Report B and then print only page 1 of the report, making sure
that all fields of information are clearly readable before printing.
c. Create a new parameter query that includes the Type, Grower Name,
product Description, Quantity, and Cost/Unit which asks ‘‘For which
type?’’ when the query is run. Name this Query L and then create a
new report containing the Grower Name, product Description, Quantity,
and Cost/Unit for a specified Type and viewed by Grower Name,
sorted by product Description, with a stepped layout and portrait orientation.
Be sure to include a summary option which sums the Quantity
field in detail and summary. Save the newly created report as
Report C. Run the report for Type Shrub and for Type Tree, and then
print both reports.
d. Create a new query that includes the Quote Number, Customer Name,
product Description, and Quantity Ordered fields. Add a calculatedfield called Sales Price which is the Cost/Unit times (1 plus Markup)
times (1 – Discount). Also include another calculated field called
Total Quote which computes the Quantity Ordered times the Sales
Price also formatted as currency and 2 decimal places. Save the new
query as Query M and then print it. (Hint: be sure to include the
Grower table in your query construction.)
e. Now create a report based on Query M that lists all the fields available
in that query in a tabular layout and landscape orientation. Save the
report as Report D. Using the Design View; delete the two existing
controls in the page footer. Now add two textbox controls. Add one
which sums the Quantity Ordered (general number format) and one
which sums the Quote Totals (currency format) in the report footer.
Also add a Totals label, and resize all labels and fields so that your
report looks like Figure 13.22.
Save the modified report and print it.
Figure 13.22
Quotes
No comments:
Post a Comment