Instructions for the Microsoft Excel Templates b y Rex A Schildhouse
B e a d v i s e d , t h e t em e m p l a te t e w o r k b o o k s a n d w o r k s h e e t s a r e n o t p r o t e c t ed ed . O v e r ty ty p i n g a n y d a t a m a y r e m o v e i t . Extensive detail and information is contained within the help function of Microsoft Excel and in the provided text. You should enter your name, date, instructor's name, and course into the cells at the top of the page. This information will be printed on the top of each page if the template requires more more than one page.
Each template is set to print with File Name, Page # of # Page(s), the print date, and the print time to assist in assembly of multiple pages. If more than one page is required by the template, manual page breaks have been set to provide consistent presentation. All of the cells have been correctly formatted for presentation and should not require any adjustment. For example, if the text requires one, two, or three significant digits in a presentation, the template has been set for that presentation in the appropriate cells. In general, the yellow highlighted cells are the cells which work and effort should be presented. These entries may include date(s), account title(s), values, memorandum appropriate to the entry, or t ext answers to questions. And information or data which may be required by the solution will be entered in cells with borders to help identify them. Where a yellow highlighted cell shows "Date" enter the appropriate date for that step of the challenge. This may be any date format that Microsoft Excel accepts. Some of these formats include "1/1/12", "01/01/ 12", and "01/01/2012. " All of these will return January 01, 2012, in the format set in the t emplate. Where a yellow highlighted cell shows "Acct Nbr" enter the appropriate account number, provided in the template and in the text for that step of the challenge. This is entry may be a "Look to" formula to another cell where that information has been provided or previously entered. Where a yellow highlighted cell shows "Account Title" enter the appropriate account title for that step of the challenge. This is a text entry and most of those cells are set for the proper indentation for that step. Frequently the chart of accounts appropriate to the challenge is provided and you can use the " look to" formula to reference the appropriate account title without typing it. Check with your instructor to see if abbreviated account titles are acceptable. For example "A/R" for Accounts Receivable, "A/P" for Accounts Payable. If your instructor is using a comparison process between workbooks for grading, these abbreviates may not be acceptable. Where a yellow highlighted cell shows titles such as "Values," "Amounts," or "Quantities" enter t he appropriate numerical value for that step of the challenge. The cell is formatted for proper presentation of the entered information. If a dollar sign is appropriate, it should not be entered, Microsoft Excel will place it there through formatting. Commas and significant digits (decimals) are also set through formatting for common presentation. presentation. Since the formatting of the templates is not protected by an assw asswor ord, d, ou ma chan chan e an of the the form format attin tin foun found d in the the tem tem late latess to meet meet our our desi desire res. s. Where a yellow highlighted cell shows titles such as "Formula" you may enter the appropriate formula or enter a numerical value appropriate for that step of the challenge. Most of the values necessary for for the appropriate formula are located on the template in cells with borders or in other yellow highlighted cells. The formula may be a simple "Look to" formula, an equal sign and a cell reference, "=E27" or more complex as "=E27*5," or something similar to the time-value-of-money time-value-of-money formula.
These are addressed in the tutorial text provided for Microsoft Excel. Where a yellow highlighted cell shows "Text" enter the appropriate text for that step of the challenge. This may be a memorandum entry for a journal entry or a lengthy text answer discussing the results of an analysis of a company's financials. These titles can simply be typed over. Where a yellow highlighted cell shows titles such as "Journal Number" or "Journ #" you should enter the appropriate number provided in the template and in the text for that step of the challenge. In general this will appear in instances such as "Record the following events in General Journal number six." The print area is defined to fit onto 8 1/2" × 11" sheets in portrait or landscape mode as required. Margins are generally set to no less than 1/2" so most printers can print them without a problem. If you printer cannot accept margins less than 1" you may have to reformat the margins through Page Setup. The display may have "Freeze Pane" invoked so column titles remain visible during data entry. This can be removed by utilizing the View menu and selecting "Unfreeze Panes" under "Freeze Panes." When negative values are required, enter t hem by starting with a minus sign, "-". Negative values may be shown as ($400) or -$400. Negative values in formulas can be created by putting a minus sign in front of the cell reference - "=E10*-E11" will return a negative value if both cells E10 and E11 contain positive values. Microsoft Office and Microsoft Excel are products of, and copyrighted by, Microsoft Corporation, One Microsoft Way, Redmond, Washington 98052-6399
Solution Name: Date: Instructor: Course: th Intermediate Accounting, 14 Edition by Kieso, Weygandt, and Warfield Primer on Using Excel in Accounting by Rex A Schildhouse E9-1 (Lower-of-Cost-or-Market) The inventory of Oheto Company on December 31, 2013, consists of
the following items. Part No. Quantity Cost Per Unit Cost to Replace per Unit 110 600 $95 $100.00 111 1,000 60 $52.00 112 500 80 $76.00 113 200 170 $180.00 120 400 205 $208.00 121 1,600 16 $14.00 122 300 240 $235.00 Part No. 121 is obsolete and has a realizable value of each as scrap: $0.50
Part No.
Quantity
110 111 112 113 120 121 122 Totals
Per Unit Cost
600 1,000 500 200 400 1,600 300
$95 60 80 170 205 16 240
Market $100.00 52.00 76.00 180.00 208.00 14.00 $235.00
Total Cost $57,000 60,000 40,000 34,000 82,000 25,600 72,000 $370,600
Total Market $60,000 52,000 38,000 36,000 83,200 800 70,500 $340,500
Lower of Cost or Market $57,000 52,000 38,000 34,000 82,000 800 70,500 $334,300
Instructions:
Complete the table above by inserting the correct values or formulas into the yellow highlighted cells. From this data, answer the following two questions: (a) Determine the inventory as of December 31, 2013, by the lower-of-cost-or-market method, applying
this method directly to each item. The valuation of inventory as of December 31, 2013, by the lower of cost or market method, as applied directly to each item is:
$334,300
(b) Determine the inventory by the lower-of-cost-or-market method, applying the method to the total
of the inventory. The valuation of inventory as of December 31, 2013, by the lower of cost or market method, as applied to total inventory is:
$340,500
153010892.xlsx.ms_office, Exercise 9-1 Solution, Page 3 of 12, 6/20/2013, 6:59 AM
Name: Date: Instructor: Course: th Intermediate Accounting, 14 Edition by Kieso, Weygandt, and Warfield Primer on Using Excel in Accounting by Rex A Schildhouse E9-1 (Lower-of-Cost-or-Market) The inventory of Oheto Company on December 31, 2013, consists of
the following items. Part No. Quantity Cost Per Unit Cost to Replace per Unit 110 600 $95 $100.00 111 1,000 60 $52.00 112 500 80 $76.00 113 200 170 $180.00 120 400 205 $208.00 121 1,600 16 $14.00 122 300 240 $235.00 Part No. 121 is obsolete and has a realizable value of each as scrap: $0.50
Part No. 110 111 112 113 120 121 122 Totals
Quantity
Per Unit Cost
600 1,000 500 200 400 1,600 300
$95 60 80 170 205 16 240
Market $100.00 52.00 76.00 180.00 208.00 14.00 $235.00
Total Cost Formula Formula Formula Formula Formula Formula Formula Formula
Total Market Formula Formula Formula Formula Formula Formula Formula Formula
Lower of Cost or Market Formula Formula Formula Formula Formula Formula Formula Formula
Instructions:
Complete the table above by inserting the correct values or formulas into the yellow highlighted cells. From this data, answer the following two questions: (a) Determine the inventory as of December 31, 2013, by the lower-of-cost-or-market method, applying
this method directly to each item. The valuation of inventory as of December 31, 2013, by the lower of cost or market method, as applied directly to each item is:
Value
(b) Determine the inventory by the lower-of-cost-or-market method, applying the method to the total
of the inventory. The valuation of inventory as of December 31, 2013, by the lower of cost or market method, as applied to total inventory is:
Value
153010892.xlsx.ms_office, Exercise 9-1, Page 4 of 12, 6/20/2013, 6:59 AM
Solution Name: Date: Instructor: Course: th Intermediate Accounting, 14 Edition by Kieso, Weygandt, and Warfield Primer on Using Excel in Accounting by Rex A Schildhouse E9-7 (Relative Sales Value Method) Larsen Realty Corporation purchased a tract of unimproved land for
$55,000 . This land was improved and subdivided into building lots at an additional cost of $30,000 These building lots were all of the same size but owing to differences in location were offered for sale at different prices as follows. Group No. of Lots Price per Lot 1 9 $3,000 2 15 $4,000 3 19 $2,000 Operating expenses for the year allocated to this project total as follows: Group No. of Lots 1 5 2 7 3 2
$18,200
Lots unsold at the year-end
Instructions:
At the end of the fiscal year Larsen Realty Corporation instructs you to arrive at the net income realized on this operation to date.
Group 1 2 3
No. of lots 9 15 19
Sales price per lot $3,000 4,000 2,000
Total sales price $27,000 60,000 38,000 $125,000
Price per lot $3,000 4,000 2,000
Total selling price $12,000 32,000 34,000 $78,000
Relative sales price as % 22% 48% 30% 100%
Cost total $85,000 85,000 85,000
Cost allocated to lots $18,360 $40,800 $25,840 $85,000
Lots Sold Group 1 2 3
No. of Lots 4 8 17 29
Sales (see schedule) Cost of goods sold (see schedule) Gross profit Operating expenses Net income
Cost per lot $2,040 2,720 1,360
Extended cost $8,160 21,760 23,120 $53,040
Gross Profit $3,840 $10,240 10,880 $24,960
$78,000 53,040 24,960 18,200 $6,760
153010892.xlsx.ms_office, Exercise 9-7 Solution, Page 5 of 12, 6/20/2013, 6:59 AM
Cost per lot $2,040 $2,720 $1,360
Name: Date: Instructor: Course: th Intermediate Accounting, 14 Edition by Kieso, Weygandt, and Warfield Primer on Using Excel in Accounting by Rex A Schildhouse E9-7 (Relative Sales Value Method) Larsen Realty Corporation purchased a tract of unimproved land for
$55,000 . This land was improved and subdivided into building lots at an additional cost of $30,000 These building lots were all of the same size but owing to differences in location were offered for sale at different prices as follows. Group No. of Lots Price per Lot 1 9 $3,000 2 15 $4,000 3 19 $2,000 Operating expenses for the year allocated to this project total as follows: Group No. of Lots 1 5 2 7 3 2
$18,200
Lots unsold at the year-end
Instructions:
At the end of the fiscal year Larsen Realty Corporation instructs you to arrive at the net income realized on this operation to date.
Group 1 2 3
No. of lots Number Number Number
Sales price Total per lot sales price Amount Amount Amount Amount Amount Amount Formula
Relative sales price as % Formula Formula Formula Formula
Cost total Amount Amount Amount
Cost allocated to lots Cost per lot Formula Formula Formula Formula Formula Formula Formula
Lots Sold Group 1 2 3
No. of Lots Number Number Number Formula
Price Total selling per lot price Cost per lot Amount Formula Amount Amount Formula Amount Amount Formula Amount Formula
Sales (see schedule) Cost of goods sold (see schedule) Gross profit Operating expenses Net income
Extended cost Formula Formula Formula Formula
Gross Profit Formula Formula Formula Formula
Amount Amount Formula Amount Formula
153010892.xlsx.ms_office, Exercise 9-7, Page 6 of 12, 6/20/2013, 6:59 AM
Solution Name: Date: Instructor: Course: th Intermediate Accounting , 14 Edition by Kieso, Weygandt, and Warfield Primer on Using Excel in Accounting by Rex A Schildhouse P9-2 (Lower-of-Cost-or-Market) Garcia Home Improvement Company installs replacement siding, windows, and
louvered glass doors for single-family homes and condominium complexes in northern New Jersey and southern New York. The company is in the process of preparing its annual financial statements for the fiscal year ended May 31, 2012, and Jim Alcide, controller for Garcia, has gathered the following data concerning inventory. $408,000 and the Allowance to Reduce Inventory to Market had a credit balance of $27,500 Alcide summarized the relevant inventory cost and market data at May 31, 2012, in the schedule below. Alcide assigned Patricia Devereaux, an intern from a local college, the task of calculating the amount that should At May 31, 2012, the balance in Garcia’s Raw Material Inventory account was
appear on Garcia’s May 31, 2012, financial statements for inventory under the lower-of-cost-or-market rule as
applied to each item in inventory. Devereaux expressed concern over departing f rom the cost principle.
Cost $70,000 86,000 112,000 140,000 $408,000
Aluminum siding Cedar shake siding Louvered glass doors Thermal windows Total
Replacement Cost $62,500 79,400 124,000 126,000 $391,900
Sales Price $64,000 94,000 186,400 154,800 $499,200
Net Realizable Value $56,000 84,800 168,300 140,000 $449,100
Normal Profit $5,100 7,400 18,500 15,400 $46,400
Instructions:
(a) (1) Determine the proper balance in the Allowance to Reduce Inventory to Market at May 31, 2012.
Calculations of Proper Balance on the Allowance to Reduce Inventory to Market At May 31, 2012.
Aluminum siding Cedar shake siding Louvered glass doors Thermal windows Totals
Cost $70,000 $86,000 $112,000 $140,000 $408,000
Replacement Cost $62,500 $79,400 $124,000 $126,000 $391,900
Inventory cost LCM valuation Allowance at May 31, 2012
NRV (Ceiling) $56,000 $84,800 $168,300 $140,000 $449,100
NRV less normal profit (Floor) $50,900 77,400 149,800 124,600 $402,700
LCM $56,000 $79,400 $112,000 $126,000 $373,400
$408,000 373,400 $34,600
153010892.xlsx.ms_office, Problem 9-2 Solution, Page 7 of 12, 6/20/2013, 6:59 AM
Name: Instructor:
Solution
Date: Course:
(a) (2) For the fiscal year ended May 31, 2012, determine the amount of the gain or loss that would be recorded
due to the change in the Allowance to Reduce Inventory to Market. For the fiscal year ended May 31, 2010, the loss that would be recorded due to the change in the Allowance to Reduce Inventory to Market would be $7,100, as calculated below. Balance prior to adjustment Required balance Loss to be recorded
$27,500 34,600 ($7,100)
(b) Explain the rationale for the use of the lower of cost or market rule as it applies to inventories.
The use of the lower of cost or market (LCM) rule is based on both the matching principle and the concept of conservatism. The matching principle applies because the application of the LCM rule allows for the recognition of a decline in the utility (value) of inventory as a loss in the period in which the decline takes place.
The departure from the cost principle for inventory valuation is permitted on the basis of c onservatism. The general rule is that the historical cost principle is abandoned when the future utility of an asset is no longer as great as its original cost.
153010892.xlsx.ms_office, Problem 9-2 Solution, Page 8 of 12, 6/20/2013, 6:59 AM
Name: Date: Instructor: Course: th Intermediate Accounting , 14 Edition by Kieso, Weygandt, and Warfield Primer on Using Excel in Accounting by Rex A Schildhouse P9-2 (Lower-of-Cost-or-Market) Garcia Home Improvement Company installs replacement siding, windows, and
louvered glass doors for single-family homes and condominium complexes in northern New Jersey and southern New York. The company is in the process of preparing its annual financial statements for the fiscal year ended May 31, 2012, and Jim Alcide, controller for Garcia, has gathered the following data concerning inventory. $408,000 and the Allowance to Reduce Inventory to Market had a credit balance of $27,500 Alcide summarized the relevant inventory cost and market data at May 31, 2012, in the schedule below. Alcide assigned Patricia Devereaux, an intern from a local college, the task of calculating the amount that should At May 31, 2012, the balance in Garcia’s Raw Material Inventory account was
appear on Garcia’s May 31, 2012, financial statements for inventory under the lower-of-cost-or-market rule as
applied to each item in inventory. Devereaux expressed concern over departing f rom the cost principle.
Cost $70,000 86,000 112,000 140,000 $408,000
Aluminum siding Cedar shake siding Louvered glass doors Thermal windows Total
Replacement Cost $62,500 79,400 124,000 126,000 $391,900
Sales Price $64,000 94,000 186,400 154,800 $499,200
Net Realizable Value $56,000 84,800 168,300 140,000 $449,100
Normal Profit $5,100 7,400 18,500 15,400 $46,400
Instructions:
(a) (1) Determine the proper balance in the Allowance to Reduce Inventory to Market at May 31, 2012.
Calculations of Proper Balance on the Allowance to Reduce Inventory to Market At May 31, 2012.
Aluminum siding Cedar shake siding Louvered glass doors Thermal windows Totals
Cost Amount Amount Amount Amount Formula
Replacement Cost Amount Amount Amount Amount Formula
Inventory cost LCM valuation Allowance at May 31, 2012
NRV (Ceiling) Amount Amount Amount Amount Formula
NRV less normal profit (Floor) Amount Amount Amount Amount Formula
LCM Amount Amount Amount Amount Formula
Amount Amount Formula
153010892.xlsx.ms_office, Problem 9-2, Page 9 of 12, 6/20/2013, 6:59 AM
Name: Instructor:
Date: Course:
(a) (2) For the fiscal year ended May 31, 2012, determine the amount of the gain or loss that would be recorded
due to the change in the Allowance to Reduce Inventory to Market. Enter text answer here as appropriate.
Balance prior to adjustment Required balance Loss to be recorded
Amount Amount Formula
(b) Explain the rationale for the use of the lower of cost or market rule as it applies to inventories.
Enter text answer here as appropriate.
Enter text answer here as appropriate.
153010892.xlsx.ms_office, Problem 9-2, Page 10 of 12, 6/20/2013, 6:59 AM
Solution Name: Date: Instructor: Course: th Intermediate Accounting, 14 Edition by Kieso, Weygandt, and Warfield Primer on Using Excel in Accounting by Rex A Schildhouse P9-6 (Retail Inventory Method) The records for the Clothing Department of Sharapova’s Discount Store
are summarized below for the month of January. Inventory, January 1, Purchases in January, Freight-in, Purchases returns, Transfers in from suburban branch, Net markups: Net markdowns: Inventory losses due to normal breakage, etc, Sales Sales returns:
at retail: at retail: at retail: at retail:
at retail: at retail:
$25,000 137,000 7,000 3,000 13,000 8,000 4,000 400 95,000 2,400
at cost: at cost:
$17,000 82,500
at cost: at cost:
2,300 9,200
Instructions:
(a) Compute the inventory for this department as of January 31, at Retail
Beginning Inventory Purchases Freight-in Purchase returns Transfers in from suburban branch
Cost $17,000 82,500 7,000 (2,300) 9,200 113,400
Retail $25,000 137,000 (3,000) 13,000 172,000 8,000 180,000 (4,000)
Net markups Net markdowns Sales Sales returns Net sales Inventory losses due to breakage Ending inventory at retail Cost-to-retail ratio =
($95,000) 2,400 (92,600) (400) $83,000 $113,400 $180,000
=
63.0%
(b) Compute the inventory for this department as of January 31, at lower of average cost or market.
Ending inventory at lower of average cost or market
$83,000
× 63.0% =
$52,290
Note: Due to significant digits of worksheets and calculators, small differences may occur.
153010892.xlsx.ms_office, Problem 9-6 Solution, Page 11 of 12, 6/20/2013, 6:59 AM
Name: Date: Instructor: Course: th Intermediate Accounting, 14 Edition by Kieso, Weygandt, and Warfield Primer on Using Excel in Accounting by Rex A Schildhouse P9-6 (Retail Inventory Method) The records for the Clothing Department of Sharapova’s Discount Store
are summarized below for the month of January. Inventory, January 1, Purchases in January, Freight-in, Purchases returns, Transfers in from suburban branch, Net markups: Net markdowns: Inventory losses due to normal breakage, etc, Sales Sales returns:
at retail: at retail: at retail: at retail:
at retail: at retail:
$25,000 137,000 7,000 3,000 13,000 8,000 4,000 400 95,000 2,400
at cost: at cost:
$17,000 82,500
at cost: at cost:
2,300 9,200
Instructions:
(a) Compute the inventory for this department as of January 31, at Retail
Beginning Inventory Title Title Title Transfers in from suburban branch
Cost Amount Amount Amount Amount Amount Formula
Retail Amount Amount Amount Amount Formula Amount Formula Amount
Net markups Net markdowns Title Title Title Title Ending inventory at retail Cost-to-retail ratio =
Amount Amount Formula Amount Formula Value Value
=
Formula
(b) Compute the inventory for this department as of January 31, at lower of average cost or market.
Ending inventory at lower of average cost or market
Value
Percentage
Formula
Note: Due to significant digits of worksheets and calculators, small differences may occur.
153010892.xlsx.ms_office, Problem 9-6, Page 12 of 12, 6/20/2013, 6:59 AM
