Can I get assistance with the Excell spreadsheet for module one (blue tabs)?

1 answer below »
ACC 308 Final Project Workbook
HOME
    Southern New Hampshire University
    ACC 308 - Intermediate Accounting II
    Note: This workbook contains instructions and financial information you will need to complete the Workbook portions of Milestones One and Two. For full instructions for the milestone and final project assignments, refer to the guidelines and ru
ic documents.
You can use the links below to navigate from this HOME tab to additional instructions and the worksheets for each milestone.
    MILESTONE ONE
(Due in Module Three)                MILESTONE TWO
(Due in Module Five)                FINAL PROJECT
(Due in Module Seven)
    Milestone One
Workbook Instructions                Milestone Two
Workbook Instructions                Note: The final project consists of co
ected work from Milestone One and Milestone Two. Incorporate the feedback you received from your instructor.
    1. Trial Balance                1. Pro Forma Financial Statements
    Using the Peyton Approved financial data, create:                Using the given pro forma information, create:
    Adjusting Entries                Pro Forma Income Statement
    Adjusted Trial Balance                Pro Forma Balance Sheet
    2. Revised Financial Statements                2. Notes to the Financial Statements
                    Note: This part of the project is submitted as a separate Word document. Refer to the Milestone Two Guidelines and Ru
ic document for submission guidelines.
    Using the trial balance and preliminary financial statements, prepare:                Your notes must contain the following:
    Revised Balance Sheet                A. Create appropriate notes as year-to-year documentation for managing depreciation, supplies, and inventory.
    Revised Income Statement                B. Create appropriate notes for long-term debt.
    Revised Retained Earnings Statement
    Revised Statement of Cash Flows
    3. Ratio Analysis                3. Management Analysis Brief
    Note: Refer to the Final Project Scenario for Peyton's ratio formulas.                Note: This part of the project is submitted as a separate Word document. Refer to the Milestone Two Guidelines and Ru
ic document for submission guidelines.
    Using the financial statements from 2015, 2016, and revised 2017, calculate the following ratios:                Your management analysis
ief should explain financial information to management. Provide evidence from your accounting workbook to support your ideas where applicable.
     Cu
ent Ratio (Working Capital)                A. Discuss the impact of the pro forma financial statements for predicting ability to meet future expansion goals.
     Quick Ratio                B. Describe the implications of inventory costing, contingent liabilities, and revenue recognition.
     A/R Turnover                C. Identify potential issues in interpretation of financial information, providing examples to support your ideas.
     Inventory Turnove
     Gross Margin
     Return on Sales
     Return on Equity
     Return on Assets
    4. Management Analysis Brief
    Note: This part of the project is submitted as a separate Word document. Refer to the Milestone One Guidelines and Ru
ic document for submission guidelines.
    Your management analysis
ief should explain financial information to management. Provide evidence from your accounting workbook to support your ideas where applicable.
    A. Assess the company’s financial health based on ratio analyses presented in the accounting workbook.
    B. Compare ratio analysis to trends in financial ratios over time for illustrating their impact, providing examples to support your claims.
    C. Summarize the effects of different compounding periods and interest rates on future value of money.
    D. Explain how alignment to relevant regulations and ethical reporting influenced your accounting practices and notes, providing examples to support your claims.
Milestone One Instructions
        Return
HOME
    INSTRUCTIONS FOR MILESTONE ONE
(Due in Module Three)
    Note: Make sure to completely review the Milestone One Guidelines and Ru
ic document.
    Use the data from this milestone and begin working on your final project, due in Module Seven.
    ITEMS TO COMPLETE FOR MILESTONE ONE:
    The tabs to complete are linked below and colored blue for convenience.
    GENERAL
    You just began a position as a financial accountant at Peyton Approved. In this role, your first task is to prepare the company’s financials for the year-end audit. Additionally, the company is interested in expanding its business within the next year. They would like your support in assessing their ability to meet their goals.
    1. TRIAL BALANCE
    Using the Peyton Approved financial data provided below:
    A. Create the necessary adjusting journal entries. Use the REF column to reference the entry to each event.
    B. Complete the adjusted trial balance.
        Trial Balance 2017
    2. REVISED FINANCIAL STATEMENTS
    Peyton Approved's preliminary financial statements are provided in the yellow tabs.
        Balance Sheet 2017
        Income Statement 2017
        Retained Earnings 2017
        Cash Flow 2017
    Using the preliminary financial statements and the Trial Balance 2017, prepare the following statements:
        Balance Sheet
