Question A: Analyzing Regional Sales Information for CKG Auto As a regional sales manager for CKG Auto, you have just finished summarizing sales data for the first half of this year (January through June), aggregated by car model. You have started to enter data in an Excel worksheet, which lists by model the following information: Sales Volume, indicating the number of cars sold to dealers. Manufacturing (Mfg.) Cost per Vehicle © Total Cost of all vehicles sold for the model, which is based on the sales volume and the ‘manufacturing costs per vehicle. Markup Percentage, which is the percentage charged above manufacturing costs to dealers. Total Sales to dealers, which is the Total Cost plus Markup. Markup equals the markup percentage times the manufacturer cost of the vehicle. © % of Total Volume, which is based on the volume for the model compared to the volume of all models sold for the time period. First, you need to complete the January through June computations based on the data contained in the worksheet and the information given. Then, you have been asked to create a similar worksheet to estimate sales for July through December based on volume supplied by the marketing group. These volumes are based on the historical values adjusted for seasonal demand of specific models. and from market research on the popularity of models. After you have completed both the first half actual sales and the second half estimated sales, combine the data to determine expected yearly sales. Management is interested in not only the absolute value of those sales, but also each model's contribution to the total yearly sales in each half of the year and in aggregate. When completing the workbook, be sure 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. ‘To complete the workbook, use the following steps: 1. Open the workbook named [Link] located in the BC Assignment I folder on moodle, and then save the file as CKG [Link]. Rename the Sheet! worksheet tab as Ist Half. 2. In the highlighted cells, enter formulas to perform the necessary calculations for January through June and to summarize. Be sure to write all formulas so that they can be copied as necessary, Note the following: * Display all dollar values in whole numbers and include the dollar sign in the first row and total rows only. © When calculating averages, your formula should automatically exclude models that had no sales. Display all average values (other than the percentage) with commas and no decimal places.3. © When calculating summary data (total, average, etc.), keep in mind that additional car ‘models (rows) may eventually be inserted at the bottom of the list. © The formulas in column G need to determine the percent of total volume sales that the vehicle represents. (That is, if model A sold 100 cars and a total of 1000 cars were sold for all models, then model A would represent 10% of the total volume. Format the cells in column G to display values to the nearest tenth of a percent. Be sure to calculate the number of models available for sale. Your next task is to estimate the July through December sales hased on the marketing data and the sales volume for the second half ofthe year. 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. You can find this list in the [Link] workbook, located in the BC Assignment I folder on moodle. The marketing group assumes that the manufacuuring costs and markups will be the same for the second half of the year as they were. for the first half, With the data and assumptions in mind insert a new worksheet named 2nd Half in the CKG [Link] workbook, identical to the Ist Half worksheet. Copy and paste the sales volumes from the [Link] workbook into your new worksheet. Verify that all the calculations in the new worksheet reflect the new data. NOTE: In order to complete this task successfully, copy the entire 1st Half worksheet to a new worksheet. Then, copy only the values for the sales volume, excluding the heading, from the Market workbook by highlighting the column values and clicking the copy button. Next, place the insertion point in the cell of the new worksheet corresponding to the first vehick volume, and then paste the data. Check the calculated fields (Total Cost, Total Sales to Dealers, etc.) to make sure vehicles that were not sold during the first half of the year have values calculated and vehicles not sold during the second half do not have values. Add another new worksheet named Summary, and include the column headings shown in Table A on this new worksheet. ‘Table A: Column headings for Summary Worksheet Model] Annual | Jandune | July-Dec | Total Sales | Total | Toul | “Toul Volume | Salesto | Sales to | toDealers | Salesto | Salesto_| Sales to Dealers | Dealers Dealers | Dealers | Dealers Jan-June | July-Dec_| Annual a. Insert the model numbers in the identical format as shown on the Ist Half and 2nd Half worksheets b. Insert the annual volume for each model—the combined totals of the Jannary through June and the July through December volumes. Make sure that the values will automatically update if any of the input values are changed. ¢. Insert the Jan-June sales dealers, again ensuring that these values will automatically update if any of the input datd, Insert the July-Dec sales to dealers, again ensuring that these values will automatically update if any of the input data changes. e. Create a combined total of sales to dealers for the entire year. £. Calculate the total volumes and the total sales to dealers for each time period and annually. in a row below the data. 2. Calenlate the percentage af sales to deslers that each model represents, as percentage of the total sales to dealers for all models, for January through June, July through December, and the year. Use only one formula for this calculation, and make sure that the formula can be copied down the column to calculate the percentages for the corresponding models and across the row to calculate the percentages for the corresponding tme frames. Display the percentages with an appropriate format and number of decimal places. 5. Format all three worksheets so that they have a professional appearance. 6. Save and close the CKG [Link] workbook, and upload back to moodle using the BC ‘Assignment I submission link. Question B: Determining Inventory Levels for CKG Auto Another profitable facet of CKG Auto’s business is supplying parts for auto repairs. The most critical component of the parts supply business is having enough of the right parts on hand so that repair shops can receive same-day delivery. The key (o profits is minimizing the number of paris that need to be warehoused while also ensuring that sufficient parts on hand to meet orders. Because each warehouse distribution center serves a different set of customers with different needs, each center must be considered separately. Distribution centers have a five-day lead time for ordering parts. The lead time must be taken into consideration when determining target inventory levels. ‘The costs involved in warehousing the parts include the working capital tied up in the inventory (the cost of each part) as well as the space to store the part. These costs can be substantial. On the other hand, alternative “generic” parts are often available from rival suppliers, and keeping the auto repair centers supplied and customers satisfied is critical. One of the most problematic to supply and store are bearings. This part is both high volume and relatively large, taking up considerable warehousing space. A bearing is also relatively expensive. Analyzing bearing needs is a good place for CKG to start, specifically in one of its largest distribution centers such as Central New Jersey, which serves 10 major customers. You have been asked to analyze the bearing inventory level requirements for this center, including simulating demand based on 30-day historical extremes, calculating a target inventory level based on this simulation, and then comparing simulated values to actual values from the past five days from the targeted warchouse. Complete the following:Open the workbook named [Link] located in the BC Assignment | folder on moodle, and then save it as CKG Parts [Link]. Modify the format of the data on the BearingData worksheet so that zero values are displayed with a 0 instead of the default dash, aligned on the right side of the column. On the BearingData worksheet, take the existing 30-day data for these bearings for each customer to calculate the high and low limits of the bearing demand by customer. On a new worksheet named Simulation, use the high and low limits you just calculated to stimulate daily requirements for each customer to obtain a combined daily requirement. Assume that the daily requirements will vary for each customer randomly between the high and low limits you have calculated from the existing 30-day data for that Customer. Generate the data for approximately 100 instances (days), and then copy the results as values to another new worksheet in the workbook. Keep the original analysis intact on the Simulation ‘worksheet so you can use it again later. Name the new worksheet Simulation Data 1. Be sure that your worksheets have titles and cell formatting. On the Simulation Data | worksheet, in an adjacent column, calculate the total demand by day for all 10 customers. Then, use this daily demand total data to calculate the daily mean, mode, median, and standard deviation for the combined requirements of all 10 customers. Use cell shading to clearly identify your calculation area Extend your analysis (on the same worksheet) to include a ranking of the data (1 to 100) so. that the day with the least total demand hes a rank of 1. Again, use cell shading to clearly identify columns with your calculations. To the right of the data, ereate a listing of the top and bottom five daily combined demands from the 100 simulated instances. Clearly identity this listing using borders and shading. Recommend a target inventory level needed for a five-day period based on the following; to be on the cautious side, assume each day's supply will be equal to the average daily demand for all locations combined, plus three standard deviations: ‘© Because the mean and standard deviations might not already be integers, round the daily demand up to the nearest whole number. ‘© Then, use this calculated daily demand (average plus 3 standard deviations) to calculate Place this recommendation just below the top/bottom analysis, again clearly identifying it. Add the label Bearing Recommended to identify the cell containing the actual value. The warehouse manager has tracked a total of five different parts over the past five days, recording for each shipment the value of the part and the number of days it was in storage. One of these tracked parts is the bearing you have just analyzed. The data has been compiled in a workbook named [Link]. Each line item represents a single shipment of one item. 4Copy the data from the [Link] workbook, located in the BC Assignment I folder on moodle, to your workbook and place it on a worksheet named Actual Demand. 10. On the same worksheet, summarize the deta to determine the number shipped by part, the total values of those shipments by part, and the average number of days that part was stored, using the format shown in Table B. Table B: Setup for parts data summary Total Dollar Value of Items] Average #Days Held in tem Description | # Items Shipped Shipped Inventory Bearings Timing Belts Air Filters Fan Belt Electronic Board In the # Items Shipped Column, write a formula to deten we the number of bearings shipped. Write the formula so that it can be copied down the column to automatically determine the number of timing belts shipped, the number of air filters shipped, and so on. In the Total Dollar Value of Items Shipped column, write a formula to determine the value of all bearings shipped. Again, write the formula so that it can be copied down the column to automatically determine the value of timing belts, air filters, and so on. In the Average #Days Held in Inventory column, write a formula to determine the average number of days bearings shipped were held in inventory; again, write the formula so that it can be copied down the column, Be sure that these formulas will work even if the data is updated in the future. 11. Based on the recommended inventory level you previously calculated for bearings, would you have had enough bearing: a cell below your analys and highlight it in yellow. the warehouse to cover these orders? Place your answer in ‘on the Actual Demand Worksheet. Clearly identify t is answer 12. Double-check all values and formulas for correct implementation. Include sufficient formatting and titles to clearly identify the worksheet elements. 13. Save and close the CKG Parts [Link] workbook, and upload back to moodle using the BC Assignment I submission link.