ACCOUNTING SPREADSHEET EXCEL CHART

danivic78

Chapter6lab.xlsx

Home >Business & Finance homework help >Accounting homework help >ACCOUNTING SPREADSHEET EXCEL CHART

Pr. 6-1B

Problem 6-1B

# Incorrect N-box and B-box entries COUNTIF(B15:G24,"~*")

Name:

Section:

# N-box Incorrects due to blanks COUNTIF(B15:AT24," ")

Score:

# N-box +B-box corrects COUNTIF(B15:AT24," ")

Key Code:

[Key code here]

Total SUM(AD13:AD15)

Instructions

Answers are entered in the cells with gray backgrounds.

Percentage =(AD16-AD13-AD14)/AD16

Cells with non-gray backgrounds are protected and cannot be edited.

An asterisk (*) will appear to the right of an incorrect entry. In part 1, only final inventory cost - Column K - will be graded.

Notes:

Date

Purchases

Cost of Goods Sold

Inventory

If number-entry or blank-entry box is incorrect, returns "*"

Quantity

Unit Cost

Total Cost

Quantity

Unit Cost

Total Cost

Quantity

Unit Cost

Total Cost

If number-entry or blank-entry box is correct, returns single space, " "

Use data verification to set data entry to whole number >= 0, and use drop-downs for lables and names, so that students can't enter a space in a box and have it counted as correct.

Apr. 3

$1,200

$ 30,000

Conditional formatting might be used but wasn't here, to hide some of the error check return symbols. If A1 = "~*", then font = red, if something else, then font = background color.

May 8

cpence: Place the earliest units first, above the units that were received later.

June 5

Balances

cpence: Enter the total of column H, the total cost of merchandise sold.

JOURNAL

Description

Debit

Credit

Accounts Receivable

cpence: Record the credit sale in this entry. Select the accounts from the drop-down list.

cpence: Enter the ending inventory balance from cell K36.

Cost of Goods Sold

Inventory

Sales

cpence: Record the cost of the goods sold in this entry.

Gross profit for the sales period

$214,474

Ending inventory cost

Inventory under LIFO would be

Sol

Problem 6-1B
Name:			Solution
Section:
Scoring:			ON
Instructions
Answers are entered in the cells with gray backgrounds.
Cells with non-gray backgrounds are protected and cannot be edited.
An asterisk (*) will appear to the right of an incorrect entry. In part 1, only final inventory cost - Column K - will be graded.
1.	Date	Purchases			Cost of Goods Sold			Inventory
		Quantity	Unit Cost	Total Cost	Quantity	Unit Cost	Total Cost	Quantity	Unit Cost	Total Cost
	Apr. 3							25	$1,200	$ 30,000
	8	75	$1,240	$ 93,000				25	$1,200	$ 30,000 cpence: Place the earliest units first, above the units that were received later.
								75	$1,240	93,000
	11				25	$1,200	$ 30,000 cpence: Place the earliest units first, above the units that were received later.
					15	$1,240	18,600	60	$1,240	$ 74,400
	30				30	$1,240	$ 37,200	30	$1,240	37,200
	May 8	60	$1,260	$ 75,600				30	$1,240	$ 37,200 cpence: Place the earliest units first, above the units that were received later.
								60	$1,260	75,600
	10				30	$1,240	$ 37,200
					20	$1,260	25,200	40	$1,260	$ 50,400
	19				20	$1,260	$ 25,200	20	$1,260	$ 25,200
	28	80	$1,260	$ 100,800				20	$1,260	$ 25,200
								80	$1,260	100,800
	June 5				20	$1,260	$ 25,200
					20	$1,260	25,200	60	$1,260	$ 75,600
	16				25	$1,260	$ 31,500	35	$1,260	$ 44,100
	21	35	$1,264	$ 44,240				35	$1,260	$ 44,100
								35	$1,264	44,240
	28				35	$1,260	$ 44,100
					9	$1,264	11,376	26	$1,264	$ 32,864
	30	Balances					$ 310,776 cpence: Enter the total of column H, the total cost of merchandise sold.			$ 32,864 cpence: Enter the ending inventory balance from cell K36.
2.	JOURNAL
		Description					Debit		Credit
												Accounts Receivable
		Accounts Receivable cpence: Record the credit sale in this entry. Select the accounts from the drop-down list.					525,250					Cost of Goods Sold
		Sales							525,250			Inventory
												Sales
		Cost of Goods Sold cpence: Record the cost of the goods sold in this entry.					310,776 Mark Sears: This should equal the total of Column H in Part 1.	cpence: Place the earliest units first, above the units that were received later.			cpence: Place the earliest units first, above the units that were received later.	cpence: Enter the total of column H, the total cost of merchandise sold.	cpence: Record the credit sale in this entry. Select the accounts from the drop-down list.	cpence: Enter the ending inventory balance from cell K36.	cpence: Record the cost of the goods sold in this entry.
		Inventory							310,776
3.
				Gross profit for the sales period			$214,474
4.
				Ending inventory cost			$32,864
5.
				Inventory under LIFO would be			lower