2017 Revised
        Income Statement
2017 Revised
        Retained Earnings Statement
2017 Revised
        Statement of Cash Flows
2017 Revised
    Note: Refer to Module Three resources for a refresher on statement of cash flows.
    Peyton Approved's previous years' financial statements are provided in the orange tabs.
        Balance Sheet 2015
        Balance Sheet 2016
        Income Statement 2016
    3. RATIO ANALYSIS
    Using the revised 2017 financial statements, 2016 financial statements, and 2015 financial statements, prepare a ratio analysis for Peyton Approved.
    Note: Refer to the Final Project Scenario for Peyton's ratio formulas.
        Ratio Analysis
     PEYTON APPROVED FINANCIAL DATA
    Preliminary financial statements have already been prepared (2017 statements in the Final Project Workbook). Final adjusting entries have not yet been made. See table for possible adjustments that indicate what will be recorded at 12/31/17 (fiscal year end). Use the following to complete year-to-year documentation and notes for managing depreciation, inventory, and long-term debt.
    1. A supplier shipped $3,000 of ingredients on 12/29/17. Peyton receives an invoice for $3,175—goods of $3,000 and freight of $175—all dated 12/29/17. Goods were shipped FOB supplier’s warehouse.
    2. At 12/31/17, Peyton has $200 worth of merchandise on consignment at Bruno’s House of Bacon.
    3. On 12/23/17, Peyton received a $1,000 deposit from Pet Globe for product to be shipped by Peyton in the second week of January.
    4. On 12/03/2017, a mixer with cost of $2,000, accumulated depreciation $1,200, was destroyed by a forklift. As of 12/23/17, insurance company has agreed to pay $700 in January 2018, for accidental destruction.
    4. MANAGEMENT ANALYSIS BRIEF
    Note: This part of the project is submitted as a separate Word document. Refer to the Milestone One Guidelines and Ru
ic document for submission guidelines.
    Your management analysis
ief should explain financial information to management. Provide evidence from your accounting workbook to support your ideas where applicable.
    A. Assess the company’s financial health based on ratio analyses presented in the accounting workbook.
    B. Compare ratio analysis to trends in financial ratios over time for illustrating their impact, providing examples to support your claims.
    C. Summarize the effects of different compounding periods and interest rates on future value of money.
    D. Explain how alignment to relevant regulations and ethical reporting influenced your accounting practices and notes, providing examples to support your claims.
Trial Balance 2017
        Milestone One
