231|EXCEL – Cougar Equipment Trial Balance

[Samantha_Cassell]

Time to complete: 50 Minutes
Rating: Intermediate
Solution:

Net Profit Margin: 24.44%

=ABS(SUMIF(‘Journal Entries’!$J$6:$J$40,“>4000”,‘Journal Entries’!$L$6:$L$40)/(SUMIFS(‘Journal Entries’!$L$6:$L$40,‘Journal Entries’!$J$6:$J$40,“>4000”,‘Journal Entries’!$J$6:$J$40,“<5000”)))

=24.44%

Current Ratio:

=ABS(SUMIFS(‘Journal Entries’!$L$6:$L40,‘Journal Entries’$!$J$6:$J$40,“>1100”,‘Journal Entries’!$J$6:$J$40,“<1200”)/(SUMIFS(‘Journal Entries’!$L$6:$L$40,‘Journal Entries’!$J$6:$J$40,“>2100”,‘Journal Entries’!$J$6:$J$40,“<2200”)))

=3.57

ROA:

=ABS(SUMIF(‘Journal Entries’!$J$6:$J$40,“>4000”,‘Journal Entries’!$L$6:$L$40)/(SUMIFS(‘Journal Entries’!$L$6:$L$40,‘Journal Entries’!$J$6:$J$40,“>1000”,‘Journal Entries’!$J$6:$J$40,“<2000”)))

=47.80%

Debt To Equity Ratio:

=ABS(SUMIFS(‘Journal Entries’!$L$6:$L$40,‘Journal Entries’!$J$6:$J$40,“>2000”,‘Journal Entries’!$J$6:$J$40,“<3000”)/(SUMIFS(‘Journal Entries’!$L$6:$L$40,‘Journal Entries’!$J$6:$J$40,“>3000”,‘Journal Entries’!$J$6:$J$40,“<4000”)))

= 1.32

Inventory Turnover:

=‘Journal Entries’!$L$29/‘Journal Entries’!$L$9

= 7.19

ROE:

=ABS(SUMIF(‘Journal Entries’!$J$6:$J$40,“>4000”,‘Journal Entries’!$L$6:$L$40)/(SUMIFS(‘Journal Entries’!$L$6:$L$40,‘Journal Entries’!$J$6:$J$40,“>3000”,‘Journal Entries’!$J$6:$J$40,“<4000”)))

=212.67%

[Henry_Chen]

