231|EXCEL – Cougar Equipment Trial Balance

[Mariafe_Torrejon]

Time to Complete: 40 minutes
Difficulty: Easy

Solution:

1. Net Profit Margin 24,44%
   =ABS(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”))

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

3. 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”))

4. Debt to Equity Ratio = 1.32
   =ABS(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”))

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

6. ROE = 212.67%
   =ABS(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”))

[Charles_Winfield]

Time to Complete: 45 minutes
Difficulty: Intermediate

TechHubChallenge231_Data.xlsm | Powered by Box

[RJ_Bateman]

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”))

Beginner
25 Minutes

[Chase_Torgesen]

Time- 40 mins
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 = 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 = 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”))

[Scott_Johnston]

Time to complete: 45 min
Difficulty: 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”))/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: 3.13
=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”))

[Gwen_Chandler]

Time to complete: 50 minutes
Difficulty: Hard
Solution:
Net Profit Margin: 24.44%
=(SUMIFS(‘Journal Entries’!$H$5:$H$3424,‘Journal Entries’!D5:D3424,“>4000”))/SUMIFS(‘Journal Entries’!H5:H3424,‘Journal Entries’!D5:D3424,“>4000”,‘Journal Entries’!D5:D3424,“<5000”)

Current Ratio: 3.57%
=(SUMIFS(‘Journal Entries’!H5:H3424, ‘Journal Entries’!D5:D3424, “>1100”, ‘Journal Entries’!D5:D3424, “<1200”))/(SUMIFS(‘Journal Entries’!H5:H3424, ‘Journal Entries’!D5:D3424, “>2100”, ‘Journal Entries’!D5:D3424, “<2200”))

ROA: 47.80%
=(SUMIF(‘Journal Entries’!D5:D3424,“>4000”, ‘Journal Entries’!H5:H3424))/(SUMIFS(‘Journal Entries’!H5:H3424,‘Journal Entries’!D5:D3424,“>1000”,‘Journal Entries’!D5:D3424,“<2000”))

Debt to Equity: 1.32
=(SUMIFS(‘Journal Entries’!H5:H3424, ‘Journal Entries’!D5:D3424, “>2000”, ‘Journal Entries’!D5:D3424, “<3000”))/(SUMIFS(‘Journal Entries’!H5:H3424, ‘Journal Entries’!D5:D3424, “>3000”, ‘Journal Entries’!D5:D3424, “<4000”))

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

ROE: 212.67%
=(SUMIF(‘Journal Entries’!D5:D3424,“>4000”,‘Journal Entries’!H5:H3424))/(SUMIFS(‘Journal Entries’!H5:H3424, ‘Journal Entries’!D5:D3424, “>3000”, ‘Journal Entries’!D5:D3424, “<4000”))
[/quote]