This is everything I need done. I have some done and I have all numbers that I will you will need attached.
Part One: +5
Download the data file from D2L. Add a documentation sheet with the following information:
The Workbook Title: “Buckley’s Cattle”
Your name as the author
The Purpose (you can summarize the problem statement above for the purpose).
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 possible—Let the software do the work for you. Avoid using constants, if the data is in the workbook use a reference.). Also note that your ranges and references may be slightly different than the instructions depending on how you choose to format your worksheet.
Insert a column before the very first column in your worksheet. Name the Heading in in cell A 11 Tag ID.
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.
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.
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.
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 not an error message.
Extra Credit: use the Round Function to round the values in the Corn, Hay, and Soy columns to two decimal places and modify the number format to display two decimal places.
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. Note these requirements will need to be calculated for 365 days.
Part Three: +15
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:
You will be selling 200 mature cattle for $.75 per each pound of animal. The average 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.
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.
Veterinary Expenses are $4.68 per head of cattle.
Labor Expenses are based on 15 hours per week of help at $15.00 per hour 52 weeks a year.
Profit before taxes is equal to gross profit minus operating expenses.
Taxes are a flat 15%.
Taxable income is equal to profit before taxes.
Profit after taxes is profit before taxes minus taxes.
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: +20
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”, the data labels should be in currency number format with a dollar sign. Add a percentage value for each data label so that both the dollar amount and percentage are displayed. Both labels should have two decimal places. Name the new sheet “Operating Expenses Chart”. Use a chart style that you feel best meets the need of this worksheet. Please do not use a chart with a white background.
|Due By (Pacific Time)||9/25/2015 11pm|
out of 1971 reviews
out of 766 reviews
out of 1164 reviews
out of 721 reviews
out of 1600 reviews
out of 770 reviews
out of 766 reviews
out of 680 reviews