VIEW: Instructions Previous Attempts Best Practices Independent Project 5-4 Windows   Mac Wilson Home Entertainment Systems monitors cash flow at their individual locations separately and consolidates data. After the summary is complete, you insert hyperlinks to each of the supporting worksheets. [Student Learning Outcomes 5.1, 5.4, 5.6, 5.7, 5.8] Files Needed: WilsonHome-05.xlsx (Available from the Start File link.) and WHES.png (Available from the Resources link.) Completed Project File Name: [your name]-WilsonHome-05.xlsx Skills Covered in This Project Group and format worksheets. Create a static data consolidation with SUM. Insert a picture from a file. Insert a hyperlink. Copy a hyperlink. Encrypt a workbook with a password. Open the WilsonHome-05 start file. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it. Group all the worksheets. Edit and format grouped sheets. Select cells A1:B2 and click the Launcher in the Alignment group [Home tab]. Choose Center Across Selection from the Horizontal list and click OK.Figure 5-76 Consolidate dialog box for cash flow Click the Launcher in the Page Setup group [Page Layout tab] and click the Margins tab. Choose Horizontally from the Center on page list and click OK. Edit the contents of cell A10 to read Cash paid for marketing. Select cell A1 and ungroup the sheets. Select the CashFlow sheet.Figure 5-77 Image positioned as title Build a static data consolidation for the Cash flow from operations section. Select cells B4:B12. Use SUM to consolidate the data from the three location sheets without links. (Figure 5-76). Build a static data consolidation for the Cash flow from banking and investment section in cells B15:B21. Delete the references in the Consolidate dialog box and use SUM as the function.Figure 5-78 Hyperlink text to switch to Cash Flow sheet Build a static data consolidation for the Cash balance at the beginning of the quarter amounts in cell B24 with SUM as the function. Insert a picture from a file. Delete the contents of cell A1 on the CashFlow sheet. Click cell D2. Click the Pictures button [Insert tab, Illustrations group]. Find and select WHES from your student data files. Click Insert. The picture is placed at a default size. Click the Height box [Picture Tools Format tab, Size group]. Type 1.2 to replace the default height and press Enter. Format the height of row 1 to 86.25 (115 pixels). Point to the logo frame to display a move pointer. Drag the image to appear in cell A1 as a main label for the worksheet (Figure 5-77). Click cell D2 to deselect the image. Insert and copy a hyperlink. Click cell C3 on the Peoria worksheet. Create a hyperlink that displays Total Cash Flow and switches to cell A1 on the Cash Flow worksheet (Figure 5-78). Right-click cell C3 and choose Copy from the menu. Select the Champaign sheet tab and paste the hyperlink in cell C3. Select the Rockford sheet tab and paste the hyperlink in cell C3. Select the Peoria sheet, and press Esc to remove the copy marquee if it is still visible. Select cell C5 and then click the cell with the hyperlink to test it. Save and close the workbook (Figure 5-79). Upload and save your project file. Submit project for grading.

PART II You are a staff accountant at Anderson & Paul, a CPA firm located in downtown San Diego.  A long-time client, Brady Patterson, has contacted you with a request.  Brady is 52 years old and is currently in the 32% tax bracket.  He is beginning to think about saving for retirement and has some questions.  His employer does not offer a retirement plan so he is considering an IRA.  However, he is confused about the differences between a Roth IRA and a Traditional IRA and needs your advice.  He wants you to prepare an Excel spreadsheet that he can use to see the difference in a Traditional versus a Roth IRA.  Based on your discussion with Brady, the Excel model should cover a ten-year period.  Brady would like to be able to change the following items in the model: Year analysis begins Amount of the IRA contribution (Note: only one contribution in this analysis – not one per year) Tax rate at date of contribution (Note: assume the contribution is made on January 1 of the initial year) Annual growth rate (Note: assume constant growth throughout the analysis period) Tax rate at date of withdrawal (Note: assume the entire account balance is withdrawn at the end of the tenth year) Prepare the model so that the above input variables automatically update the worksheet.  This will allow Brady to test various assumptions and see the results of contributing to a Roth or a Traditional IRA.  The model should be based on a contribution only in the first year.  Don’t build the model to have contributions every year.  The contribution should grow at the annual growth rate over a ten-year period.  At the end of the ten years, the entire account balance is distributed to the account holder.  The model should provide the net-of-tax amount the account holder will receive on distribution after the ten-year period and show the account balance each year.  Prepare one table showing the Roth IRA and one showing the Traditional IRA.  Both tables and the input variable section should easily fit on a single page if set to print in landscape mode.  Remember, a professional presentation includes proper titles and headings, is centered on the page when printed, and is a reasonable size on the page.  Test the appearance of the worksheet by using the print preview function in Excel.  Professional appearance is important so don’t overlook this part of the assignment. The second page of your Excel model should be a graph showing the account balance at each year-end for the Roth and Traditional IRAs.  The final year data point should be the net of tax amount of the distribution. As you know, a contribution to a traditional IRA typically provides a deduction for the taxpayer while a contribution to a Roth IRA is after-tax.   Therefore, the contribution amount in full will be the starting point for the Traditional IRA table.  However, since a Roth IRA contribution is taxed, the net of tax amount of the contribution will be the starting point for the Roth IRA table. Check figure:  If you have prepared the worksheet correctly, the net of tax distribution amount will be the same for the traditional and Roth IRA if the tax rate on contribution and the tax rate on distribution are the same.  Assignment: This assignment counts as a quiz.  There are three parts to this assignment.  The working Excel model. I will test the model.  The properly working model and graph will count as 80% of your quiz grade. Your observation of what the model tells you about a Roth versus a Traditional IRA. Can you make a generalization about when it is best to use one over the other?  Use your model to make this determination.  This will count as 10% of your quiz grade. Your graph showing the account balance at each year-end for the Roth and Traditional IRAs. This will count as 10% of your quiz grade Upload a single Excel file with your observation about choosing between a Roth versus a Traditional IRA, the model worksheet, and the charts.  Save all three as a single file and then upload via Canvas, prior to the deadline. 