1.=ABS(SUMIFS(Table1[Balance], Table1[Account Number], “>1100”, Table1[Account Number], “<1200”)) / ABS(SUMIFS(Table1[Balance], Table1[Account Number], “>2100”, Table1[Account Number], “<2200”))
2.=ABS(SUMIF(Table1[Account Number], “>4000”, Table1[Balance])) / ABS(SUMIFS(Table1[Balance], Table1[Account Number], “>3000”,
3.
=ABS(SUMIF(Table1[Account Number], “>4000”, Table1[Balance])) / ABS(SUMIFS(Table1[Balance], Table1[Account Number], “>4000”, Table1[Account Number], “<5000”))
4.
=ABS(SUMIFS(Table1[Balance],Table1[Account Number], “>2000”, Table1[Account Number],“<2300”)) / ABS(SUMIFS(Table1[Balance],

5.XLOOKUP(“Cost of Goods Sold”,Table1[[#All],[Account Label]],Table1[[#All],[Balance]])/XLOOKUP(“Inventory”,Table1[[#All],[Account Label]],Table1[[#All],[Balance]])

[Zachary_Kuta]

Time to complete: 1 hour
Rating: Intermediate:
Solution:
Net Profit Margin: 24.44%
=ABS(SUMIF(‘Journal Entries’!J6:J41,“>4000”,‘Journal Entries’!L6:L1048576)/SUMIFS(‘Journal Entries’!L6:L1048576,‘Journal Entries’!J6:J1048576,“>4000”,‘Journal Entries’!J6:J1048576,“<5000”))
Current Ratio 3.57
=ABS(SUMIFS(‘Journal Entries’!L6:L1048576,‘Journal Entries’!J6:J1048576,“>1100”,‘Journal Entries’!J6:J1048576,“<1200”))/ABS(SUMIFS(‘Journal Entries’!L6:L1048576,‘Journal Entries’!J6:J1048576,“>2100”,‘Journal Entries’!J6:J1048576,“<2200”))
ROA 47.8%
=ABS(SUMIF(‘Journal Entries’!J6:J41,“>4000”,‘Journal Entries’!L6:L1048576)/ABS(SUMIFS(‘Journal Entries’!L6:L1048576,‘Journal Entries’!J6:J1048576,“>1000”,‘Journal Entries’!J6:J1048576,“<2000”)))
Debt to Equity Ratio 1.32
=ABS(SUMIFS(‘Journal Entries’!L6:L1048576,‘Journal Entries’!J6:J1048576,“>2000”,‘Journal Entries’!J6:J1048576,“<3000”))/ABS(SUMIFS(‘Journal Entries’!L6:L1048576,‘Journal Entries’!J6:J1048576,“>3000”,‘Journal Entries’!J6:J1048576,“<4000”))
Inventory Turnover 7.19
=‘Journal Entries’!L29/‘Journal Entries’!L9
ROE 212.67%
=ABS(SUMIFS(‘Journal Entries’!L6:L1048576,‘Journal Entries’!J6:J1048576,“>4000”))/ABS(SUMIFS(‘Journal Entries’!L6:L1048576,‘Journal Entries’!J6:J1048576,“>3000”,‘Journal Entries’!J6:J1048576,“<4000”))

[Will_Snow]

Time to complete: 1hr
Difficulty: hard
NPM: 24.44
=SUM(‘Journal Entries’!L21:L40)/SUM(‘Journal Entries’!L21:L23)
CR: 3.57
=SUM(‘Journal Entries’!L6:L10)/SUM(‘Journal Entries’!L16:L17)
ROA: 47.8
==SUMIF(‘Journal Entries’!J6:J40,“>4000”,‘Journal Entries’!L6:L40)/SUM(‘Journal Entries’!L6:L15)
DTE: 1.32
==SUM(‘Journal Entries’!J16:J18)/SUM(‘Journal Entries’!J19:J20)
ITR: 7.19
=‘Journal Entries’!L29/‘Journal Entries’!L9
ROE: 212.67
==SUM(‘Journal Entries’!L21:L40)/SUM(‘Journal Entries’!L19:L20)

[Charles_T_Garrard]

Time to Complete: 30 Minutes
Difficulty: Medium

Net Profit Margin: 24.44%
=ABS(SUMIF(‘Journal Entries’!J6:J2573,“>4000”,‘Journal Entries’!L6:L2573)) / ABS(SUMIFS(‘Journal Entries’!L6:L2573,‘Journal Entries’!J6:J2573,“>4000”,‘Journal Entries’!J6:J2573,“<5000”))

Current Ratio: = 3.45
=IFERROR(ABS(SUMIFS(‘Journal Entries’!L6:L40, ‘Journal Entries’!J6:J40, “>1100”, ‘Journal Entries’!J6:J40, “<1200”) /
SUMIFS(‘Journal Entries’!L6:L40, ‘Journal Entries’!J6:J40, “>2100”, ‘Journal Entries’!J6:J40, “<2200”)), “”)

ROA: 47.8%
=IFERROR(ABS(SUMIFS(‘Journal Entries’!L6:L40, ‘Journal Entries’!J6:J40, “>4000”) /
SUMIFS(‘Journal Entries’!L6:L40, ‘Journal Entries’!J6:J40, “>1000”, ‘Journal Entries’!J6:J40, “<2000”)), 0)

Debt to Equity Ratio = 1.32
=IFERROR(SUMIFS(‘Journal Entries’!L6:L40, ‘Journal Entries’!J6:J40, “>2000”, ‘Journal Entries’!J6:J40, “<3000”) /
SUMIFS(‘Journal Entries’!L6:L40, ‘Journal Entries’!J6:J40, “>3000”, ‘Journal Entries’!J6:J40, “<4000”), 0)

Inventory Turnover = 7.19
=‘Journal Entries’!L29/‘Journal Entries’!L9

Return On Equity: = 212.67%
=IFERROR(SUMIFS(‘Journal Entries’!L6:L40, ‘Journal Entries’!J6:J40, “>4000”) /
SUMIFS(‘Journal Entries’!L6:L40, ‘Journal Entries’!J6:J40, “>3000”, ‘Journal Entries’!J6:J40, “<4000”), 0)

[Caroline_Howard]

Difficulty: medium
Time to complete: 45 minutes
Net Profit Margin = 24.44%
=SUMIF(‘Journal Entries’!J:J, “>=4000”, ‘Journal Entries’!L:L) / SUMIFS(‘Journal Entries’!L:L, ‘Journal Entries’!J:J, “>=4000”, ‘Journal Entries’!J:J, “<5000”)

Current Ratio = 3.57
=-SUMIFS(‘Journal Entries’!L:L, ‘Journal Entries’!J:J, “>=1100”, ‘Journal Entries’!J:J, “<1200”) / SUMIFS(‘Journal Entries’!L:L, ‘Journal Entries’!J:J, “>=2100”, ‘Journal Entries’!J:J, “<2200”)

ROA = 47.80%
=-SUMIF(‘Journal Entries’!J:J, “>=4000”, ‘Journal Entries’!L:L) / SUMIFS(‘Journal Entries’!L:L, ‘Journal Entries’!J:J, “>=1000”, ‘Journal Entries’!J:J, “<2000”)

Debt to Equity = 1.32
=SUMIFS(‘Journal Entries’!L:L, ‘Journal Entries’!J:J, “>=2000”, ‘Journal Entries’!J:J, “<3000”) / SUMIFS(‘Journal Entries’!L:L, ‘Journal Entries’!J:J, “>=3000”, ‘Journal Entries’!J:J, “<4000”)

Inventory Turnover = 7.19
=SUMIF(‘Journal Entries’!K:K, “Cost of Goods Sold”, ‘Journal Entries’!L:L) / SUMIF(‘Journal Entries’!K:K, “Inventory”, ‘Journal Entries’!L:L)

ROE = 212.67%=SUMIF(‘Journal Entries’!J:J, “>=4000”, ‘Journal Entries’!L:L) / SUMIFS(‘Journal Entries’!L:L, ‘Journal Entries’!J:J, “>=3000”, ‘Journal Entries’!J:J, “<4000”)

[Mark_Whisenant]

Time to complete: 1 hour
Rating: easy-intermediate
Solution: Net Profit Margin = 24.44%
=(SUMIF(‘Journal Entries’!J6:J40,“>4000”,‘Journal Entries’!L6:L40))/(SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40,“>4000”,‘Journal Entries’!J6:J40,“<5000”))

Current Ratio = 3.57
=ABS(SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40,“>1100”,‘Journal Entries’!J6:J40,“<1200”)/SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40,“>2100”,‘Journal Entries’!J6:J40,“<2200”))

ROA = 47.80%
=ABS(SUMIF(‘Journal Entries’!J6:J40,“>4000”,‘Journal Entries’!L6:L40)/SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40,“>1000”,‘Journal Entries’!J6:J40,“<2000”))

Debt to Equity Ratio = 1.32
=SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40,“>2000”,‘Journal Entries’!J6:J40,“<3000”)/SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40,“>3000”,‘Journal Entries’!J6:J40,“<4000”)

Inventory Turnover = 7.19
=‘Journal Entries’!L29/‘Journal Entries’!L9

ROE = 212.67%
=SUMIF(‘Journal Entries’!J6:J40,“>4000”,‘Journal Entries’!L6:L40)/SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40,“>3000”,‘Journal Entries’!J6:J40,“<4000”)

