INTRODUCTION This exercise is comprised of four parts. Part 1 covers automated routines macros in Excel to rearrange and transform data, Part 2 covers Pivot Table: Data Sets Cross-Tabulation and Querying in Excel to extract and summarize data , Part 3 covers using Microsoft Excel spreadsheet software to construct, automate and analyze what-if-scenario for a sample budget/investment to help in decision making, and Part 4 has selected powerful Excel functions to assist in data analyses. Macros may not work on a Mac computer (relative reference feature in Macros was disabled in latest Excel for Mac!). Without this feature Macros will not work on a Mac computer. You can do the Macros Part 1 on a PC computer, then proceed working with the copy of restructured data using your Mac computer. Another advanced (complex) approach is to virtually install Windows PC on your Mac (this is on your own approach- not recommended and not supported by instructor). Please follow all display and formatting instructions for Part 3 (use WebEX class instructions too to save reading many formatting details in Part 3). Also, data set used every semester may change; the instructions below may reference range of cells for 12 months not 15 months; you adapt the months instructions based on the data set given/assigned on Blackboard (Bb). YOU ARE EXPECTED TO DO YOUR OWN WORK. Feel free, of course, to make use of the excellent help feature of Excel. If you work with other students, Do Not share your work with other students, or copy another students work into your Excel file/workbook. PART 1: CREATING AND EDITING A MACRO [5 POINTS] To automate a repetitive task so that you can do the task again with a single click, you can record a macro in Excel. (Microsoft Office documentation). Make sure you download or save the Excel file Excel_Macro_Data Analysis_Template_semesterYY.xlsm on Blackboard (Bb) to your hard-drive, then work off your workstation. DO NOT invoke Excel by double-clicking or opening the file on Blackboard as this will allow you to open the Excel file on the internet utilizing most but maybe not all Excel 2 functionality and you may NOT have ownership of the file you work with and may not be able to save your work. Although this limitation is fading with new advanced Cloud computing, it may still be an issue depending on the set-up of your PC and software). 1. Download the Excel file from Blackboard (filename: Excel Macro_Data Analysis_Template_semesterYY.xlsm) [click on file and select Save from the prompt screen or download then copy to desktop or USB depending on your browser]. 2. Then open the file using Excel on your desktop. 3. Sometimes your computer protects your files from viruses and open it in Protected View. To exit and start edit, click Enable Editing on the Message Bar. 4. Enter your name (in cell B3 of Macro sheet) and student number (in cell B5 of Macro sheet without the leading character A) before you start creating the macro. [make sure you do not delete any columns or rows in Macro sheet or override format of cells B3 and B5] 5. Write a macro to move the data for the fields (columns) Date, Price, Shares, Account and Amount into five columns under the appropriate headings (i.e., the date data should begin in A21 and end around A386 and the price data should begin in B21 and end around B386 (or after/higher row# depending on data set given- maybe to cell B476, etc.). When recording macros, make sure you are viewing the stop macro toolbar and set the recorder to record relative cell references. Hints i. Use cut and paste commands and use end (up, down, right or left) commands where possible when constructing the macro. ii. Macros are used to automate repetitive actions and minimize manual spreadsheet keystrokes and activities. Your macro should not be more cumbersome than manual tasks and should not exceed more than ten lines of commands (class discussion-*01). *01- The macro should not have recordings of repetitive/duplicate individual cut and paste steps; this will create 5-6 pages of programming code involving doing the whole restructuring of the data manually with more than 64 cuts & pastes!!! NOT ACCEPTABLE and defeats the sole purpose of macros. 3 PART 2: PIVOT TABLE (EXCEL DATABASE FUNCTION) [10 POINTS] After ending with five columns of consecutive daily sales of shares, the data will be copied and used in Pivot worksheet (As an Internal Control DO NOT use the original data. Use the Paste Options to paste the values in Pivot sheet). Then, generate the following reports to help management perform trend analyses on summarized data: 1. Report 1: a report that totals the Amount of sales by ACCOUNT across Months. 2. Report 2: summarizing the total amount of sales by Industry type of shares sold across Months [report should be categorized by industry number, ignore description of industry type see data dictionary item (ii) below]. 3. Report 3: produce a Line chart to show the total monthly sales trend for the period given (place chart below the two pivot tables). Write two sentences analyzing/describing what the chart represents and the summary conclusion(s) revealed by the line chart. 4. HINTS: a. Use Pivot Table in Excel (place the Pivot Tables on the right of the data in same Pivot worksheet). b. Data Dictionary for Excel Downloaded Data. Account field or attribute is an alphanumeric field which is 7 digits or characters long. The field is composed of the following: Digits 1-3, indicate Revenue-Sales account (400-General Ledger Chart of Accounts). Digits 4-5, indicate industry type of shares sold (Energy, International, Service, etc. type of funds). Digits 6-7, indicate division or broker/custodian (financial institution) making the sale. PART 3: CAPITAL INVESTMENT WHAT-IF ANALYSIS [25 POINTS] [Please see AUTOMATION AND MINIMAL MANUAL DATA ENTRY for Part 3 below] Circular Solutions Corporation is considering an expansion of their manufacturing capacity in order to produce a new golf ball to be introduced to the market. 4 The equipment will cost $270,000 to purchase and install. Incremental cash collections from sales are projected to be $86,000 for the first year, grow by $25,000 for the second year, and another $10,000 (compared to year 2) for years 3-7, decreasing thereafter by $5,000 each year through the remainder of the 10 years total life of the equipment as the popularity of the ball declines. Incremental cash disbursements for product costs and operating expenses are projected to be $55,000 in the first year, increasing by $2,500 each year thereafter throughout the life of the project. In addition, a major repair is expected to require one-time $35,000 cash expenditure (expense) at the end of the fifth year. With the exception of the initial investment, which will occur at the beginning of the first year (i.e., at time 0), all cash flows can be assumed to occur at the end of the period. IMPORTANT The equipment will be depreciated using the straight-line method with no salvage value. Income taxes are projected to be 23% of pre-tax income. Assume full tax recovery of any losses (not realistic assumption but required for simplification purposes). Circular Co. uses 10.5% cost of capital to evaluate its investment opportunities. INSTRUCTIONS On the Capital sheet of the Excel file you downloaded, prepare a sheet that computes the net present value (NPV), internal rate of return (IRR), and payback period for the proposed investment. Your solution must utilize the time value features of Excel to calculate the NPV and IRR. The payback period is to be undiscounted. The spreadsheet should have the similar appearance as the example shown on the last page (the example is shown for formatting guide only; it does NOT present the solution). Cash outflows should be shown as negative values and cash inflows should be shown as positive values. Row titles and column headings should be the same as shown. Make sure columns width and rows height are 5 adequate to present data clearly on the screen and if printed (format essential criterion: make your work user-friendly, if not user(s) will be upset and cost you points). 1. Header: a. In the upper left: enter the date the exercise is submitted, your name as the analyst, and the last four digits of your student (A number) as the employee ID. b. In the upper center: i. Enter the initial investment (format as accounting, no $ sign, no decimals) ii. The tax rate (format as percentage with one decimal) iii. The discount rate (format as percentage with one decimal) in cells at the top left of the spreadsheet (see example). c. In the upper right: display the project evaluation as either: i. Net Value (NPV) > 0: Accept Project ii. Net Value (NPV)

i need to know how to get income before tax, tax, income after tax, add back dep. exp., incremental cash flow and cumulative cash flow

Circular Solutions Corporation is considering an expansion of their manufacturing capacity in order to produce a new golf ball to be introduced to the market. 3 . The equipment will cost $270,000 to purchase and install. Incremental cash collections from sales are projected to be $86,000 for the first year, grow by $25,000 for the second year, and another $10.000 (compared to year 2) for years 3-7. decreasing thereafter by $5,000 each year through the remainder of the 10 years total life of the equipment as the popularity of the ball declines. Incremental cash disbursements for product costs and operating expenses are projected to be $55,000 in the first year, increasing by $2,500 each year thereafter throughout the life of the project In addition, a major repair is expected to require one-time $35.000 cash expenditure (expense) at the end of the fifth year, With the exception of the initial investment, which will occur at the beginning of the first year (i.e, at time 0), all cash flows can be assumed to occur at the end of the period. . . The equipment will be depreciated using the straight-line method with no salvage value. Income taxes are projected to be 23% of pre-tax income. Assume full tax recovery of any losses (not realistic assumption but required for simplification purposes). Circular Co. uses 10.5% cost of capital to evaluate its investment opportunities. INSTRUCTIONS On the 'Capital' sheet of the Excel file you downloaded, prepare a sheet that computes the net present value (NPV), internal rate of return (IRR), and payback period for the proposed investment. Your solution must utilize the time value features of Excel to calculate the NPV and IRR. The payback period is to be undiscounted. The spreadsheet should have the similar appearance as the example shown on the last page (the example is shown for formatting guide only; it does NOT present the solution). Cash outflows should be shown as negative values and cash inflows should be shown as positive values. Row titles and column headings should be the same as shown. Make sure columns width and rows' height are 4 adequate to present data clearly on the screen and if printed (format essential criterion: make your work user-friendly". if not user(s) will be upset and cost you points) 1 Capital Investment Analysis 7/1/2021 Date 10 Analyst 11 Employee ID: Project Evaluation investment Tax Rates Discount Rates 220,000 23% 10.5 CONE O 1 2 4 G 3 5 10 ( 2000.00 6,000.00 155,000.00 111,000.00 (57.500.000 121.000,00 160,000.00 13 14 I Can Flow Clements 16 Investment 12 cales Operative expenses 10 Repair expenses 10 Depreciation expenses 21 income before tas 22 Tar 2) income after tax 24 Adid back depen 25 incremental cash flow 121,000.00 16.2.500.00 121,000.00 167,500.00 121,000.00 165,000.00) (25,000.00 127.000,00) 121,000,00 170,000.00 110,000.00 122,500.00 111.000.00 106,000.00 175.000,00) 77.500.00) (27.000.00 (27.000.00 22.000,00) 27.000.00) (27.000,00 227.000.00) (27,000.00 (27,000.00 22.000,00) (270,000.00 27 Cumulative cash flow 1270.000.000 270,000.00 DE 29 Payback period fysis Net Present Value internal Rate of Return 22 11 34 35 Macro Prot ** Capital Budget Excel Functions + Circular Solutions Corporation is considering an expansion of their manufacturing capacity in order to produce a new golf ball to be introduced to the market. 3 . The equipment will cost $270,000 to purchase and install. Incremental cash collections from sales are projected to be $86,000 for the first year, grow by $25,000 for the second year, and another $10.000 (compared to year 2) for years 3-7. decreasing thereafter by $5,000 each year through the remainder of the 10 years total life of the equipment as the popularity of the ball declines. Incremental cash disbursements for product costs and operating expenses are projected to be $55,000 in the first year, increasing by $2,500 each year thereafter throughout the life of the project In addition, a major repair is expected to require one-time $35.000 cash expenditure (expense) at the end of the fifth year, With the exception of the initial investment, which will occur at the beginning of the first year (i.e, at time 0), all cash flows can be assumed to occur at the end of the period. . . The equipment will be depreciated using the straight-line method with no salvage value. Income taxes are projected to be 23% of pre-tax income. Assume full tax recovery of any losses (not realistic assumption but required for simplification purposes). Circular Co. uses 10.5% cost of capital to evaluate its investment opportunities. INSTRUCTIONS On the 'Capital' sheet of the Excel file you downloaded, prepare a sheet that computes the net present value (NPV), internal rate of return (IRR), and payback period for the proposed investment. Your solution must utilize the time value features of Excel to calculate the NPV and IRR. The payback period is to be undiscounted. The spreadsheet should have the similar appearance as the example shown on the last page (the example is shown for formatting guide only; it does NOT present the solution). Cash outflows should be shown as negative values and cash inflows should be shown as positive values. Row titles and column headings should be the same as shown. Make sure columns width and rows' height are 4 adequate to present data clearly on the screen and if printed (format essential criterion: make your work user-friendly". if not user(s) will be upset and cost you points) 1 Capital Investment Analysis 7/1/2021 Date 10 Analyst 11 Employee ID: Project Evaluation investment Tax Rates Discount Rates 220,000 23% 10.5 CONE O 1 2 4 G 3 5 10 ( 2000.00 6,000.00 155,000.00 111,000.00 (57.500.000 121.000,00 160,000.00 13 14 I Can Flow Clements 16 Investment 12 cales Operative expenses 10 Repair expenses 10 Depreciation expenses 21 income before tas 22 Tar 2) income after tax 24 Adid back depen 25 incremental cash flow 121,000.00 16.2.500.00 121,000.00 167,500.00 121,000.00 165,000.00) (25,000.00 127.000,00) 121,000,00 170,000.00 110,000.00 122,500.00 111.000.00 106,000.00 175.000,00) 77.500.00) (27.000.00 (27.000.00 22.000,00) 27.000.00) (27.000,00 227.000.00) (27,000.00 (27,000.00 22.000,00) (270,000.00 27 Cumulative cash flow 1270.000.000 270,000.00 DE 29 Payback period fysis Net Present Value internal Rate of Return 22 11 34 35 Macro Prot ** Capital Budget Excel Functions +