COURSE PROJECT/CHECKPOINT ASSIGNMENTEach student will be assigned a company ( Nike )for which they will conduct a financial analysis. Included in the analysis will be the following items: Background Information/Environmental ScanDuPont IdentityCurrent RatioQuick RatioInventory Holding PeriodAverage Collection PeriodDebt RatioFree Cash FlowMarket Value Added Economic Value AddedDividend YieldRequired Rate of Return based on the Capital Asset Pricing Model After calculating the above items, the student will make a recommendation to buy, sell, or hold the company’s common stock at its current price. The recommendation should be based on the calculations required plus any other information about the company that the student believes pertinent. In a separate section of the paper, the student will describe a situation in which his/her company would make use of capital budgeting. No calculations are required. This section of the paper would be about one to two pages describing the process that the company would make use of for analyzing the capital budgeting situation. In a third section of the paper, the student should describe how the company would use cost-volume-profit analysis for one of its products. No calculations are necessary; just provide an explanation of how the process works. This section of the paper should be one to two pages.Grading for the analysis will be based on the following: Environmental Scan/Background 20 pointsRatio Calculations 15 pointsFree Cash Flow Calculations 8 pointsMarket Value Added? Economic Value Added 7 pointsCapital Budgeting Discussion 15 pointsCost-Volume-Profit Analysis Discussion 15 pointsConclusions 10 pointsQuality of Writing and Sources 10 pointsExtra credit is not applicable or available in this course.Rubric for Company Analysis BUS 311, Financial Management Critical AssignmentThe critical assignment for BUS 311, Financial Management is one of a series of assignments designed to demonstrate that students have achieved the learning outcomes established for the majors in the College of Business and Management. The specific objective of this critical assignment is to assure that students have a sufficient level of accounting and finance skills to conduct financial analyses of an organization. These exercises will provide students with the opportunity to integrate and implement theory to practice and will prepare students to develop a comprehensive business plan in the capstone course during the final year of study. For the purpose of this exercise, students will choose a company for which they will conduct an environmental scan and a financial analysis. The analysis of the company should culminate with students recommending that the company is “excellent,” “healthy,” or “ill.” Students will be evaluated based on correctness of the calculations, the insight of the analysis, and the quality of the discussion relating to the requirements. Before performing the financial analysis, students will prepare an environmental scan, which will discuss the various forces with which an organization must contend to assure that its business model has a chance for success. The specific calculations should include:DuPont IdentityCurrent RatioQuick RatioInventory Holding PeriodAverage Collection PeriodDebt RatioDividend YieldRequired Rate of Return based on the Capital Asset Pricing ModelFree Cash FlowMarket Value Added Economic Value Added Additionally, students will be expected to prepare an executive summary of the process of capital budgeting, using an example that is specific to the company that is being analyzed. An executive summary of cost-volume-profit analysis is also required, using an example that is specific to the company that is being analyzed. The written paper is to be typed double–spaced, 1’ margins, Times New Roman and 12-point font, with correct spelling and grammar, proper citation, references, with a cover page and organized with headings. The instructor’s approval of the topic is required. The following Library resources and websites could assist students in conducting the research to do the Critical Assignment: Lynn Library Sources: Business majors can also avail themselves of all Lynn University Library databases (Links to an external site.) and the business specific databases (Links to an external site.) such as: ABI/Inform, Business Insights: Essentials, Passport by Euromonitor, and Statista. Directions to access: Go to Lynn University Website: www.lynn.edu (Links to an external site.)Click the Libraries Tab Click DatabasesClick on the « All Subject » dropdown= Business http://lynnlibrary.libguides.com/az.php?s=43296 (Links to an external site.) Websites: www.zacks.com (Links to an external site.) www.bloomberg.com/markets/rates/index.html (Links to an external site.) www.moodys.com (Links to an external site.) www.bondsonline.com (Links to an external site.) www.federalreserve.gov (Links to an external site.) www.morningstar.com (Links to an external site.) http://finance.yahoo.com/ (Links to an external site.) http://www.gurufocus.com/ (Links to an external site.) https://www.google.com/finance (Links to an external site.) Student Learning Outcomes: Students will be able to:Conduct an environmental scan, indicating future prospects for a company. (SLO 3.3, SLO 7.4, SLO 12.3) (20% of grade for project)Calculate and interpret financial ratios to ascertain the overall health of a company. (SLO 1.2, SLO 6.1, SLO 13.4) (15% of grade for project)Determine and explain free cash flow to assess the cash position for a company. (SLO 6.2, SLO 10.4, SLO 13.4) (8% of grade for project)Determine and explain the market value added and economic value added to ascertain whether a company is creating or destroying value. (SLO 6.2, SLO 13.4) (7% of grade for project)Specifically describe the capital budgeting process, in particular the net present value method in order to demonstrate the importance of the timing and risks associated with cash flows. (SLO 6.3, SLO 6.5, SLO 10.4, SLO 13.3, SLO 13.4) (15% of grade for project)Specifically describe cost-volume-profit analysis for the company to assure the understanding of the elements of operating income. (SLO 1.5, SLO 11.6, SLO 13.3, SLO 13.4) (15% of grade for project)Make a recommendation that the company’s financial condition is: “excellent”, “healthy”, or “ill”. (SLO 13.3, SLO 13.4) (10% of grade for project)Demonstrate proficient writing skills (SLO 13.6) (10% of grade for project)