Excel for Accounting Multi-Chapter Project Chapters 8-10 In this project, you will use skills and procedures...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Excel for Accounting Multi-Chapter Project Chapters 8-10 In this project, you will use skills and procedures presented in Chapters 8-10 together as you analyze financial results for Offsides Corporation. Follow all instructions and use the approaches presented in the chapters to present the analysis in good form. Submit your work per your instructor's directions. Offsides Corporation manufactures hockey equipment and installs ice rinks in Boston, MA, and surrounding areas. The owner, Caleb Nelson, has built the business substantially since its inception five years ago. To maintain this growth, Caleb hires you as an outside consultant to help him make a few decisions. To begin, the business has accumulated significant cash reserves over its first three years, and Caleb is considering investing in a particular bond. He would like you to advise him as to whether the $580,000 of currently available cash is sufficient to purchase this bond. Next, Caleb would like to better understand whether operations have improved over the past few years. He wants you to perform a financial statement analysis, including an examination of key ratios, so you can advise him on the performance of the corporation. Finally, Caleb needs assistance in forecasting the company's performance over the next year by establishing both a cash budget and a purchases budget for 2026. He would also like to understand the unit sales necessary to break even and to earn profit at various levels over the next year. Project Requirements Requirement #1. Start a new file. Reduce the width of column A to 0.75 and the height of row 1 to 7.20. Enter the bond details in the range B2:C8. Include a centered header and appropriate titles. Enter the details for a bond with a $580,000 face value, a four-year life, semiannual interest payments, a contract interest rate of 9%, and an effective interest rate of 12%. The bond is issued on 1/1/26. (Hint: Values must be entered here so they can be used in subsequent formulas.) Enter bond calculations for the present value, future value, and payments in the range B10:C13. The future value will generate a result that matches the above figure; you complete this calculation to check your work. Create a bond amortization schedule beginning in cell B15 with the proper headers in row 15. (Chapter 8) Calculate totals for the appropriate columns. Assign an appropriate name to the worksheet tab. Record a macro that generates every formula in the worksheet after the bond details (in the range B2:C8) and dates have been entered. Run the macro to ensure it operates properly. Insert a text box in an appropriate location, add an appropriate name in it, and assign the macro to it. Microsoft Excel for Accounting Page 1 of 4 Chapters 8-10 Project Protect the workbook structure and add a password. Modify cell protection for all cells with formulas in column G to hide the formulas. Save your file as a Macro- Enabled Workbook. Then, using Word or another word-processing program, write a paragraph to summarize the results of the bond amortization schedule and indicate whether Caleb can purchase this bond with the $580,000 presently available. Requirement #2. Use the EA-MCP-FSA file. For the two years showing, complete a horizontal analysis on the income statements and a vertical analysis on the balance sheets. (Chapter 9) Embed two pie charts beside the balance sheet-one to display the asset account percentages for 2024 and another to display the same for 2025. Ensure that each has a chart title, legend, and data labels that are logically displayed. Place a column chart on its own worksheet that displays each 2025 expense account percent change. Include a chart title and worksheet tab title. Create a new worksheet to calculate the quick ratio, debt ratio, and profit margin for 2025 and 2024; use an appropriate worksheet title. Expand the worksheet to include these ratio results: Ratio Current Ratio Debt Ratio Profit Margin 2023 2022 2021 1.60 1.91 1.82 0.37 0.35 0.32 0.45 0.42 0.41 Present the ratios logically and format the cells to properly present the data. Enter cell comments for the largest current ratio and smallest profit margin to indicate that further investigation is needed for each. Then add a response to the profit margin comment, indicating that a temporary market adjustment led to the decrease. Create column sparklines beside the financial ratios to show the trends over the five- year period. Embed a line chart below the financial ratios to display the profit margin for all five years. Include a chart title within the chart. Lastly, adding to the Word document you started for Requirement #1, create a one-paragraph comment discussing company performance. Use data from the horizontal analysis, the vertical analysis, and the ratios to support your assertions, focusing most heavily on the trends evident in the corporation's performance over time. Microsoft Excel for Accounting, 2nd Edition Page 2 of 4 Chapters 8-10 Project Requirement #3. Use the EA-MCP-Budget file to create two budgets and multiple CVP Analyses. (Chapter 10) On the Cash Budget tab, create a cash budget based on a beginning cash balance of $615,100 and this data: Cash Receipts (in-store sales) Cash Receipts (installation services) Reduction in Accounts Receivable Interest Received Sale of Equipment Cash Disbursements (merchandise purchases) Cash Disbursements (operating expenses) Loan Payments (principal) Loan Payments (interest) Also enter the necessary formulas. Set up the budget to print with narrow margins and in Landscape orientation. Adjust the third line of the header to read For Quarters Ended in 2026. Insert page breaks such that data for each quarter appears on a separate page and use the Print Titles option to display the three-line header and the headers in columns A-B on every printed page. Set the print area to include only the cash data through row 15. Create a horizontal split that displays the itemized cash receipts directly above the itemized cash disbursements (only the split bar between them). On the Purchases Budget tab, create a purchases budget based on this data: Budgeted Cost of Goods Sold $89,200 Desired Ending Inventory $32,000 Beginning Inventory $27,400 Quarter Quarter Quarter Quarter #4 #3 #2 #1 $69,000 $64,000 $81,000 $72,000 $49,000 $62,000 $55,000 $54,000 $4,800 $0 $0 $2,100 $800 $700 $700 $0 $15,000 $0 $0 $27,100 $16,500 $22,000 $19,400 $44,300 $48,500 $41,000 $39,200 $2,500 $2,500 $2,500 $2,500 $1,040 $1,060 $1,080 $1,100 $700 Also enter the necessary formulas. Simultaneously adjust the cash budget and purchases budget to display headings and gridlines when printed. On the CVP Analysis #1 tab, create a CVP analysis based on this data: $65 per unit $42 per unit $97,400 Sales Revenue Variable Costs Fixed Costs Enter the necessary formulas but leave the Units Sold cell blank. Use Goal Seek to determine the breakeven number of units. Create a second CVP analysis tab and use Goal Seek to determine the required unit sales to achieve a net income of $15,000. Microsoft Excel for Accounting, 2nd Edition Page 3 of 4 Chapters 8-10 Project Create a third CVP analysis tab that assumes unit sales equal to those calculated in the second CVP analysis but that modifies other assumptions as follows: Assume fixed costs are the same as those in the first two CVP analyses and that they cannot change. Assume sales revenue per unit cannot exceed $66. Assume variable costs per unit cannot fall below $44. Assume the contribution margin must be less than or equal to 42% of sales revenue. Name each CVP tab appropriately. For each CVP analysis, freeze the three-line header at the top. Lastly, in your Word document, write a paragraph that summarizes the findings of the CVP analyses and that discusses the anticipated cash position based on the cash and purchases budgets you completed. Microsoft Excel for Accounting, 2nd Edition Page 4 of 4 Excel for Accounting Multi-Chapter Project Chapters 8-10 In this project, you will use skills and procedures presented in Chapters 8-10 together as you analyze financial results for Offsides Corporation. Follow all instructions and use the approaches presented in the chapters to present the analysis in good form. Submit your work per your instructor's directions. Offsides Corporation manufactures hockey equipment and installs ice rinks in Boston, MA, and surrounding areas. The owner, Caleb Nelson, has built the business substantially since its inception five years ago. To maintain this growth, Caleb hires you as an outside consultant to help him make a few decisions. To begin, the business has accumulated significant cash reserves over its first three years, and Caleb is considering investing in a particular bond. He would like you to advise him as to whether the $580,000 of currently available cash is sufficient to purchase this bond. Next, Caleb would like to better understand whether operations have improved over the past few years. He wants you to perform a financial statement analysis, including an examination of key ratios, so you can advise him on the performance of the corporation. Finally, Caleb needs assistance in forecasting the company's performance over the next year by establishing both a cash budget and a purchases budget for 2026. He would also like to understand the unit sales necessary to break even and to earn profit at various levels over the next year. Project Requirements Requirement #1. Start a new file. Reduce the width of column A to 0.75 and the height of row 1 to 7.20. Enter the bond details in the range B2:C8. Include a centered header and appropriate titles. Enter the details for a bond with a $580,000 face value, a four-year life, semiannual interest payments, a contract interest rate of 9%, and an effective interest rate of 12%. The bond is issued on 1/1/26. (Hint: Values must be entered here so they can be used in subsequent formulas.) Enter bond calculations for the present value, future value, and payments in the range B10:C13. The future value will generate a result that matches the above figure; you complete this calculation to check your work. Create a bond amortization schedule beginning in cell B15 with the proper headers in row 15. (Chapter 8) Calculate totals for the appropriate columns. Assign an appropriate name to the worksheet tab. Record a macro that generates every formula in the worksheet after the bond details (in the range B2:C8) and dates have been entered. Run the macro to ensure it operates properly. Insert a text box in an appropriate location, add an appropriate name in it, and assign the macro to it. Microsoft Excel for Accounting Page 1 of 4 Chapters 8-10 Project Protect the workbook structure and add a password. Modify cell protection for all cells with formulas in column G to hide the formulas. Save your file as a Macro- Enabled Workbook. Then, using Word or another word-processing program, write a paragraph to summarize the results of the bond amortization schedule and indicate whether Caleb can purchase this bond with the $580,000 presently available. Requirement #2. Use the EA-MCP-FSA file. For the two years showing, complete a horizontal analysis on the income statements and a vertical analysis on the balance sheets. (Chapter 9) Embed two pie charts beside the balance sheet-one to display the asset account percentages for 2024 and another to display the same for 2025. Ensure that each has a chart title, legend, and data labels that are logically displayed. Place a column chart on its own worksheet that displays each 2025 expense account percent change. Include a chart title and worksheet tab title. Create a new worksheet to calculate the quick ratio, debt ratio, and profit margin for 2025 and 2024; use an appropriate worksheet title. Expand the worksheet to include these ratio results: Ratio Current Ratio Debt Ratio Profit Margin 2023 2022 2021 1.60 1.91 1.82 0.37 0.35 0.32 0.45 0.42 0.41 Present the ratios logically and format the cells to properly present the data. Enter cell comments for the largest current ratio and smallest profit margin to indicate that further investigation is needed for each. Then add a response to the profit margin comment, indicating that a temporary market adjustment led to the decrease. Create column sparklines beside the financial ratios to show the trends over the five- year period. Embed a line chart below the financial ratios to display the profit margin for all five years. Include a chart title within the chart. Lastly, adding to the Word document you started for Requirement #1, create a one-paragraph comment discussing company performance. Use data from the horizontal analysis, the vertical analysis, and the ratios to support your assertions, focusing most heavily on the trends evident in the corporation's performance over time. Microsoft Excel for Accounting, 2nd Edition Page 2 of 4 Chapters 8-10 Project Requirement #3. Use the EA-MCP-Budget file to create two budgets and multiple CVP Analyses. (Chapter 10) On the Cash Budget tab, create a cash budget based on a beginning cash balance of $615,100 and this data: Cash Receipts (in-store sales) Cash Receipts (installation services) Reduction in Accounts Receivable Interest Received Sale of Equipment Cash Disbursements (merchandise purchases) Cash Disbursements (operating expenses) Loan Payments (principal) Loan Payments (interest) Also enter the necessary formulas. Set up the budget to print with narrow margins and in Landscape orientation. Adjust the third line of the header to read For Quarters Ended in 2026. Insert page breaks such that data for each quarter appears on a separate page and use the Print Titles option to display the three-line header and the headers in columns A-B on every printed page. Set the print area to include only the cash data through row 15. Create a horizontal split that displays the itemized cash receipts directly above the itemized cash disbursements (only the split bar between them). On the Purchases Budget tab, create a purchases budget based on this data: Budgeted Cost of Goods Sold $89,200 Desired Ending Inventory $32,000 Beginning Inventory $27,400 Quarter Quarter Quarter Quarter #4 #3 #2 #1 $69,000 $64,000 $81,000 $72,000 $49,000 $62,000 $55,000 $54,000 $4,800 $0 $0 $2,100 $800 $700 $700 $0 $15,000 $0 $0 $27,100 $16,500 $22,000 $19,400 $44,300 $48,500 $41,000 $39,200 $2,500 $2,500 $2,500 $2,500 $1,040 $1,060 $1,080 $1,100 $700 Also enter the necessary formulas. Simultaneously adjust the cash budget and purchases budget to display headings and gridlines when printed. On the CVP Analysis #1 tab, create a CVP analysis based on this data: $65 per unit $42 per unit $97,400 Sales Revenue Variable Costs Fixed Costs Enter the necessary formulas but leave the Units Sold cell blank. Use Goal Seek to determine the breakeven number of units. Create a second CVP analysis tab and use Goal Seek to determine the required unit sales to achieve a net income of $15,000. Microsoft Excel for Accounting, 2nd Edition Page 3 of 4 Chapters 8-10 Project Create a third CVP analysis tab that assumes unit sales equal to those calculated in the second CVP analysis but that modifies other assumptions as follows: Assume fixed costs are the same as those in the first two CVP analyses and that they cannot change. Assume sales revenue per unit cannot exceed $66. Assume variable costs per unit cannot fall below $44. Assume the contribution margin must be less than or equal to 42% of sales revenue. Name each CVP tab appropriately. For each CVP analysis, freeze the three-line header at the top. Lastly, in your Word document, write a paragraph that summarizes the findings of the CVP analyses and that discusses the anticipated cash position based on the cash and purchases budgets you completed. Microsoft Excel for Accounting, 2nd Edition Page 4 of 4
Expert Answer:
Related Book For
Advanced Accounting
ISBN: 978-0077431808
10th edition
Authors: Joe Hoyle, Thomas Schaefer, Timothy Doupnik
Posted Date:
Students also viewed these accounting questions
-
Before you begin this assignment, be sure you have read the UMUC Family Clinic Case Study below and your Week 1 and Week2 syllabus readings, which discuss EHR functionality and Process...
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
Upland Co.'s inventory records showed the following data accounted for in a perpetual inventory system. Date Units Unit Cost June 1 Inventory 1,250 $8.00 June 3 Purchases 2,500 8.40 June 7 Sales (at...
-
You are considering opening another restaurant in the TexasBurgers chain. The new restaurant will have annual revenue of $300,000 and operating expenses of $150,000. The annual depreciation and...
-
Old Town Entertainment has two employees in Year 1 . Clay earns $ 3 , 5 0 0 per month, and Philip, the manager, earns $ 1 0 , 0 0 0 per month. Neither is paid extra for working overtime. Assume the...
-
Loris Catering, Inc., purchased $400 of supplies on account. The journal entry to record this transaction is denoted by which of the following? DATE ACCOUNTS POST REF. DR. CR. a. Inventory 400...
-
Keystone Computer Timeshare Company entered into the following transactions during May 2015. 1. Purchased computer terminals for $20,000 from Data Equipment on account. 2. Paid $3,000 cash for May...
-
) XYZ Company manufactures and sells one product. The following information pertains to eachof the company's first three years of operations: Variable costs per unit: Manufacturing: Direct materials...
-
The adjusted trial balance of Gibson Company for the year ended December 31, 2019, is as follows: Instructions Prepare a multiple-step income statement, retained earnings statement, and a classified...
-
Klumper Corporation is a diversified manufacturer of industrial goods. The company's activity-based costing system contains the following six activity cost pools and activity rates: Activity Cost...
-
If a telescope has objective and eyepiece lenses with focal lengths respectively of 104 cm and 2.90 cm, determine the distance needed between these two lenses in order to produce a final image far...
-
The telescope at a small observatory has objective and eyepiece focal lengths respectively of16.6mand12 cm.If this telescope is used to view a1660mdiameter lunar crater on the surface of the moon...
-
Throughout the years, the medical record has experienced several transformations.Historically, healthcare organizations used paper records. Then, there was a transition to hybrid records, and now...
-
The Earth has a mass of 6* 1024 kg and orbit the Sun in 3.15 107 seconds at a constant radius of 1.5 101 m. Find the following: 1) What is the Earth's centripetal acceleration around the Sun? 2) What...
-
A local healthcare system has hired you to develop and implement a virtual heart failure clinic in the next 16 months. As the next part of the project plan, you must develop a detailed communication...
-
Q.10 A graph is a collection of.....? A. Row and columns B. Vertices and edges C. Equations D. None of these Answer: Q.11 A minimal spanning tree of a graph G is....? A. A spanning sub graph B. A...
-
A 2500-lbm car moving at 15 mi/h is accelerated at a constant rate of 15 ft/s 2 up to a speed of 50 mi/h. Calculate force and total time required?
-
The Willingham Museum qualifies as a component unit of the City of Willingham. How does an activity or function qualify to be a component unit of a primary government?
-
1. A local citizen gives a not-for-profit organization a cash donation that is restricted for research activities. The money should be recorded in a. Unrestricted Net Assets. b. Temporarily...
-
Following are preacquisition financial balances for Padre Company and Sol Company as of December 31. Also included are fair values for Sol Company accounts. On December 31, Padre acquires Sols...
-
What are property, plant and equipment (PPE) assets? What are the major classes of PPE assets? At what value should PPE assets be recorded?
-
What are capital expenditures?
-
What are the two main types of leases?
Study smarter with the SolutionInn App