Case Study – Analyzing Regional Sales Information for Ken’s Motor Works

Question Description:


As a regional sales manager for Ken’s Motor Works, you have just finished summarizing sales data for the first quarter of this calendar year (January through March) aggregated by car model. You have started to enter data in an Excel worksheet, which lists by model the following:
Columns Descriptions:
Sales Volume (column B) indicating the quantity of cars sold to dealers.
Manufacturing (Mfg.) Cost per Vehicle: (Column C) how much it costs to make the vehicle
Selling Price to Dealer: (Column D) the price at which the vehicle is sold to the dealer
Gross Profit Margin (PM) Percentage, (Column E) You will need to calculate the value that represents the percentage of the sale price of a car that is profit. For instance, if CKG Auto sells a car to a dealer for $10,000 and it costs them $8,000 to make that car then the gross profit margin is 20%. This can be calculated by the following formula:

PM = 1 – Mfg Cost
Selling Price

For example, if it cost $7,500 to manufacture a vehicle and we sell it to the dealer for $10,000 then the profit margin would be 1- 7500/10000 or 25%

Total Cost of all vehicles sold for a model. (Column F) You will need to calculate this by multiplying the Sales Volume and the Manufacturing Costs Per Vehicle together.

Total Sales to dealers (Column G) You will need to calculate this value by multiplying the Selling Price to Dealer column by the Sales Volume.

% of Total Volume (Column H) CKG wants to know, for each model, what % of the overall quantity of cars was that attributed to that model. For instance, if there were 1000 total cars shipped (value in B30) and 100 of those sold were for the MO307 in cell B4 model then that cell in H4 should contain a formula that gives you an answer of 10%.

For this assignment, first you need to complete the January through March computations based on the data contained in the “Sales.xlsx” worksheet and the information given below. Then, you have been asked to create an exact copy of this worksheet to estimate sales for April through June (Q2), July through September (Q3) and October through December (Q4) based on estimated volumes supplied by the marketing group for each of the other quarters. These additional data are located in the separate file called “Market Forecasts for Q2, Q3, Q4.xlsx”. These estimated quarterly volumes are based on the historical values adjusted for seasonal demand of specific car types and from market research data on car popularity. The forecasted sales values for quarters 2, 3 & 4 are located on separate worksheets in this spreadsheet and are called… go figure… Q2, Q3 & Q4.

After you have completed both the 1st quarter actual sales as well as the 2nd, 3rd and 4th quarter sales, you need to combine this data on a 5th sheet to determine expected yearly sales. Management is not only interested in the absolute value of those sales, but each model’s contribution to the total yearly sales in each quarter of the year as well as in the aggregate.

When you complete the workbook, verify that all data is correctly referenced so that your formulas will work as you copy them down the column or across the row, as necessary. If you do this assignment correctly and you change the data on any of the first four quarterly sheets, the information provided on the final summary page should change as well!

Your first task is to complete the Sales Summary for January through March by writing the necessary formulas in the cells that are highlighted in the Excel worksheet.

Complete the following:

1) (0 pt)Open the workbook named A1Sales.xlsx downloadable from links at the top of this page and then save it as Auto Sales LastName FirstName.xlsx (where LastName is YOUR last name and FirstName is YOUR first name).

2) (1 pt)Rename the Sheet1 worksheet tab as “Q1 Actual”.

3) (0 pts)For each of the highlighted cells in rows 4-28, create formulas to perform the necessary calculations as outlined above in the Column Descriptions. For your convenience on this assignment I have applied a yellow shading to all of the cells that will need you to generate formulas in. For all cells on the page that contain dollar values, display them in whole dollars using the Currency format and setting the decimal places to zero. Make sure to only include the dollar sign in the first row of the table and total rows at the bottom of the table.

Also apply appropriate formatting for all values on the sheet regardless of the color shading of it. By “appropriate”, I would hope that all percentage values would be formatted as a percentage, all large numbers (that are or could ever be > 999) throughout the entire worksheet should have commas in them and decimal values should be as instructed. These formatting expectations will apply to ALL assignments for the rest of the semester unless explicitly instructed to do otherwise.

