A B D E F 1 2 3 4 5 6 7 8 Account Title 9...

Transcribed Image Text:

A B D E F 1 2 3 4 5 6 7 8 Account Title 9 Cash DATA SHEET ONLY; No answers entered here! COUGAR MOUNTAIN SPORTS Adjusted Trial Balance June 30, 2024 Balance Debit Credit 152,000 10 Accounts Receivable 11,250 11 Merchandise Inventory 2,876 12 Estimated Returns Inventory 556 13 Office Supplies 1,160 14 Prepaid Rent 4,000 15 Building 45,000 16 Accumulated Depreciation, Building 2,800 17 Equipment 6,500 18 Accumulated Depreciation, Equipment 2,400 19 Accounts Payable 14,250 20 Salaries Payable 1,700 21 Interest Payable 2,610 22 Refunds Payable 23 Notes Payable 24 Common Stock 25 Retained Earnings 26 Dividends 27 Sales Revenue 28 Sales Discounts Forfeited 29 Cost of Goods Sold 30 Salaries Expense 1,300 43,500 100,000 42,800 3,200 105,700 400 26,390 14,300 *Note 1 31 Rent Expense 24,000 *Note 2 32 Utilities Expense 5,500 *Note 3 33 Depreciation Expense - Building 1,400 *Note 4 34 Depreciation Expense - Equipment 1,200 *Note 5 35 Advertising Expense 2,225 36 Interest Expense 2,610 37 Delivery Expense 450 38 Supplies Expense 39 Income Tax Expense 40 41 42 Notes 43 #1-30% of the salaries is for the Administrative Offices. 940 *Note 6 11,903 317,460 317,460 44 #2 -20% of the rent is for the Administrative Offices. 45 #3 -40% of the utilities is for the Administrative Offices. 46 #4 - The Building houses the Administrative Offices. 47 #5 - The Equipment is categorized as selling. 48 #6 -50% of the supplies is for the Administrative Offices. 49 50 51 52 53 54 A B D E F G H 1 Prepare a multi-step income statement based on the adjusted trial balance. 2 (Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from the Instruction or Adjusted Trial Balance tabs you will be marked wrong.) 3 4 5 6 7 8 9 10 11 Operating Expenses: 12 Selling Expenses: 13 14 15 16 17 18 19 20 Total Selling Expenses 21 Administrative Expenses: 22 23 24 25 26 27 Total Administrative Expense 28 Total Operating Expenses 29 Operating Income 30 Other Income and (Expenses) 31 32 33 Total Other Income and (Expenses) 34 Income Before Income Tax Expense 35 36 37 38 COUGAR MOUNTAIN SPORTS Income Statement Year Ended June 30, 2024 39 GROSS PROFIT MARGIN PERCENTAGE 40 Compute the gross profit margin percentage. Format percentages to two decimal places (e.g. 22.74%). 41 (Always use cell references and formulas where appropriate to receive full credit.) 42 43 44 Gross Profit Margin Percentage 45 46 47 48 49 Cell | Hint: % of Sales Revenue HINTS 50 Formulas | Begin the formulas with an equals sign (=), click on the balance in the adjusted trial balance worksheet, and press the enter key. 51 Interest Expense | Enter interest expense with a minus sign (-) 52 E33 | Use the function =SUM to add up the Other Income and Expenses 53 54 E F G H J K L M N 0 P 6 Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from the Instruction or Adjusted Trial Balance tabs you will be marked wrong. 7 8 9 10 11 12 13 23 A B C Requirements D 1. Use Excel to prepare a multi-step income statement based on the adjusted trial balance. Use formulas to link the balances on the adjusted trial balance to the amounts on the income statement. Note that a. some balances are split between two different areas of the Income Statement. For example, the Rent Expense is divided into two categories-Selling and Administrative. b. Use a formula to add and subtract account amounts as indicated. c. Indent in the income statement as needed. Possible Points 100 Q Format the numbers in the dollar columns with a comma and no decimal places (e.g. 5,460). Use a dollar sign at the d. 14 15 top of each column and at the total (e.g. $26,201). e. Double-underline the total. 16 f. Check your spelling carefully and do not abbreviate. 17 g. Use a minus sign to enter any "Other Expenses". 18 h. Enter all amounts (except for "Other Expenses") as positive values. Do not use a minus sign or parentheses for any values to be subtracted (except for "Other Expenses"). 19 20 21 22 Excel Skills 23 24 222 28 1. Enter numbers or text into cells. 2. Use formulas to add or subtract 3. Select the number format (dollar signs). 4. Select the percentage format (%) to two decimal places. 5. Select the border (double underline) 6. Use the Increase Indent button to indent items in the income statement. 29 30 32 1 Save file to desktop. 33 34 31 Saving & Submitting Solution a. Create folder on desktop, and label COMPLETED EXCEL PROJECTS 2 Upload and submit your file to Canvas to be graded. 35 a. Navigate back to the activity window - screen where you downloaded the initial spreadsheet 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 A B D E F 1 2 3 4 5 6 7 8 Account Title 9 Cash DATA SHEET ONLY; No answers entered here! COUGAR MOUNTAIN SPORTS Adjusted Trial Balance June 30, 2024 Balance Debit Credit 152,000 10 Accounts Receivable 11,250 11 Merchandise Inventory 2,876 12 Estimated Returns Inventory 556 13 Office Supplies 1,160 14 Prepaid Rent 4,000 15 Building 45,000 16 Accumulated Depreciation, Building 2,800 17 Equipment 6,500 18 Accumulated Depreciation, Equipment 2,400 19 Accounts Payable 14,250 20 Salaries Payable 1,700 21 Interest Payable 2,610 22 Refunds Payable 23 Notes Payable 24 Common Stock 25 Retained Earnings 26 Dividends 27 Sales Revenue 28 Sales Discounts Forfeited 29 Cost of Goods Sold 30 Salaries Expense 1,300 43,500 100,000 42,800 3,200 105,700 400 26,390 14,300 *Note 1 31 Rent Expense 24,000 *Note 2 32 Utilities Expense 5,500 *Note 3 33 Depreciation Expense - Building 1,400 *Note 4 34 Depreciation Expense - Equipment 1,200 *Note 5 35 Advertising Expense 2,225 36 Interest Expense 2,610 37 Delivery Expense 450 38 Supplies Expense 39 Income Tax Expense 40 41 42 Notes 43 #1-30% of the salaries is for the Administrative Offices. 940 *Note 6 11,903 317,460 317,460 44 #2 -20% of the rent is for the Administrative Offices. 45 #3 -40% of the utilities is for the Administrative Offices. 46 #4 - The Building houses the Administrative Offices. 47 #5 - The Equipment is categorized as selling. 48 #6 -50% of the supplies is for the Administrative Offices. 49 50 51 52 53 54 A B D E F G H 1 Prepare a multi-step income statement based on the adjusted trial balance. 2 (Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from the Instruction or Adjusted Trial Balance tabs you will be marked wrong.) 3 4 5 6 7 8 9 10 11 Operating Expenses: 12 Selling Expenses: 13 14 15 16 17 18 19 20 Total Selling Expenses 21 Administrative Expenses: 22 23 24 25 26 27 Total Administrative Expense 28 Total Operating Expenses 29 Operating Income 30 Other Income and (Expenses) 31 32 33 Total Other Income and (Expenses) 34 Income Before Income Tax Expense 35 36 37 38 COUGAR MOUNTAIN SPORTS Income Statement Year Ended June 30, 2024 39 GROSS PROFIT MARGIN PERCENTAGE 40 Compute the gross profit margin percentage. Format percentages to two decimal places (e.g. 22.74%). 41 (Always use cell references and formulas where appropriate to receive full credit.) 42 43 44 Gross Profit Margin Percentage 45 46 47 48 49 Cell | Hint: % of Sales Revenue HINTS 50 Formulas | Begin the formulas with an equals sign (=), click on the balance in the adjusted trial balance worksheet, and press the enter key. 51 Interest Expense | Enter interest expense with a minus sign (-) 52 E33 | Use the function =SUM to add up the Other Income and Expenses 53 54 E F G H J K L M N 0 P 6 Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from the Instruction or Adjusted Trial Balance tabs you will be marked wrong. 7 8 9 10 11 12 13 23 A B C Requirements D 1. Use Excel to prepare a multi-step income statement based on the adjusted trial balance. Use formulas to link the balances on the adjusted trial balance to the amounts on the income statement. Note that a. some balances are split between two different areas of the Income Statement. For example, the Rent Expense is divided into two categories-Selling and Administrative. b. Use a formula to add and subtract account amounts as indicated. c. Indent in the income statement as needed. Possible Points 100 Q Format the numbers in the dollar columns with a comma and no decimal places (e.g. 5,460). Use a dollar sign at the d. 14 15 top of each column and at the total (e.g. $26,201). e. Double-underline the total. 16 f. Check your spelling carefully and do not abbreviate. 17 g. Use a minus sign to enter any "Other Expenses". 18 h. Enter all amounts (except for "Other Expenses") as positive values. Do not use a minus sign or parentheses for any values to be subtracted (except for "Other Expenses"). 19 20 21 22 Excel Skills 23 24 222 28 1. Enter numbers or text into cells. 2. Use formulas to add or subtract 3. Select the number format (dollar signs). 4. Select the percentage format (%) to two decimal places. 5. Select the border (double underline) 6. Use the Increase Indent button to indent items in the income statement. 29 30 32 1 Save file to desktop. 33 34 31 Saving & Submitting Solution a. Create folder on desktop, and label COMPLETED EXCEL PROJECTS 2 Upload and submit your file to Canvas to be graded. 35 a. Navigate back to the activity window - screen where you downloaded the initial spreadsheet 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56