Decision Making Excel - CIS 1220

I need help this assignment and how to complete it?

My instructor provide attachment of Cattle Data File .xlsx along with this assignment

Data Items # Head of cattle # Head bought/sold per year Days/year Tax rate $/lb Calf $/lb Steer/heifer (mature) $/hour Labor Hours/wk Labor #./lbs per Calf #/lbs Steer/heifer (mature) $ $ $ 300 200 365 15% 0.74 0.75 15.00 15 400 1,400 Feed Requirements and Cost of Feed Age Category Calf yearling Mature Total Weight Category 600 900 1400 ID 383 230 118 Age Category calf calf calf 197 yearling 235 119 yearling yearling 273 calf 311 120 yearling calf 349 calf 387 121 calf calf 425 yearling 463 122 calf yearling 501 yearling 539 123 calf calf 577 yearling 615 124 mature calf 653 yearling 691 mature 125 yearling 729 767 126 805 843 127 881 919 128 957 995 129 033 071 130 109 147 131 185 223 132 261 299 133 337 375 134 413 451 135 489 527 136 565 603 137 641 679 138 717 755 139 793 831 140 869 907 141 945 983 142 021 059 143 097 mature yearling mature yearling mature yearling yearling calf yearling calf calf yearling yearling calf yearling mature yearling yearling calf yearling yearling yearling mature mature yearling calf mature yearling calf yearling calf calf yearling calf yearling calf yearling yearling calf calf calf calf yearling calf calf yearling calf yearling yearling calf yearling yearling calf yearling calf Pounds of Feed per Animal per Day Corn 14.48 17.59 22.47 54.54 Weight Category Feed Prices Hay Soy 5.17 2.69 3.97 11.83 Corn Total 1.03 0.41 0.00 1.45 Hay Corn 20.69 20.69 26.44 67.82 Soy Price Unit Price/lb Hay 50 ton Soy 0.02 pound 0.025 0.02 pound 0.02 0.02 135 144 173 211 145 249 287 146 325 363 147 401 439 148 477 515 149 553 591 150 629 667 151 705 743 152 781 819 153 857 895 154 933 971 155 009 047 156 085 123 157 161 199 158 237 275 159 313 351 160 389 427 161 465 503 162 541 579 163 617 655 164 693 731 165 769 807 166 845 883 167 921 959 168 997 035 169 073 111 170 149 187 171 225 263 172 301 339 173 377 415 174 453 491 175 529 yearling yearling yearling yearling yearling yearling yearling calf yearling calf yearling yearling calf yearling calf yearling yearling calf calf yearling yearling yearling yearling yearling yearling calf yearling yearling yearling yearling yearling yearling mature yearling calf calf yearling yearling yearling yearling calf yearling yearling calf yearling calf yearling yearling yearling yearling yearling yearling yearling calf yearling yearling yearling yearling calf yearling yearling yearling yearling calf calf mature yearling yearling yearling yearling yearling yearling calf yearling calf calf yearling yearling yearling calf yearling calf yearling yearling calf yearling yearling yearling yearling calf calf yearling yearling calf yearling calf 567 176 605 643 177 681 719 178 757 795 179 833 871 180 909 947 181 985 023 182 061 099 183 137 175 184 213 251 185 289 327 186 365 403 187 441 479 188 517 555 189 593 631 190 669 707 191 745 783 192 821 859 193 897 935 194 973 011 195 049 087 196 125 163 197 201 239 198 277 315 199 353 391 200 429 467 201 505 543 202 581 619 203 657 695 204 733 771 205 809 847 206 885 923 207 961 yearling mature yearling yearling calf mature yearling yearling calf yearling calf yearling calf yearling calf yearling mature yearling yearling calf yearling yearling yearling yearling mature yearling yearling mature yearling calf yearling calf calf yearling yearling yearling calf yearling calf calf calf yearling yearling yearling calf calf yearling calf calf yearling calf yearling yearling calf yearling calf yearling yearling yearling yearling yearling yearling yearling calf yearling yearling yearling yearling calf yearling yearling yearling yearling calf calf yearling yearling yearling yearling yearling yearling calf yearling calf calf calf yearling yearling mature yearling calf calf yearling yearling yearling yearling 999 208 037 075 209 113 151 210 189 227 211 265 303 212 341 379 213 417 455 214 493 531 215 569 607 216 645 683 217 calf yearling yearling calf yearling calf yearling yearling yearling yearling yearling yearling yearling calf yearling yearling yearling yearling calf yearling yearling calf yearling calf calf mature yearling calf calf Buckley's Cattle Income Statement Income Sales Revenue Cost of Sales Gross Profit Operating Expenses New Calves Cost of Feed Loan Payments Veterinary Expenses Labor Expenses Total Operating Expenses Profit Before Taxes Taxable Income Taxes (15%) Profit After Taxes Profit per Head CIS 1220 Spring 2016 Application Section Cattle Ranch Problem Statement You own a small ranch in Minnesota. You have recently began a cattle business on your ranch. You own 300 cattle. You feel that your business is at a point where you can sell mature cattle and buy more calves and raise them for sale. A nearby cattle-breeding operation can provide a good supply of calves at less than the market rate, and because you only feed your cattle organic feed, you believe you can receive a premium for them at market. This will be the first year that you are actually selling mature cattle and then replacing them by purchasing calves. Before you continue you want to evaluate your current situation and the viability of continuing in the cattle business. You have collected a lot of information on cattle pricing, feeding, housing, and veterinary needs. You will use this data to complete the steps in the project. Data Files: Cattle Data File.xlsx (in d2L) Name the file Buckley's Ranch. Save often!! So you do not lose any of your content. Project Outline: Format all titles, headings, and data appropriately. Use your text, past tutorials, and cases for reference. Use font styles and fill color to differentiate parts of your worksheets. Use number formatting to emphasize your data. Financial data should use accounting format. You will lose points if you do not format data and text. Each student may have different formatting. That is fine! NOTE: DEPENDING UPON HOW YOU FORMAT YOUR WORKSHEETS YOU MAY HAVE SLIGHTLY DIFFERENT REFERENCED CELLS THAN THE INSTRUCTIONS. A. You will add a documentation sheet. B. You will use Flash fill to add record identification. C. You will use the Vlookup function up to determine the average weight of each cattle, a Vlookup to calculate feed requirements, formulas to calculate the total annual feed requirement and expense for calves, yearlings, and mature cattle. D. You will use a nested Vlookup and If function to test for data integrity (to ensure that all of your data is current, correct, and present). E. You will evaluate the cost of expanding your barn and paved lot. You will use the PMT function to analyze the capital investment of $155,000.00. However the loan amount will be $100,000. F. You will create an income statement from all of the data you have organized and determine if your current venture is success, if you should continue status quo, expand, or discontinue. G. You will chart operating expenses. H. You will determine the subtotal of feed per weight category for Calves, Yearlings, and Mature Cattle and chart them accordingly. I. You will use your feed requirement worksheet data to create a pivot table and use a slicer to filter data. J. Extra Credit will be awarded for using the round function when requested. K. Your worksheets will be formatted appropriately for ease of use, readability, and appeal. Part One: +5 Download the data file from D2L. Add a documentation sheet with the following information: 1. The Workbook Title: \"Buckley's Cattle\" 2. The Date 3. Your name as the author 4. The Purpose (you can summarize the problem statement above for the purpose). 5. Format the text as you feel is appropriate. Non-formatted text and worksheets in this exam may not receive full credit. You can use prior tutorials and assignments from you textbook as examples. Hint: Merge cells when you can. Headings should be a larger font than the data. Color can be used to make information \"Pop\". Organize your data for the ease of readability and appeal, see item J above. Part Two: +35 The worksheet titled \"Feed Requirements\" contains all the data necessary for completing the following (Note: use formulas, functions, and calculations whenever possibleLet the software do the work for you. Avoid using constants, if the data is in the workbook use a reference.). 1. Insert a table beginning with the data in A11 and then insert a column before the very first column in your table. Name the Heading in this first column Tag ID. 2. Use flashfill to create a Tag ID for each cattle that begins with the ID number followed by the first two characters of age category. For example: The Tag ID in A12 would be 383-ca. Delete the ID column. 3. Use the Vlookup function to complete the column under Weight Category. All the data you need to use for your table/array in your Vlookup function is available in the worksheet. The return value will be the average weight for each animal. Remember to use absolute references when necessary. 4. Use the Vlookup function to determine the pounds of feed per animal per day for each type of feed: Corn, Hay, and Soy. Use absolute references when appropriate, reference sheets, and use as few constants as possible. 5. Revise all the Vlookup formulas as nested Vlookups in an IF function to meet the following requirements: If there is no value in the Tag ID column then the returned value should read \"error\". Hint: This would be a returned text value of error. This step is to maintain data integrity, meaning that information that should be included in your worksheet is. 6. Extra Credit: use the Round Function to round the values to two decimal places and modify the number format to display two decimal places. 7. Calculate the total pounds of feed required for each cattle and the entire herd. Calculate the total annual cost of feed for the entire herd. Part Three: +5 Create a loan request worksheet for the barn expansion investment using the following information: The total investment will cost $155,000. However, you will be cashing in corporate bonds for $55,000 to help finance this investment. You are requesting a loan for $100,000, at an annual interest rate of 5.5%, with quarterly payments over a ten year term. Calculate your annual loan payments. Part Four: +25 Open the Income statement worksheet and complete the income statement using the following information, include an assumption range if helpful (use absolute references and relative references when needed) Format the Income statement appropriately: 1. You will be selling 200 mature cattle for $.75 per each pound of animal. The average 2. 3. 4. 5. weight of mature is $1,400 lbs. Sales revenue is equal to the sale of the cattle. The cost of sales is equal to the shipping cost of cattle, which is 3% of the sales revenue. Gross profit is equal to Sales Revenue minus Cost of Sales. You will be buying 200 Calves at $.74 per pound (new calves average 400 pounds). Annual cost of feed can be found in the worksheet \"Feed Requirements\". You will always have a consistent number of cattle which is 300. 6. Annual loan payments can be found in the \"Loan Request Worksheet\". Remember that in an income statement your expenses are displayed as positive numbers because you add all of your expenses and then deduct them from your income. 7. Veterinary Expenses are $4.68 per head of cattle. 8. Labor Expenses are based on 15 hours per week of help at $15.00 per hour 52 weeks a year. 9. Profit before taxes is equal to gross profit minus operating expenses. 10. Taxes are a flat 15%. 11. Taxable income is equal to profit before taxes. 12. Profit after taxes is profit before taxes minus taxes. 13. Profit per head is profit after taxes divided by the heard size. Your heard size will always be 300. Answer the following discussion in your income statement worksheet. Would you continue in the cattle business as is or expand? Meaning, you currently are not purchasing calves to replace cattle sold. When evaluating the scenario of purchasing calves, selling mature cattle, and expanding your barn, would you expand or stay status quo? Why? Part Five: +15 a) Chart the operating expenses using a 3-D pie chart. Move the chart to a new sheet and name it Operating Expenses. Change the title of the chart to Operating Expenses, Format the title, move the legend to the right hand side of the chart, change the data labels to \"best fit\