4) (3 pts)In cell E4, create a formula that calculate the Profit Margin % and copy that formula down to E28. Format the values to show the percent value with zero decimal places (i.e.24%)
5) (2.5 pts)In cell F4, create a formula that calculates the Total Cost and copy that formula down to F28. Remember from above to apply the Currency format to all of the lines with zero decimal places but only show the $ sign on the top row!
6) (2 pts)In cell G4, create a formula that calculates the Total Sales to Dealer using the logic above and copy that formula down to G28
7) (3.5 pts) For % of Total Volume in column H, first calculate the total volume of all cars sold in cell B30. Then in cell H4, for that model, find the % of its volume as compared to the Total Volume now located in B30. For full credit, you should write your formula in H4 using proper cell referencing (hint: Mixed referencing puts the $ sign in front of either the column OR the row and absolute referencing puts $ signs in front of both column AND row. One of these two techniques is the better/more efficient choice so that is the one that will get you the most points! By using proper as you copy/paste the formula down for all models, Excel properly writes the formula in all of the cells.

Format the cells in column H to display percentage values to the nearest tenth of a percent (e.g. 5.6%).

8) (0 pts)For the following steps: summarize the columns as needed for the shaded cells in rows 30-35. Be sure to write all formulas so that they can be copied across as necessary without having to re-enter in the formula manually into each cell. Also to make sure that each column is formatted correctly. If it is a $ amount, make the formatting Currency, if it is a percentage make the formatting Percentage, etc. If it is a big number, make sure there are commas.

9) (2.5 pts) In row 30, calculate the totals for each of the 3 shaded columns (B, F & G)
10) (3pts)For row 31, Calculate the averages for all columns except for the Profit Margin (since that would require a weighted average and that is for a later module). Mentally make a note of what the AVERAGE formula calculates for the columns with no number in them for sales. Now put zeroes into the Sales Volume cells that are empty (NULL) and note the difference. Zero counts differently than empty/NULL! Leave in the zeroes for the rest of this Assignment because it is important to note that they did not sell any of those models! Display all average values (other than the percentage) with commas and no decimal places and $ signs if appropriate.

11) (2.5 pts) In cells B32 and B33 write a formula that finds the highest and lowest volumes of all of the car models. Format these with commas and no decimal places.
12) (2 pts)In cell E35 write a formula that provides the number of different models that are available regardless of whether there were sales for that model or not (hint: you will need to use the “Model” column in order to do this because that is the only column that will always contain data for each model). You will need to use the formula that counts cells containing text (i.e. MO307).

13) (4.5pts)Now that you have your Q1 Actual sheet completed, your next task is to create 3 additional functionally identical sheets that instead of containing ACTUAL first quarter sales, they useestimated (forecasted)sales volumes for the Q2, Q3 and Q4 sales quarters. These new sheets that you must create will use sales volumes based on marketing forecast data sales values in the additional file/Asset provided by the name of: “Market Forecasts for Q2, Q3.Q4.xlsx”. The marketing group has provided a list of all car models in identical order to the original data you received, with the expected sales volumes for each car model for April – June (Q2), July-September (Q3) and October-December Q4. Make sure that you update cell B1 on each of these new worksheets to accurately reflect the data contained on each page. Manufacturing costs and Gross Profit Margins are assumed to be the same for all 4 quarters. With the data and assumptions in mind, create a new worksheet named Q2 in your Auto Sales.xlsx workbook by making a copy of the 1st Quarter worksheet and renaming it Q2. Copy and paste the sales volumes from the Market Forecasts for Q2, Q3.Q4.xlsx workbook into your new worksheet into the appropriate cells (replacing the Q1 data with the new Q2 data in this new additional file. If you copy the sheet as recommended above, all of the formatting will be identical (which it should be since consistency is usually a good thing) but you should make sure the header at the top of each worksheet is edited to reflect the correct months in each quarter’s worksheet. If there are models with empty sales volumes, replace these with 0 as you did in the first sheet. Verify that all the calculations in the new worksheet reflect the new data. Repeat for new additional Q3 & Q4 worksheets.

Hint: Just make 3 copies of your “Q1 Actual” sheet and rename them Q2, Q3 & Q4. If you name them something other than these you will not get any points for these sheets!!! Then download and open up the additional file and overwrite the sales volumes one quarter at a time by performing the awesome copy/paste special/values only! If you do this as designed then it should not take you any time at all 🙂

14) (2 pts)