Workbook Instructions
                                PEYTON APPROVED
                                TRIAL BALANCE
                                As of December 31, 2017
                        Unadjusted trial balance                    Adjusting entries                    Adjusted trial balance
                    Dr        Cr        ref    Dr        Cr    ref        Dr        C
            Cash        67,520.04                                        67,520.04
            Accounts Receivable        68,519.91                                        68,519.91
            Other Receivable - Insurance                                                - 0
            Baking Supplies        15,506.70                                        15,506.70
            Merchandise Inventory        1,238.07                                        1,238.07
            Consignment Inventory                                                - 0
            Prepaid Rent        2,114.55                                        2,114.55
            Prepaid Insurance        2,114.55                                        2,114.55
            Misc. Supplies        170.49                                        170.49
            Baking Equipment        14,000.00                                        14,000.00
            Accumulated Depreciation                1,606.44                                        1,606.44
            Customer Deposit                                                        - 0
            Accounts Payable                20,262.11                                        20,262.11
            Wages Payable                3,383.28                                        3,383.28
            Interest Payable                211.46                                        211.46
            Notes Payable                5,000.00                                        5,000.00
            Common Stock                20,000.00                                        20,000.00
            Beginning Retained earnings                50,144.84                                        50,144.84
            Dividends        105,000.00                                        105,000.00
            Bakery Sales                327,322.55                                        327,322.55
            Merchandise Sales                1,205.64                                        1,205.64
            Cost of Goods Sold - Baked        105,834.29                                        105,834.29
            Cost of Goods Sold - Merchandise        859.77                                        859.77
            Rent Expense        24,549.19                                        24,549.19
            Wages Expense        10,670.72                                        10,670.72
            Misc. Supplies Expense        3,000.46                                        3,000.46
            Business License Expense        2,045.77                                        2,045.77
            Misc. Expense        1,363.84                                        1,363.84
            Depreciation Expense        677.86                                        677.86
            Insurance Expense        1,091.08                                        1,091.08
            Advertising Expense        1,549.74                                        1,549.74
            Interest Expense        818.31                                        818.31
            Telephone Expense        490.98                                        490.98
            Gain/Loss on disposal of equipment
                    429,136.32        429,136.32            - 0        - 0            429,136.32        429,136.32
Balance Sheet 2017
        Preliminary
                Peyton Approved
                Balance Sheet
                As of December 31, 2017
    Assets                    Liabilities and Owners' Equity
        Cu
ent Assets:                    Cu
ent Liabilities:
        Cash    $ 67,520.04                Accounts Payable    $ 20,262.11
        Accounts Receivable    68,519.91                Wages Payable    3,383.28
        Baking Supplies    15,506.70                Interest Payable    211.46
        Merchandise Inventory    1,238.07
        Prepaid Rent    2,114.55
        Prepaid Insurance    2,114.55
        Misc. Supplies    170.49
        Total Cu
ent Assets        $ 157,184.31            Total Cu
ent Liabilities        $ 23,856.85
                            Long Term Liabilities:
        Long Term/Fixed Assets:                    Notes Payable    5,000.00
        Baking Equipment    14,000.00                Total Long Term Liabilities:        5,000.00
         Accumulated Depreciation    -1,606.44
        Net Fixed assets        12,393.56            Total Liabilities:        28,856.85
                            Common Stock    20,000.00
                            Retained Earnings    120,721.02
                            Total Equity        140,721.02
        Total Assets:        $ 169,577.87            Total Liabilities & Equity        $ 169,577.87
Balance Sheet 2017 Revised
        Milestone One
Workbook Instructions        Revised
                    Peyton Approved
                    Balance Sheet
                    As of December 31, 2017
Income Statement 2017
        Preliminary
            Peyton Approved
            Income Statement
            for Year Ended 12/31/2017
        Bakery Sales        $ 327,322.55
        Merchandise Sales        1,205.64
         Total Revenues            328,528.19
        Cost of Goods Sold - Baked        105,834.29
        Cost of Goods Sold - Merchandise        859.77
        Total Cost of Goods Sold            106,694.06
        Gross Profit            221,834.13
        Operating Expenses:
         Rent Expense        24,549.19
         Wages Expense        10,670.72
         Misc. Supplies Expense        3,000.46
         Business License Expense        2,045.77
         Misc. Expense        1,363.84
         Depreciation Expense        677.86
         Insurance Expense        1,091.08
         Advertising Expense        1,549.74
         Interest Expense        818.31
         Telephone Expense        490.98
        Total Operating Expenses:            46,257.95
        Net Income             175,576.18
Income Statement 2017 Revised
        Milestone One
Workbook Instructions
                Revised
                    Peyton Approved
                    Income Statement
                    for Year Ended 12/31/2017
Retained Earnings 2017
            Preliminary
                Peyton Approved
                Statement of Retained Earnings
                for Year Ended 12/31/2017
            Beginning Balance:        $ 50,144.84
             plus Net Income         175,576.18
            less Dividends:        105,000.00
            Ending Balance        $ 120,721.02