[Eliza_Simister]

Time to Complete: 1 hour
Difficult
Solution:
Net Profit Margin = 24.44%
=ABS(SUMIF(TB_Account_ID,“>4000”,TB_Balance))/ABS(SUMIFS(TB_Balance,TB_Account_ID,“>4000”,TB_Account_ID,“<5000”))

Current Ratio = 3.57
=ABS(SUMIFS(TB_Balance,TB_Account_ID,“>0”,TB_Account_ID,“<1200”))/ABS(SUMIFS(TB_Balance,TB_Account_ID,“>2100”,TB_Account_ID,“<2200”))

ROA = 47.8%
=ABS(SUMIFS(TB_Balance,TB_Account_ID,“>4000”))/ABS(SUMIFS(TB_Balance,TB_Account_ID,“>1000”,TB_Account_ID,“<2000”))

Debt-to-Equity Ratio = 1.32
=ABS(SUMIFS(TB_Balance,TB_Account_ID,“>2000”,TB_Account_ID,“<3000”))/ABS(SUMIFS(TB_Balance,TB_Account_ID,“>3000”,TB_Account_ID,“<4000”))

Inventory Turnover Ratio = 7.19
=XLOOKUP(“cost of goods sold”,TB_Account_Name,TB_Balance)/XLOOKUP(“Inventory”,TB_Account_Name,TB_Balance)

