14.5 The Joseph Brant Manufacturing Company makes athletic footwear. Processing of
production orders is as follows: At the end of each week, the production planning
department prepares a master production schedule (MPS) that lists which shoe
styles and quantities are to be produced during the next week. A production order
preparation program accesses the MPS and the operations list (stored on a
permanent disk file) to prepare a production order for each shoe style that is to be
manufactured. Each new production order is added to the open production order
master file stored on disk.
Each day, parts department clerks review the open production orders and the MPS
to determine which materials need to be released to production. All materials are
bar-coded. Factory workers work individually at specially designed U-shaped work
areas equipped with several machines to assist them in completely making a pair of
shoes. Factory workers scan the bar-codes as they use materials. To operate a
machine, the factory workers swipe their ID badge through a reader. This results in
the system automatically collecting data identifying who produced each pair of shoes
and how much time it took to make them.
Once a pair of shoes is finished, it is placed in a box. The last machine in each work
cell prints a bar-code label that the worker affixes to the box. The completed shoes
are then sent to the warehouse.
© 2015 Pearson Education, Inc.
1
Accounting Information Systems
a. Prepare a data flow diagram of all operations described.
2.0
Prepare
Production
Order
3.0
Perform
Production
Operation
inventory
sales forecasts
open
production
orders
operations list
bill of materials
Production
Orders
Scheduled
Production
MPS
1.0
Plan
Production
QOH
Work
Activity
Accounting Information Systems
b. What control procedures should be included in the system?
A large number of controls are possible, including the following:
Access Control – User ID and Password
Compatibility Test – Password
Preformatting or Prompting -All Data Entered
Record Count – # of Transactions
4
© 2015 Pearson Education, Inc.
Accounting Information Systems
14.6 The XYZ company’s current production processes have a scrap rate of 15% and a
return rate of 3%. Scrap costs (wasted materials) are $12 per unit; warranty/repair
costs average $60 per unit returned. The company is considering the following
alternatives to improve its production processes:
Option A: Invest $400,000 in new equipment. The new process will also require
an additional $1.50 of raw materials per unit produced. This option is predicted
to reduce both scrap rates return rates by 40% from current levels.
Option B: Invest $50,000 in new equipment, but spend an additional $3.20 on
higher quality raw materials per unit produced. This option is predicted to
reduce both scrap and return rates by 90% from current levels.
Option C: Invest $2,000,000 in new equipment. The new process will require no
change in raw materials. This option is predicted to reduce both scrap and
return rates by 50% from current levels.
a. Assume that current production levels of 1,000,000 units will continue. Which
option do you recommend? Why?
At current production levels of 1,000,000 units, none of the options reduce total costs, but
option B results in the smallest increase in total costs.
Option A:
Option B:
Option C:
4
© 2015 Pearson Education, Inc.
Accounting Information Systems
b. Assume that because all of the proposed changes will increase product quality,
that production will jump to 1,500,000 units. Which option do you recommend?
Why?
At production levels of 1,500,000 units, options B and C both reduce total costs. Option
C, however, reduces them the most.
Option A:
Option B:
Option C:
4
© 2015 Pearson Education, Inc.
14.7 EXCEL PROBLEM
a. Download the spreadsheet for problem 14.7 from the website for this textbook.
b. Create formulas to calculate
Accumulated depreciation (all assets use the straight line method; all assets acquired any time during the year get
a full year’s initial depreciation)
Current year’s depreciation (straight-line method, full amount for initial year in which asset acquired)
Ending accumulated depreciation
Net book value at end of period
Current year in the cell to the right of the phrase “Depreciation schedule for year”
Column totals for acquisition cost, beginning depreciation, current depreciation, ending accumulated depreciation,
net book value
In the cell to the right of the arrow following the text “Cross-footing test” create a formula that checks whether the
sum of the net book value column equals the sum of acquisition costs minus the sum of ending accumulated
depreciation. If the two values match, the formula should display the text “Okay” otherwise it should display the
text “Error”
c. Create a table at the bottom of your worksheet that consists of two columns:
Asset name (values should be chair, desk, laptop, monitor, software, and workstation)
Net book value (create a formula to calculate this number) assuming that the current date is 06/30/2018
Create a formula that sums the total net book values for all classes of assets
In the cell to the right of the total net book values for all asset classes, create a formula that compares the total net
book values for all classes of assets to the sum of all net book values in the top portion of the spreadsheet. The
formula should return “Okay” if the two totals match or “Error: Sum of net book values by asset class does not
equal sum of all net book values” if the two totals do not equal one another.
d. Enter your name in row 1 in the cell to the right of the text “Name”
© 2015 Pearson Education, Inc.
6
Accounting Information Systems
4
© 2015 Pearson Education, Inc.
Accounting Information Systems
Note: this solution was created assuming that the current year is 2018. Therefore, when using the problem in subsequent years, you may
want to have students increment all years initially placed in service by one in order to have the same solution OR edit the solution to
reflect that assets have less remaining life than assumed here.
Useful formulas:
Current year: =YEAR(TODAY()) – this calculates the current year. NOTE: The solution increments the value in cell $E$2 by 1
so that it can be used in fall 2017 yet mimic being June 30, 2018.
Excel has a built-in function for computing straight-line depreciation: SLN. The SLN function takes three arguments: cost,
salvage value, and estimated life: SLN(cell with cost, cell with salvage value, cell with estimated life).
Beginning accumulated depreciation equals the minimum of actual accumulated depreciation, if the asset is not yet at the end of
Current period depreciation is either the result of the straight-line depreciation calculation, if the asset has not yet been fully
Ending Accumulated depreciation: =H5+I5
Net book value equals acquisition cost less accumulated depreciation. Thus for the desk (account 11001) the formula is
Cross-foot test (result should appear in cell G20): =IF(K19=F19-J19,”Okay”,”Error”)
Net book values for asset classes: =SUMIF($C$5:$C$17,C22,$K$5:$K$17) – copy down, note that second term in the formula
Cross-foot check of net book values by class versus by asset (result should appear in cell G28): =IF(D28=K19,”okay”,”Error:
4
© 2015 Pearson Education, Inc.
Accounting Information Systems
14.8 EXCEL PROBLEM
Task: Use Excel and the Solver add-in to explore the effect of various resource constraints on the optimal product mix.
a. Read the article “Boost Profits With Excel,” by James A. Weisel in the December 2003 issue of the Journal of
Accountancy (available online at the AICPA’s Web site, www.aicpa.org
b. Download the sample spreadsheet discussed in the article and print out the screenshots showing that you used the
Solver tool as discussed in the article.
4
© 2015 Pearson Education, Inc.
Accounting Information Systems
This brings up the following pop-up window. Select the “Solver Add-in” and click OK.
4
© 2015 Pearson Education, Inc.