Retained Earnings 2017 Revised
        Milestone One
Workbook Instructions
            Revised
                Peyton Approved
                Statement of Retained Earnings
                for Year Ended 12/31/2017
Cash Flow 2017
        Preliminary
                        Peyton Approved
                        Statement of Cash Flow
                        for Year Ended 12/31/2017
            Net Income                $ 175,576.18
            Depreciation Expense                677.86
                                176,254.04
            Increase in Accounts Receivable                    (25,886.91)
            Increase in Baking Supplies                    (8,187.84)
            Increase in Merchandise inventory                    (443.10)
            Increase in Prepaid Rent                    (449.55)
            Increase in Prepaid Insurance                    (1,004.55)
            Increase in Misc. Supplies                    (114.99)
            Increase in Accounts Payable                    3,292.11
            Increase in Wages Payable                    1,850.48
            Increase in Interest Payable                    44.96
                Operating Cash Flow                    145,354.65
            Cash Flow from Investments
            Equipment Purchases                    (6,000.00)
                Cash Flow from Investments                    (6,000.00)
            Cash Flow from Financing
            Repayment of Note Payable                    (10,000.00)
            Dividends Paid                    (105,000.00)
                Cash Flow from Financing                    (115,000.00)
                Net Cash Flow                    24,354
Answered 3 days AfterMay 13, 2022

Answer To : Can I get assistance with the Excell spreadsheet for module one (blue tabs)?

Bhavani answered on May 17 2022
13 Votes
ACC 308 Final Project Workbook
HOME
    Southern New Hampshire University
    ACC 308 - Intermediate Accounting II
    Note: This workbook contains instructions and financial information you will need to complete the Workbook portions of Milestones One and Two. For full instructions for the milestone and final project assignments, refer to the guidelines and ru
ic documents.
You can use the links below to navigate from this HOME tab to additional instructions and the worksheets for each milestone.
    MILESTONE ONE
(Due in Module Three)                MILESTONE TWO
(Due in Module Five)                FINAL PROJECT
(Due in Module Seven)
    Milestone One
Workbook Instructions                Milestone Two
Workbook Instructions                Note: The final proj
ect consists of co
ected work from Milestone One and Milestone Two. Incorporate the feedback you received from your instructor.
    1. Trial Balance                1. Pro Forma Financial Statements
    Using the Peyton Approved financial data, create:                Using the given pro forma information, create:
    Adjusting Entries                Pro Forma Income Statement
    Adjusted Trial Balance                Pro Forma Balance Sheet
    2. Revised Financial Statements                2. Notes to the Financial Statements
                    Note: This part of the project is submitted as a separate Word document. Refer to the Milestone Two Guidelines and Ru
ic document for submission guidelines.
    Using the trial balance and preliminary financial statements, prepare:                Your notes must contain the following:
    Revised Balance Sheet                A. Create appropriate notes as year-to-year documentation for managing depreciation, supplies, and inventory.
    Revised Income Statement                B. Create appropriate notes for long-term debt.
    Revised Retained Earnings Statement
    Revised Statement of Cash Flows
    3. Ratio Analysis                3. Management Analysis Brief
    Note: Refer to the Final Project Scenario for Peyton's ratio formulas.                Note: This part of the project is submitted as a separate Word document. Refer to the Milestone Two Guidelines and Ru
ic document for submission guidelines.
    Using the financial statements from 2015, 2016, and revised 2017, calculate the following ratios:                Your management analysis
ief should explain financial information to management. Provide evidence from your accounting workbook to support your ideas where applicable.
     Cu
ent Ratio (Working Capital)                A. Discuss the impact of the pro forma financial statements for predicting ability to meet future expansion goals.
     Quick Ratio                B. Describe the implications of inventory costing, contingent liabilities, and revenue recognition.
     A/R Turnover                C. Identify potential issues in interpretation of financial information, providing examples to support your ideas.
     Inventory Turnove
     Gross Margin
     Return on Sales
     Return on Equity
     Return on Assets
    4. Management Analysis Brief
    Note: This part of the project is submitted as a separate Word document. Refer to the Milestone One Guidelines and Ru