ROE = 212.67%
=ABS(SUMIFS(TB_Balance,TB_Account_ID,“>4000”))/ABS(SUMIFS(TB_Balance,TB_Account_ID,“>3000”,TB_Account_ID,“<4000”))

[Andrew_Wells]

Time for completion: 40 minutes
Difficulty: Intermediate-Hard
Solutions:

Net Profit Margin = 24.44%
=ABS(SUMIF(TB_Account_ID,“>4000”,TB_Balance))/ABS(SUMIFS(TB_Balance,TB_Account_ID,“>4000”,TB_Account_ID,“<5000”))

Current Ratio = 3.57
=ABS(SUMIFS(TB_Balance,TB_Account_ID,“>1100”,TB_Account_ID,“<1200”))/ABS(SUMIFS(TB_Balance,TB_Account_ID,“>2100”,TB_Account_ID,“<2200”))

ROA = 47.80%
=ABS(SUMIF(TB_Account_ID,“>4000”,TB_Balance))/ABS(SUMIFS(TB_Balance,TB_Account_ID,“>1000”,TB_Account_ID,“<2000”))

Debt to Equity Ratio = 1.32
=ABS(SUMIFS(TB_Balance,TB_Account_ID,“>2000”,TB_Account_ID,“<3000”))/ABS(SUMIFS(TB_Balance,TB_Account_ID,“>3000”,TB_Account_ID,“<4000”))

Inventory Turnover = 7.19
=‘Journal Entries’!L30/‘Journal Entries’!L10

ROE = 212.67%
=ABS(SUMIF(TB_Account_ID,“>4000”,TB_Balance))/ABS(SUMIFS(TB_Balance,TB_Account_ID,“>3000”,TB_Account_ID,“<4000”))

[Logan_Messenger]

Difficulty: Medium to hard
Time to complete: 45 Minutes
Net Profit Margin = 24.44%
=SUMIF(‘Journal Entries’!J:J, “>=4000”, ‘Journal Entries’!L:L) / SUMIFS(‘Journal Entries’!L:L, ‘Journal Entries’!J:J, “>=4000”, ‘Journal Entries’!J:J, “<5000”)

Current Ratio = 3.57
=-SUMIFS(‘Journal Entries’!L:L, ‘Journal Entries’!J:J, “>=1100”, ‘Journal Entries’!J:J, “<1200”) / SUMIFS(‘Journal Entries’!L:L, ‘Journal Entries’!J:J, “>=2100”, ‘Journal Entries’!J:J, “<2200”)

ROA = 47.80%
=-SUMIF(‘Journal Entries’!J:J, “>=4000”, ‘Journal Entries’!L:L) / SUMIFS(‘Journal Entries’!L:L, ‘Journal Entries’!J:J, “>=1000”, ‘Journal Entries’!J:J, “<2000”)

Debt to Equity = 1.32
=SUMIFS(‘Journal Entries’!L:L, ‘Journal Entries’!J:J, “>=2000”, ‘Journal Entries’!J:J, “<3000”) / SUMIFS(‘Journal Entries’!L:L, ‘Journal Entries’!J:J, “>=3000”, ‘Journal Entries’!J:J, “<4000”)

Inventory Turnover = 7.19
=SUMIF(‘Journal Entries’!K:K, “Cost of Goods Sold”, ‘Journal Entries’!L:L) / SUMIF(‘Journal Entries’!K:K, “Inventory”, ‘Journal Entries’!L:L)

ROE = 212.67%=SUMIF(‘Journal Entries’!J:J, “>=4000”, ‘Journal Entries’!L:L) / SUMIFS(‘Journal Entries’!L:L, ‘Journal Entries’!J:J, “>=3000”, ‘Journal Entries’!J:J, “<4000”)

[Katelyn_Hamilton]

