safer to stay in Protected View. Enable Editing Miller-Nobles (12e) - Using Excel Ch05 v2 roject Description: miller-Nobles (12) - Using Excel Ch05 teps to Perform: Step Instructions Points Possible 1 0 Download and open the excel spreadsheet, Miller-Nobles_12e-Using Excel_Ch05_Start.xlsx This spreadsheet includes 3 tabs, you will enter all answers on the third tab, ENTERANSWERS (All answers should be entered in the blue highlighted cells.) Cougar Mountain Sports has prepared an adjusted trial balance for the fiscal year ended June 30, 2018 Notes to the adjusted trial balance are located below the totals. Use the blue shaded areas on the Income Statement worksheet for inputs 2 44 Prepare a multi-step income statement based on the adjusted trial balance (44 pts) a. Use a formula to link the balance on the adjusted trial balance to the amount on the income statement Note that 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 d. Format the numbers in the dollar columns with a comma and no decimal places (eg 5,460). Use a dollar sign at the top of each column and at the total (eg $26,201). Make sure the (implicit) decimal places line up.e. Double underline the total f. Check your spelling carefully and do not abbreviate. g Use a minus sign to enter any "Other Expenses 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") (Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from the instructions or AdjustedTrialBalance tabs, you will be marked wrong) Compute the gross profit margin percentage Format percentages to two decimal places, eg 22.74% (1 p.) (Always use cell references and formulas where appropriate to receive full credit If you copy/paste from the Instructions or Adjusted TraBalance tabs, you will be marked wrong) 3 Once completed, save the project to the desktop File name can be anything, but DO NOT include spaces. Return to auto graded excel activity window, and progress to Step 3 Follow A1 Jx Merchandising Operations B C D E F G H K L M N O P 0 1 Merdhandising Operations 2 Using Excel to prepare a multi-step Income Statement 3 4 Cougar Mountain Sports has prepared an adjusted trial balance for the fiscal year ended June 30, 2018. Notes to the adjusted trial balance are located below the totals. 5 Use the blue shaded areas on the ENTER-ANSWERS tab for inputs. 6 Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from the Instructions or Adjusted TrialBalance tabs, you will be marked wro 7 8 9 Requirements Possible Points 10 44 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. a. Note that some balances are split between two different areas of the Income Statement. For example, the 11 Rent Expense is divided into two categories --Selling and Administrative. 12 b. Use a formula to add and subtract account amounts as indicated. 13 c. Indent in the income statement as needed. + Format the numbers in the dollar columns with a comma and no decimal places (e.g. 5,460). Use a dollar sign 14 at the top of each column and at the total (e.g. $26,201). Make sure the decimal places align. 15 e. Double-underline the total. 16 f. Check your spelling carefully and do not abbreviate. 17 8. 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 2. Compute the gross profit margin percentage. Format percentages to two decimal places (e.g. 22.74%). 21 22 d. DATA SHEET ONLY; No answers entered here! 3 COUGAR MOUNTAIN SPORTS Adjusted Trial Balance June 30, 2018 5 7 Balance 8 Account Title Debit Credit 9 Cash 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 1,300 23 Notes Payable 43,500 24 Mason, Capital 142,800 25 Mason, Withdrawals 3,200 26 Sales Revenue 105,700 27 Sales Discounts Forfeited 400 28 Cost of Goods Sold 26,390 29 Salaries Expense 14,300 30 Rent Expense 24,000 31 Utilities Expense 5,500 32 Depreciation Expense - Building 1,400 Instructions AdjustedTrialBalance ENTERANSWERS1 Ready ses. Unless you need to ed C27 fc 400 D 14,250 1,700 2,610 1,300 43,500 142,800 105,700 400 A B 19 Accounts Payable 20 Salaries Payable 21 Interest Payable 22 Refunds Payable 23 Notes Payable 24 Mason, Capital 25 Mason, Withdrawals 3,200 26 Sales Revenue 27 Sales Discounts Forfeited 28 Cost of Goods Sold 26,390 29 Salaries Expense 14,300 30 Rent Expense 24,000 31 Utilities Expense 5,500 32 Depreciation Expense - Building 1,400 33 Depreciation Expense - Equipment 1,200 34 Advertising Expense 2,225 35 Interest Expense 2,610 36 Delivery Expense 450 37 Supplies Expense 940 38 Income Tax Expense 11,903 39 317,460 40 41 Notes 42 #1 - 30% of the salaries is for the Administrative Offices. 43 #2 -20% of the rent is for the Administrative Offices. 44 #3 - 40% of the utilities is for the Administrative Offices. 45 #4 - The Building houses the Administrative Offices. 46 #5 - The Equipment is categorized as selling. 47 #6 -50% of the supplies is for the Administrative Offices. 48 49 50 Instructions Adjusted TrialBalance ENTERANSWERS1 317,460 Ready 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 Instructions or Adjusted TrialBalance tabs, you will be marked wrong.) 3 4 COUGAR MOUNTAIN SPORTS 5 Income Statement 6 Year Ended June 30, 2018 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) Ne 100 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 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 % of Net Sales Revenue 44 Gross Profit Margin Percentage 45 46 47 48 HINTS 49 Cell Hint: 50 E8 Begin the formula with an equals sign (=), click on the balance in the adjusted trial balance worksheet, and press the enter key. 51 A13 | Start with Salaries Expense. 52 C13 1 =Adjusted Trial Balance!B29*0.7 53 C22=Adjusted Trial Balance!B29*0.3 54 D20 | Use the function =SUM() to add up the Selling Expenses. 55