ic document for submission guidelines.
    Your management analysis
ief should explain financial information to management. Provide evidence from your accounting workbook to support your ideas where applicable.
    A. Assess the company’s financial health based on ratio analyses presented in the accounting workbook.
    B. Compare ratio analysis to trends in financial ratios over time for illustrating their impact, providing examples to support your claims.
    C. Summarize the effects of different compounding periods and interest rates on future value of money.
    D. Explain how alignment to relevant regulations and ethical reporting influenced your accounting practices and notes, providing examples to support your claims.
Milestone One Instructions
        Return
HOME
    INSTRUCTIONS FOR MILESTONE ONE
(Due in Module Three)
    Note: Make sure to completely review the Milestone One Guidelines and Ru
ic document.
    Use the data from this milestone and begin working on your final project, due in Module Seven.
    ITEMS TO COMPLETE FOR MILESTONE ONE:
    The tabs to complete are linked below and colored blue for convenience.
    GENERAL
    You just began a position as a financial accountant at Peyton Approved. In this role, your first task is to prepare the company’s financials for the year-end audit. Additionally, the company is interested in expanding its business within the next year. They would like your support in assessing their ability to meet their goals.
    1. TRIAL BALANCE
    Using the Peyton Approved financial data provided below:
    A. Create the necessary adjusting journal entries. Use the REF column to reference the entry to each event.
    B. Complete the adjusted trial balance.
        Trial Balance 2017
    2. REVISED FINANCIAL STATEMENTS
    Peyton Approved's preliminary financial statements are provided in the yellow tabs.
        Balance Sheet 2017
        Income Statement 2017
        Retained Earnings 2017
        Cash Flow 2017
    Using the preliminary financial statements and the Trial Balance 2017, prepare the following statements:
        Balance Sheet
2017 Revised
        Income Statement
2017 Revised
        Retained Earnings Statement
2017 Revised
        Statement of Cash Flows
2017 Revised
    Note: Refer to Module Three resources for a refresher on statement of cash flows.
    Peyton Approved's previous years' financial statements are provided in the orange tabs.
        Balance Sheet 2015
        Balance Sheet 2016
        Income Statement 2016
    3. RATIO ANALYSIS
    Using the revised 2017 financial statements, 2016 financial statements, and 2015 financial statements, prepare a ratio analysis for Peyton Approved.
    Note: Refer to the Final Project Scenario for Peyton's ratio formulas.
        Ratio Analysis
     PEYTON APPROVED FINANCIAL DATA
    Preliminary financial statements have already been prepared (2017 statements in the Final Project Workbook). Final adjusting entries have not yet been made. See table for possible adjustments that indicate what will be recorded at 12/31/17 (fiscal year end). Use the following to complete year-to-year documentation and notes for managing depreciation, inventory, and long-term debt.
    1. A supplier shipped $3,000 of ingredients on 12/29/17. Peyton receives an invoice for $3,175—goods of $3,000 and freight of $175—all dated 12/29/17. Goods were shipped FOB supplier’s warehouse.
    2. At 12/31/17, Peyton has $200 worth of merchandise on consignment at Bruno’s House of Bacon.
    3. On 12/23/17, Peyton received a $1,000 deposit from Pet Globe for product to be shipped by Peyton in the second week of January.
    4. On 12/03/2017, a mixer with cost of $2,000, accumulated depreciation $1,200, was destroyed by a forklift. As of 12/23/17, insurance company has agreed to pay $700 in January 2018, for accidental destruction.
    4. MANAGEMENT ANALYSIS BRIEF
    Note: This part of the project is submitted as a separate Word document. Refer to the Milestone One Guidelines and Ru
ic document for submission guidelines.
    Your management analysis
ief should explain financial information to management. Provide evidence from your accounting workbook to support your ideas where applicable.
    A. Assess the company’s financial health based on ratio analyses presented in the accounting...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here