Exam v3
Project Description:
You are working for a candle manufacturer who needs some help doing some analysis. You will be creating charts, tables, pivot tables and charts, breakeven analysis, amortization charts, if statements, nested if statements as well as using lookups, Boolean, sumif and sumifs functions in order to help the manufacturer optimize their business.
Steps to Perform:
Step Instructions Points Possible
1 Open the downloaded last_exc_final_exam_df workbook. Save it as exc_final_exam_LastFirst using your last and first name. 0
2 On the CandleSalesVolume17-18 worksheet, select A1:M147 and insert a table. Rename the table CSalesTable. Apply a filter that will show only the Weekend Sales types. Add another filter that only shows the records between May 15, 2017 and June 29, 2018.
Hint
: Use the Insert ribbon.
5
3 Using the information from the CandleSalesVolume19-20 worksheet, create a PivotTable on a new worksheet. Put the Datesold by months in the rows and the Saletype in the columns. Sum the TOTAL number of candles sold for the value. Rename the worksheet CandleVolumePivot
Hint: After initially adding the
Datesold field to the appropriate section of the pivottable, you will have to remove the years and quarters, leaving just the months for the Datesold. Also, only include the TOTAL, not any of the other columns.
6
4 Using the information from the CandleVolumePivot worksheet, create a PivotChart on the same worksheet that shows the months on the X axis, the total volume on the Y axis, and the series using stacked columns. Change the title in the upper left corner to Candle Volume 2019 to 2020. Resize and move the chart so it fits from A19 to D33.
Hint: The stacked chart is the second column chart.
4
5 On the OverheadRate worksheet, name the A18:B21 range with the name CategoryTable. Then in the D2:D11 range, enter a function that will lookup the overhead rate for each row based on the category of the candle (found in column A).
Hint:
The formula for the Overhead Amount is already there and will automatically calculate after you fill in the function in the D column. You just need to do the rate. Don’t forget to figure out which type of lookup function you need, and whether you need it to be an exact match or not.
8
6 On the OverheadRate worksheet, in the B24 cell, enter a function that will sum the total inventory for all the candles with the category H. In the B25 cell, enter a function that will sum the total inventory for all the candles with the category S with total inventory less than 10,000 units.
Hint:
You will need slightly different functions for this, and it is not the simple SUM function.
8
7 On the Breakeven worksheet, in the D6 cell enter a formula to calculate the Gross Revenue for the average candle sales each month. In D13 enter a function to calculate the Total Fixed Costs, and in D16 enter a formula to calculate the Total Variable Costs.
Hint:
Pay attention to whether you are asked to use formulas or functions. You need to figure out the formulas from context, so read the hints in the headings. Resize the columns if necessary.
5
8 On the Breakeven worksheet, cell D17, enter a formula that will calculate the Net Income. 4
9 On the Breakeven use the data in the break-even analysis to complete the two-variable data table in range H4:Q16 with the Price across the rows and the Volume of Candles per Color down each column.
Hint: To clarify, we are talking about the Data Table that is found under the What If Analysis on the Data ribbon. Don’t forget to enter a link to the Net Income cell address in the corner of the data table so that Excel knows what values you want inside the table.Also remember that you can’t edit just a part of a data input table. If something goes wrong, delete the entire range that you did incorrectly (they may all be zeros) and try again.
8
10 On the Breakeven worksheet, do a goal seek analysis to find out how many candles (Volume of Candles Per Color) must be sold in order to make $3500 in net income?
Hint: Remember that you can't put dollar signs or commas in the value for the goal seek.
6
11 In the CustomerData worksheet, in B2:B21 range, enter a combination of functions to fix the clientname that is in the I column. You need to make sure that the name appears with an initial capital letter and then the rest should be lower case as well as ensuring that there are no extra spaces.
Hint: Put the function to make the case proper inside the function to trim the spaces.
8
12 In the CustomerData worksheet, in the J2:J21 range, enter an IF function to identify whether the client should be saved or released. The end result will say “SAVE” if the client has a high balance of over 50,000 and the type of client is Corp or Serv. Otherwise, the end result will say “Release”. 8
13 In the CustomerData worksheet, in the K2:K21 range, enter an IF function to estimate the projected balance next year. If the client is in CA, the projected balance will be 2% higher than last year. If the client is not in CA, and the client type is Nonp, then the projected balance will be 3% higher than last year. If the client type is not CA and not Nonp, then the projected balance will be 4% higher than last year.
Hint: You do not need to calculate the project balance, just put the percent (2% or 3% or 4%). However, make sure that your result is a number so that if someone were to calculate the actual projected balance, they could do so. Also - don’t forget that if you’ve already tested for being in California, you don’t need to test again, so that condition should only appear once.
10
14 In the EquipmentLoanAmortization worksheet, create range names for the cells in the range B1:B7 by using the names in the A1:A7 range. 4
15 In the EquipmentLoanAmortization worksheet, you will need to do a payment calculation and an amortization chart for the candle mold equipment. The amount borrowed was $150,000 at an interest rate of 6.5% for monthly payments for 7 years. These numbers are already entered for you. In B5 calculate the term (i.e. total number of payments). In B6 calculate the monthly payments. In B7, calculate the total amount paid for the equipment.
Hint:
Be sure to use the range names you created in #14. The term is a simple formula, but payment amount is a function. The Total Amount Paid is also a simple function (you know how much you are paying and how many times you are paying it – so just do simple multiplication.)
9
16 In the EquipmentLoanAmortization worksheet in cell B12, use a reference link to the payment and fill down to B95. In cell E12 enter a reference link to the Loan Amount. In E13 enter a formula to calculate the balance the second month, after the principal portion from the previous month is paid. Fill down to E95.
Hint: Remember that the formulas in E12 and E13 are different. You should only be filling down the formula in E13. 7
17 Save the spreadsheet and make sure the worksheets are in this order CandleSalesVolume17-18, CandleVolumePivot, CandleSalesVolume19-20, OverheadRate, Breakeven, CustomerData, EquipmentLoanAmortization.
Hint: Double check that you typed in the CandleVolumePivot sheet name correctly, and that it is the second sheet.
0
Total Points 100
Quick Reply
