Excel
YO16_XL_GRADER_CASE5 – Coffee 1.1
Project Description:
Chloe Jants is fulfilling her long-term dream of opening a coffee shop on a busy corner of her hometown. Her town is like many others in the southern half of the state, and she has done a substantial amount of research regarding sales and customer patterns. Chloe has developed a startup plan with costs and expenses and would like to explore different loan options to see which would work best for her. She would also like to run a statistical analysis on the sales and customer patterns for which she has collected data.
Don't use plagiarized sources. Get Your Custom Essay on
Excel
Just from $13/Page
Steps to Perform:
| Step | Instructions | Points Possible |
| 1 | Start Excel. Open the file named ex05Coffee_START. Save the file with the name ex05Coffee_LastFirst, replacing LastFirstwith your name. Add the Data Analysis ToolPak to the Ribbon. | 0 |
| 2 | On the LoanAnalysis worksheet, in cell B9, use the PMT function to calculate the end of the month payment amount required to pay off the amount borrowed for loan option 1. Use cell references where appropriate and make sure the result is positive. | 3 |
| 3 | In cell B10, use the PMT function to calculate the beginning of the month payment amount required to pay off the amount borrowed for loan option 1. Use cell references where appropriate and make sure the result is positive. | 3 |
| 4 | In cell B18, use the RATE function to calculate the annual interest rate for loan option 2 if payments are made at the end of each month. Use cell references for all arguments. Increase the number of decimal places to 2. | 3 |
| 5 | In cell B19, use the RATE function to calculate the annual interest rate for loan option 2 if payments are made at the beginning of each month. Use cell references for all arguments. Increase the number of decimal places to 2. | 3 |
| 6 | In cell B26, use the NPER function to calculate the total number of payment periods required to pay off the amount borrowed in loan option 3 if payments are made at the end of each period. Format as a Number with 2 decimal places. | 3 |
| 7 | In cell B27, calculate the number of years it would take to pay off the amount borrowed in loan option 3, using the total payment periods calculated in cell B26. Format as a number with 2 decimal places. | 1 |
| 8 | In cell B39, use the CUMPRINC function to calculate the end of period cumulative principal payment amounts for each period for loan option 4. Use cell references for all arguments and make sure the result is positive. Autofill the function down through B78. | 5 |
| 9 | In cell C39, use the CUMIPMT function to calculate the end of period cumulative interest payment amounts for each period for loan option 4. Use cell references for all arguments and make sure the result is positive. Autofill the function down through C78. | 5 |
| 10 | In cell B79, use the SUM function to calculate the total amount of principal payments necessary to pay off loan option 4. | 2 |
| 11 | In cell C79, use the SUM function to calculate the total amount of interest payments necessary to pay off loan option 4. | 2 |
| 12 | On the NPV worksheet, in cell B21, use the NPV function to calculate the net present value based on the cash flows provided. | 5 |
| 13 | In cell B23, use the IRR function to calculate the internal rate of return based on the cash flows provided. | 4 |
| 14 | On the Depreciation worksheet, in cell B9, use the SLN function to calculate the straight line depreciation amount for year 1. AutoFill the function down to B13. | 4 |
| 15 | In cell C9, calculate the accumulated depreciation at the end of year 1. In cell C10, calculate the accumulated depreciation at the end of year 2. AutoFill the calculation down to C13. | 2 |
| 16 | In cell D9, calculate the book value at the end of year 1. AutoFill the calculation down to D13. | 2 |
| 17 | In cell B18, use the DB function to calculate the declining-balance depreciation amount for year 1. AutoFill the function down to B22. | 5 |
| 18 | In cell C18, calculate the accumulated depreciation at the end of year 1. In cell C19, calculate the accumulated depreciation at the end of year 2. AutoFill the calculation down to C22. | 2 |
| 19 | In cell D18, calculate the book value at the end of year 1. AutoFill the calculation down to D22. | 1 |
| 20 | On the DailyStats worksheet, in cell C4, use the Data Analysis tools to calculate the moving average for the number of customers that visit the coffee shop every seven days. Include a chart output and position the chart so that the top-left corner is in cell G4 and the bottom-right corner is in cell L18. | 7 |
| 21 | In cell F6, use the AVERAGE function to calculate the mean of the number of customers. In cell F7, use the MEDIAN function to calculate the median number of customers. In cell F8, use the MODE.SNGL function to determine the number of customers that appears most often in the data set. | 3 |
| 22 | In cell F10, use the MIN function to determine the fewest number of customers to visit the coffee shop in any given day. In cell F11, use the MAX function to determine the most number of customers to visit the coffee shop in any given day. | 2 |
| 23 | In cell F13, calculate the range by subtracting the smallest number of customers to visit the coffee shop on any given day from the largest number. In cell F14, use the VAR.S function to calculate the variance of the sample data set. In cell F15, use the STDEV.S function to calculate the standard deviation of the sample data set. | 3 |
| 24 | Use the Data Analysis tools to run descriptive statistics on the number of customers. Place the summary statistics output in cell A1 of the DescriptiveStatistics worksheet. Be sure and use the Number of Customers column label in the output and include labels in the first row. | 5 |
| 25 | On the MonthlyStats worksheet, in cell E4, use the AVERAGE function to calculate the median monthly customers to visit the coffee shop. In cell E5, use the STDEV.S function to calculate the standard deviation of the number of customers to visit the coffee shop each month. | 2 |
| 26 | In cell E7, use the NORMDIST function to determine the probability of meeting or exceeding 7,100 customers in a month using the cumulative probability distribution. | 4 |
| 27 | In the Standard Deviation column, starting in cell D11, enter values ranging from 4 to -4 in increments of 0.1. These will serve as standard deviations from the mean that will be used to chart the normal distribution of monthly customers.
In cell E11, calculate the number of monthly customers that would be 4 standard deviations from the mean. AutoFill the calculation down to E91. In cell F11, use the NORM.DIST function to calculate the probability of there being exactly the 4 standard deviations from the mean of customers visiting the coffee shop in a month. Format as Percentage with 2 decimal places. |
7 |
| 28 | Create a Scatter with Smooth Lines chart using the Quantity and Probability data. Edit the Chart Title to read, Monthly Customer Distribution. Add a vertical axis title that reads, Probability, and a horizontal axis label that reads, Total Monthly Customers. Move the chart so that the top-left corner is in cell H10 and the bottom-right corner is in cell M24. | 4 |
| 29 | On the Correlations worksheet, in cell E4, use the COVARIANCE.S function to calculate the covariance between the age and visits per month variables. | 2 |
| 30 | In cell E5, use the CORREL function to calculate the correlation coefficient between the age and visits per month variables. | 2 |
| 31 | Create a Scatter chart using the Age and Visits per month columns, A2:B30. Edit the chart title to read Age-Visits Correlation. Add a vertical axis title that reads Visits per month. Add a horizontal axis title that reads Age. Add a linear trend line. Move the chart so that the top-left corner is in cell D7 and the bottom-right corner is in cell K21. | 4 |
| 32 | Save and close the workbook and then exit Excel. Submit the workbook as directed. | 0 |
| Total Points | 100 |
Calculator
Our features
We've got everything to become your favourite writing service
Money back guarantee
Your money is safe. Even if we fail to satisfy your expectations, you can always request a refund and get your money back.
Confidentiality
We don’t share your private information with anyone. What happens on our website stays on our website.
Our service is legit
We provide you with a sample paper on the topic you need, and this kind of academic assistance is perfectly legitimate.
Get a plagiarism-free paper
We check every paper with our plagiarism-detection software, so you get a unique paper written for your particular purposes.
We can help with urgent tasks
Need a paper tomorrow? We can write it even while you’re sleeping. Place an order now and get your paper in 8 hours.
Pay a fair price
Our prices depend on urgency. If you want a cheap essay, place your order in advance. Our prices start from $11 per page.