Follow the instructions, at the end of the exam you will upl…
Follow the instructions, at the end of the exam you will upload your excel file. Please label your file lastname_finalexam for me I would name my file Dille_finalexam. You can download the word document which lists all of the requirements. ACCT321_Exam2_SV.docx Exam2_Data.xlsx OneDrive link to Data, if attachment does not work This is data from the Thunder Fish Market located in Landlocked, Missouri. The fish market is owned by two brothers James and John Thunder. The fish market has asked you to analyze some of its data. Please answer the following questions. Below is the list of variables. · Date of purchase – Day the fish was purchased by the market · Date of Sale – Day the fish was sold to the customer · Species – Type of Fish · Species_Dummy – Type of fish, given numeric identifier for use in a regression · Weight – The weight of the fish in grams · Length 1 – Vertical length of the fish in Centimeters · Length 2 – The diagonal length in Centimeters · Length 3 – The cross length in Centimeters · Height – The height of the fish in Centimeters · Width – The diagonal width of the fish in centimeters · Customer Type – Lists whether the customer is a consumer or commercial (grocery store, etc) · Selling Price – Price the fish was sold for Fish Price table · Fish – Type of fish · Cost per kilo – cost of fish per kilogram Requirement 1 1. First, we need to transform that data. The weight is listed in grams; we need to transform that to Kilos. a. Create a column called Weight_Kilo i. Multiply Weight by 0.001 2. The cost of the fish is based on weight and the type of the fish. a. Using a VLookup or XLookup create a column listing the cost per fish. The Table “FishPrice” Lists the costs per Kilo. i. Multiply the Weight_Kilo*Cost per kilo 1. You can take a one or two column approach, you will not be penalized for “Helper Columns” 3. Calculate the length of time it takes to sell the fish, label the column Days Held 4. Calculate the gross margin Requirement 2 1. In a new tab, labeled requirement 2 create a pivot table with the following information a. Rows – Species b. Values – i. Average Length 1 ii. Average of Weight Kilo iii. Average Cost Per fish iv. Average Days Held 2. Name the Pivot Table “Descriptive” Requirement 3 1. Create a new tab labeled requirement 3 a. You will use this table to place the results of the following three regressions 2. Regression 1 a. Use Species_Dummy as the X variable b. Use Weight as the Y variable 3. To the right of the regression write your interpretation of the results of regression 1. 4. Regression 2 a. Use Cost as the Y Variable b. Use Weight as the X variable 5. To the right of the regression write your interpretation of the results of regression 2. 6. Regression 3 a. Use Cost as your Y Variable b. Use Length1, Length2, and Length3 as the X variable 7. To the right of the regression write your interpretation of the results of regression 3. Requirement 4 In a new tab, labeled requirement 4 create a pivot table with the following information Rows Date Purchased Values Average of Days held Columns Species Name the pivot table “Days Held by Fish” Click on the newly created pivot table Insert a pivot chart Use a line Chart On the same tab insert a new Pivot table, name this pivot table “Rev&Inv Count” Rows Date of Sale Values Sum of Selling Price – Rename to “Total Revenue” Count of Species – Rename to “Fish available” Insert two pivot charts The first is a Bar Chart The second chart should be line graph