Time to complete: 30 minutes
Difficulty: Mid
Solution:
a. Net Profit Margin: =ABS(SUMIF(‘Journal Entries’!J6:J40,“>4000”,‘Journal Entries’!L6:L40))/ABS(SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40,“>4000”,‘Journal Entries’!J6:J40,“<5000”))

b. Current Ratio: =ABS(SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40,“>1100”,‘Journal Entries’!J6:J40,“<1200”))/ABS(SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40,“>2100”,‘Journal Entries’!J6:J40,“<2200”))

c. ROA: =ABS(SUMIF(‘Journal Entries’!J6:J40,“>4000”,‘Journal Entries’!L6:L40))/ABS(SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40,“>1000”,‘Journal Entries’!J6:J40,“<2000”))

d. Debt to Equity Ratio: =ABS(SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40,“>2000”,‘Journal Entries’!J6:J40,“<3000”))/ABS(SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40,“>3000”,‘Journal Entries’!J6:J40,“<4000”))

e. Inventory Turnover: =XLOOKUP(“Cost of Goods Sold”,‘Journal Entries’!K6:K40,‘Journal Entries’!L6:L40)/XLOOKUP(“Inventory”,‘Journal Entries’!K6:K40,‘Journal Entries’!L6:L40)

f. ROE: =ABS(SUMIF(‘Journal Entries’!J6:J40,“>4000”,‘Journal Entries’!L6:L40))/ABS(SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40,“>3000”,‘Journal Entries’!J6:J40,“<4000”))

[Preston_Dudley]

Time to complete: 45 minutes
Level: Intermediate
Solution:
Net Profit Margin = 24.44%
=ABS(SUMIF(‘Journal Entries’!J6:J40,“>4000”,‘Journal Entries’!L6:L40))/ABS(SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40, “>4000”,‘Journal Entries’!J6:J40,“<5000”))

Current Ratio 3.57
=ABS(SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40,“<1200”,‘Journal Entries’!J6#,“>0”))/ABS(SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6#,“>2000”,‘Journal Entries’!J6#,“<2200”))

ROA 47.80%
=ABS(SUMIF(‘Journal Entries’!J6:J40,“>4000”,‘Journal Entries’!L6:L40))/ABS(SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40,“<2000”,‘Journal Entries’!J6#,“>0”))

Debt to Equity Ratio 1.32
=ABS(SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40,“>2000”,‘Journal Entries’!J6:J40,“<3000”))/ABS(SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40,“>3000”,‘Journal Entries’!J6:J40,“<4000”))

Inventory Turnover 7.19
=‘Journal Entries’!L29/‘Journal Entries’!L9

ROE 212.67%
=ABS(SUMIF(‘Journal Entries’!J6#,“>4000”,‘Journal Entries’!L6:L40))/ABS(SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6#,“>3000”,‘Journal Entries’!J6#,“<4000”))

[Aaron_Christensen]

Difficulty intermediate. Time to complete about 35 minutes
Great application of accounting principles in excel!

[Isaiah_LeMone]

Solution: Net Profit Margin = 24.44%
=(SUMIF(‘Journal Entries’!J6:J40,“>4000”,‘Journal Entries’!L6:L40))/(SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40,“>4000”,‘Journal Entries’!J6:J40,“<5000”))

Current Ratio = 3.57
=ABS(SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40,“>1100”,‘Journal Entries’!J6:J40,“<1200”)/SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40,“>2100”,‘Journal Entries’!J6:J40,“<2200”))

ROA = 47.80%
=ABS(SUMIF(‘Journal Entries’!J6:J40,“>4000”,‘Journal Entries’!L6:L40)/SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40,“>1000”,‘Journal Entries’!J6:J40,“<2000”))

Debt to Equity Ratio = 1.32
=SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40,“>2000”,‘Journal Entries’!J6:J40,“<3000”)/SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40,“>3000”,‘Journal Entries’!J6:J40,“<4000”)

Inventory Turnover = 7.19
=‘Journal Entries’!L29/‘Journal Entries’!L9

ROE = 212.67%
=SUMIF(‘Journal Entries’!J6:J40,“>4000”,‘Journal Entries’!L6:L40)/SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40,“>3000”,‘Journal Entries’!J6:J40,“<4000”)

[Erica_Huang]

Time to complete: 1 hour
Rating: Medium