Your starter file came with a sheet called “Sheet 2”. Rename it to “Summary”. Move this Summary tab to be the 5th tab from the left (the far right one). If it is in any other position you will not get any credit for the following steps!
Merge and Center the text in cell A1 (“Yearly Sales Summary – Estimated”) across columns A – L.
Make all of the text in rows 1 & 2 be bold and place borders around all 4 sides of cells A1 – L2.
Adjust the column widths of columns A – L to be able to show all of the text in all of the text in those columns.

Table 1): Screenshot of Column headings for Summary worksheet (here is an example of what it could look like.


Note that it does not have to look like this but should at least have cell borders and numbers be formatted similarly to the previous sheets!)…

15) (3 pts) In the Annual Volume column B, create a formula that adds the “Sales Volume” columns together from the 4 previously created Q1 Actual, Q2, Q3 & Q4 worksheets. Make sure that the values generated by the formulas in these cells will automatically update if any of the input values are changed on any of the 4 individual quarterly sheets at a later time.

16) (3 pts) In the “Q1(Jan-Mar) Sales to Dealers” column C, create a formula that shows the corresponding “Total Sales to Dealers” column from the Q1 Actual worksheet, again ensuring that these values will automatically update if any of the input data changes on that Q1 Actual worksheet.

17) (3 pts) In the “Q2(Apr-Jun) Sales to Dealers” column D, create a formula that shows the “Total Sales to Dealers” column from the Q2 worksheet, again ensuring that these values will automatically update if any of the input data changes. Repeat for columns E & F that should get the corresponding values out of the Q3 & Q4 worksheets.

18) (3 pts) In cell G3, create a formula that sums the sales to dealers for the entire year by adding the cells C3, D3, E3 & F3. Copy this formula down for all of the models.

19) (3 pts) At the bottom of this table in row 29, add a totals row that calculates up the totals for each of the columns B through G.

20) (4 pts) In cell H3, calculate the percentage of how much that model’s Q1 sales will contribute to the yearly total sales to dealers (the value in cell G29), In columns I, J, K & L repeat for quarters 2, 3, 4, as well as the annual values. If you use absolute referencing correctly, you will only have to create one formula in H3 (assuming that row 3 is your first row containing model/sales data) for this calculation and copy it down the column to calculate the percentages for each of the corresponding models, and across the row to calculate the percentages for the corresponding time frames. Display all percentage values to 2 decimal places (e.g. 5.20%)

21) (4 pts) Format all five worksheets so that they have a consistent appearance.
If you haven’t done so yet, go back to all of the sheets and for the “Sales Volume” column apply formatting to the cells to add a comma with no decimal places
For the first row of the table and the grand totals at the bottom, for all of those values that are currency, make sure that you use the currency format with commas.
All tables should have borders for all cells.
All of the cells that either contain a zero or a formula that calculates a value of zero should be formatted consistently in that you shouldn’t have some show as dashes and others with an actual zero. They should be consistently shown as either dashes or zeros but not both.

22) Save and close the Auto Sales LastName FirstName.xlsx workbook. Submit this file here in this assignment “bucket” by clicking on the link at the top of this page. Please note that if you somehow put it into the wrong bucket or if you submit the wrong file, it will receive the score of 0 unless you catch your error prior to your TA grading it.Please watch the “double face-palm” video at the top of the “Weekly Tasks” folder on how to verify that you 1) successfully uploaded your file and 2) uploaded the file that you meant to.