Net Profit Margin = 24.44%
=ABS(SUMIF(‘Journal Entries’!J6:J40,“>4000”,‘Journal Entries’!L6:L40))/ABS(SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40,“>4000”,‘Journal Entries’!J6:J40, “<5000”))

Current Ratio = 3.57%
=ABS(SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40, “>1100”,‘Journal Entries’!J6:J40, “<1200”))/ABS(SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40, “>2100”,‘Journal Entries’!J6:J40, “<2200”))

ROA= 47.8%
=ABS(SUMIF(‘Journal Entries’!J6:J40,“>4000”,‘Journal Entries’!L6:L40))/ABS(SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40,“>1000”,‘Journal Entries’!J6:J40, “<2000”))

Debt to Equity Ratio= 1.32
=ABS(SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40, “>2000”,‘Journal Entries’!J6:J40, “<3000”))/ABS(SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40, “>3000”,‘Journal Entries’!J6:J40, “<4000”))

Inventory turnover= 7.19
=‘Journal Entries’!L29/‘Journal Entries’!L9

ROE= 212.67%
=ABS(SUMIF(‘Journal Entries’!J6:J40,“>4000”,‘Journal Entries’!L6:L40))/ABS(SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40,“>3000”,‘Journal Entries’!J6:J40, “<4000”))

[John_Colton]

Time to complete: 20 mins
Difficulty: Medium

Net Profit Margin - 24.44%
=SUMIF(‘Journal Entries’!J6:J40,“>4000”,‘Journal Entries’!L6:L40)/SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40,“>4000”,‘Journal Entries’!J6:J40,“<5000”)

Current Ratio - 3.57
=ABS(SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40,“>1100”,‘Journal Entries’!J6:J40,“<1200”)/SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40,“>2100”,‘Journal Entries’!J6:J40,“<2200”))

ROA - 47.8%
=ABS(SUMIF(‘Journal Entries’!J6:J40,“>4000”,‘Journal Entries’!L6:L40)/SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40,“>1000”,‘Journal Entries’!J6:J40,“<2000”))

Debt to Equity Ratio - 1.32
=SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40,“>2000”,‘Journal Entries’!J6:J40,“<3000”)/SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40,“<4000”,‘Journal Entries’!J6:J40,“>3000”)

Inventory Turnover - 7.19
=‘Journal Entries’!L29/‘Journal Entries’!L9

ROE - 212.67%
=SUMIF(‘Journal Entries’!J6:J40,“>4000”,‘Journal Entries’!L6:L40)/SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40,“>3000”,‘Journal Entries’!J6:J40,“<4000”)

[Austin_Smith]

Net Profit Margin = 24.44%
=(SUMIF(‘Journal Entries’!J6:J40,“>4000”,‘Journal Entries’!L6:L40))/(SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40,“>4000”,‘Journal Entries’!J6:J40,“<5000”))

Current Ratio = 3.57
=ABS(SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40,“>1100”,‘Journal Entries’!J6:J40,“<1200”)/SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40,“>2100”,‘Journal Entries’!J6:J40,“<2200”))

ROA = 47.80%
=ABS(SUMIF(‘Journal Entries’!J6:J40,“>4000”,‘Journal Entries’!L6:L40)/SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40,“>1000”,‘Journal Entries’!J6:J40,“<2000”))

Debt to Equity Ratio = 1.32
=SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40,“>2000”,‘Journal Entries’!J6:J40,“<3000”)/SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40,“>3000”,‘Journal Entries’!J6:J40,“<4000”)

Inventory Turnover = 7.19
=‘Journal Entries’!L29/‘Journal Entries’!L9

ROE = 212.67%
=SUMIF(‘Journal Entries’!J6:J40,“>4000”,‘Journal Entries’!L6:L40)/SUMIFS(‘Journal Entries’!L6:L40,‘Journal Entries’!J6:J40,“>3000”,‘Journal Entries’!J6:J40,“<4000”)

[Adam_Peterson]

Time to Complete: 1Hr
Difficulty: Intermediate

*Net Profit Margin: 24.44%

=ABS(SUMIF(‘Journal Entries’!J7:J41, “> 4000”, ‘Journal Entries’!L7:L41))/ABS(SUMIFS(‘Journal Entries’!L7:L41, ‘Journal Entries’!J7:J41, “>4000”, ‘Journal Entries’!J7:J41,“<5000”))

*Current Ratio: 3.57

=ABS(SUMIFS(‘Journal Entries’!L7:L41, ‘Journal Entries’!J7:J41, “>1100”, ‘Journal Entries’!J7:J41, “<1200”))/ABS(SUMIFS(‘Journal Entries’!L7:L41, ‘Journal Entries’!J7:J41, “>2100”, ‘Journal Entries’!J7:J41, “<2200”))

*ROA: 47.80%

=ABS(SUMIF(‘Journal Entries’!J7:J41, “>4000”, ‘Journal Entries’!L7:L41))/ABS(SUMIFS(‘Journal Entries’!L7:L41, ‘Journal Entries’!J7:J41,“>1000”, ‘Journal Entries’!J7:J41, “<2000”))

*Debt to Equity: 1.32

=ABS(SUMIFS(‘Journal Entries’!L7:L41, ‘Journal Entries’!J7:J41, “>2000”, ‘Journal Entries’!J7:J41, “<3000”))/ABS(SUMIFS(‘Journal Entries’!L7:L41, ‘Journal Entries’!J7:J41, “>3000”, ‘Journal Entries’!J7:J41, “<4000”))

*Inventory Turnover: 7.19

=‘Journal Entries’!L30/‘Journal Entries’!L10

*ROE: 212.67

=ABS(SUMIF(‘Journal Entries’!J7:J41, “>4000”, ‘Journal Entries’!L7:L41))/ABS(SUMIFS(‘Journal Entries’!L7:L41, ‘Journal Entries’!J7:J41, “>3000”, ‘Journal Entries’!J7:J41, “<4000”))

[Liam_Smith]

Net Profit: 24.44%
=ABS(SUMIF(TB_Account_ID,“>4000”,TB_Balance))/ABS(SUMIFS(TB_Balance,TB_Account_ID,“>4000”,TB_Account_ID,“<5000”))
Quick Ratio: 3.57
=SUM(‘Journal Entries’!L7:L11)/ABS(SUM(‘Journal Entries’!L17:L18))
Return on Assets: 47.8%
=ABS(SUMIF(TB_Account_ID,“>4000”,TB_Balance))/SUM(‘Journal Entries’!L7:L16)
Debt to Equity Ratio: 1.32
=ABS(SUM(‘Journal Entries’!L17:L19))/ABS(SUM(‘Journal Entries’!L20:L21))
Inventory Turnover: 7.19
=‘Journal Entries’!L30/‘Journal Entries’!L10
ROE: 212.67%
=ABS(SUMIF(TB_Account_ID,“>4000”,TB_Balance))/ABS(SUM(‘Journal Entries’!L20:L21))

[Nick_Price]

Time: 50 minutes
Difficulty: Intermediate

Net Profit Margin = 24.44%
=ABS(SUMIF(TB_Account_ID,“>4000”,TB_Balance))/ABS(SUMIFS(TB_Balance,TB_Account_ID,“>4000”,TB_Account_ID,“<5000”))

Current Ratio = 3.57
=ABS(SUMIFS(TB_Balance,TB_Account_ID,“>0”,TB_Account_ID,“<1200”))/ABS(SUMIFS(TB_Balance,TB_Account_ID,“>2100”,TB_Account_ID,“<2200”))

ROA = 47.8%
=ABS(SUMIFS(TB_Balance,TB_Account_ID,“>4000”))/ABS(SUMIFS(TB_Balance,TB_Account_ID,“>1000”,TB_Account_ID,“<2000”))

Debt-to-Equity Ratio = 1.32
=ABS(SUMIFS(TB_Balance,TB_Account_ID,“>2000”,TB_Account_ID,“<3000”))/ABS(SUMIFS(TB_Balance,TB_Account_ID,“>3000”,TB_Account_ID,“<4000”))

Inventory Turnover Ratio = 7.19
=XLOOKUP(“cost of goods sold”,TB_Account_Name,TB_Balance)/XLOOKUP(“Inventory”,TB_Account_Name,TB_Balance)

ROE = 212.67%
=ABS(SUMIFS(TB_Balance,TB_Account_ID,“>4000”))/ABS(SUMIFS(TB_Balance,TB_Account_ID,“>3000”,TB_Account_ID,“<4000”))