WEEK # 1 - EXERCISES Exercise 1 You oversee a young and growing business. You have identified the various factors (sources of revenue and expenses) that influence the business as shown in the table below. Use the figures provided and the layout to create a financial projection model for the business for the next six years. The parameters are given on Table 2 as Assumptions. Table 1: Exercise Instructions. 1 WEEK # 1 - EXERCISES (i). (ii). (iii). (iv). (v). Create a new worksheet named W1-EX1-YourName Enter all the titles and texts Create the Table 2 since Row 35 with the title Assumption. Calculate the Sales for the Year 1 in B4 and for the other years using the percentage given in the row C Calculate the different items that make up the Total Operating Costs using the parameters in the Assumption sheet and the Description provided. (vi). Calculate Total Cost of Goods Sold = Materials + Wages + Other Benefits + Other (vii). Calculate the Total Operating Costs = Total Cost of Goods Sold + Total General and Administrative Expenses. (viii). (ix). (x). (xi). (xii). Calculate the Interest on Loans. (Check the Description for Interest of Loans Assumption - cell C48) Calculate the Pre-tax Income = Sales - Total Operating Cost - Interest on Loans. Calculate the Tax. (Check the Description for Tax Assumption - cell C49 and Cell C50) (Note: Use IF Function) Calculate the Profit = Pre-tax Income - Tax. Format the worksheet as shown in the Table 1. 2 WEEK # 1 - EXERCISES (xiii). Create a 3d Pie Chart of the Profit of all the Years. As shown in the picture. In a new Tab named Pie-Chart. Change the color of the Tab to green 3 WEEK # 1 - EXERCISES (xiv). Create a Bar Chart to compare Total Cost of Goods Sold (Row 12), the Total General & Admin. Expenses (Row 21) and the Total Operating Costs (Row 23) for the Years, as shown in the picture. The Chart should be in the Main Tab starting in the row 52. At the end of the Chart, include Your Name, Week 1 Exercise and the Current Date as shown in the Picture 4 WEEK # 1 - EXERCISES Exercise 2: Level 3- Job Analysis. You have learned a tremendous amount about creating the first year's budget for the original option of the new TZEdge shoe. Now, you need to create quarterly budgets and a summary for the textured leather option using $74.07 per shoe as the cost of materials and $5.32 per shoe as the cost of labor. Unlike with the original option, only two pricing alternatives are being considered for the textured leather option. Table 1.7 shows the two pricing alternatives, as well as the expected sales volume in each quarter for these alternatives. Overhead is calculated at 29% of the direct labor costs, and the selling expense is assumed to be $5.10 per shoe. Complete the following: 1. Create a new workbook named W1-Ex2-Texture-Budget-Your-Name.xlsx, and then save the file. 2. Using the finished first quarter budget worksheet for the original option as a model (see Figure 1.24) Create the first quarter budget worksheet for the textured leather option, as follows: a. In cells A1:B2, enter the necessary inputs for the number of shoes per pair and the % of labor for overhead. b. In cell C1, enter the title TZEdge 1st Quarter Budget Textured Leather Option in bold and italic. Merge and center this title across cells C1:D1, and wrap the text so this title appears over 2 lines. Apply a gray shaded background to the title cells. c. In rows 4 through 6, enter the first quarter sales volume and price data, with the column headings Low Priced and High Priced in cells C4 and D4, respectively. Use similar formatting as shown in Figure 1.24. 5 WEEK # 1 - EXERCISES d. In cells A7:A18, enter the labels for each cost component category from the original option (see Figure 1.24), and then in cells B7:D7, enter the headings Cost per Shoe, Low Priced Total, and High Priced Total. e. In cells B11, B12, and B16, enter the costs per shoe for Materials, Labor, and Selling Expense, respectively. f. In cell B13, enter a formula to calculate the Overhead cost. g. In columns C and D, enter the necessary formulas to determine revenue, cost of goods sold, selling expense, and projected earnings for each pricing alternative. (Keep in mind that projected earnings is defined as revenue minus cost of goods sold minus selling expense.) Be sure to use the appropriate relative, absolute, and mixed cell references, so that the formulas can be copied wherever needed and will automatically update if data inputs are modified. Format the values and calculated results on your worksheet in a similar manner to those in Figure 1.24. h. Refer to Figure 1.24 and apply similar borders to the appropriate cells on your worksheet. i. Rename the Sheet1 worksheet tab as 1stQTR. 3. With the 1stQTR worksheet complete, use an appropriate method to create similar worksheets for the other three quarters. Name these worksheets 2ndQTR, 3rdQTR, and 4thQTR. Modify values and labels as necessary on these three worksheets. 4. Create a final comparison sheet named Summary to display each of the budget components summarized by year for each pricing alternative. For the worksheet title in cells C1:D1, enter TZEdge Annual Budget Textured Leather Option. 5. Add your name at the end of the exercise and date 6. Save and close the W1-Ex2-Texture-Budget-Your-Name.xlsx workbook. 6 WEEK # 1 - EXERCISES Exercise Ch1-6: Level 3- CKG Sales. Level 3 - 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 cost 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 CKG-1.xlsx located in the Chapter 1 folder, and then save the file as W1-Ex3-CKG-Sales-Your-Name.xlsx. Rename the Sheet1 worksheet tab as 1st 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. 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.) 7 WEEK # 1 - EXERCISES 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. 3. Your next task is to estimate the July through December sales based on the marketing data and the sales volume for the second half of the 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 Marketing-1.xlsx workbook, located in the Chapter 1 folder. The marketing group assumes that the manufacturing 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 W1-Ex3CKG-Sales-Your-Name.xlsx workbook, identical to the 1st Half worksheet. Copy and paste the sales volumes from the Market.xlsx workbook into your new worksheet. Verify that all the calculations in the new worksheet reflect the new data. TROUBLESHOOTING: 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 vehicle's 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. 4. Add another new worksheet named Summary, and include the column headings shown in Table 1.9 on this new worksheet. a. Insert the model numbers in the identical format as shown on the 1st Half and 2nd Half worksheets. b. Insert the annual volume for each modelthe combined totals of the January through June and the July through December volumes. Make sure that the values will automatically update if any of the input values are changed. c. Insert the Jan-June sales to dealers, again ensuring that these values will automatically update if any of the input data changes. 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. f. Calculate the total volumes and the total sales to dealers for each time period and annually in a row below the data. g. Calculate the percentage of sales to dealers that each model represents, as a 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 8 WEEK # 1 - EXERCISES the column to calculate the percentages for the corresponding models and across the row to calculate the percentages for the corresponding time 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. Add your name at the end of the exercise and date 7. Save and close the W1-Ex2-CKG-Sales-Your-Name.xlsx workbook. 9 WEEK # 1 - EXERCISES Exercise 3: Level 2 - Unpaid Invoice Penalty TheZone accounts payable group can sometimes be delinquent in paying The Zone's vendors in a timely manner. In some cases, this is a deliberate effort to hold off payment as long as possible; in others, it is simply an oversight. The accounts payable group has developed a worksheet listing some of the vendors to which TheZone owes past due balances, organized by past due categories of 30-days past due, 60-days past due, and 90-days past due. Figure 4.35 shows this worksheet. Recently, some vendors have started to apply different penalty and discount schemes to overdue accounts, similar to those being proposed by The Zone's accounts receivable group. As a preemptive measure, you have been asked to help calculate some of these possible penalty scenarios that TheZone might incur based on its current outstanding balances. A list of these past due balances is provided in the workbook named Unpaid.xlsx. This file also contains the data input values that you need to calculate the penalties in the top portion of the worksheet (similar to the spreadsheet in Figure 4.35). Keep in mind that you should use cell references in your formulas wherever possible. Complete the following: 1. Open the workbook named Unpaid-1.xlsx attached with the data of the assignment, and then save the file as W1-Ex3-Unpaid-Invoice-Penalties-YourName.xlsx 2. Some vendors have agreed on an industry-standard penalty of $45 on all past due accounts regardless of the past due amount or number of days past due. These vendors are identified by the value TRUE in the corresponding row of column C. Write a formula in column H, which can be copied down the column, listing the penalty for the corresponding account: $45 for vendors that are participating in this standard penalty and $0 for all other vendors. Only vendors that are owed past due balances are listed on this sheet. 3. Calculate another possible penalty whereby only those accounts with 90-days past due balances are owed a fee. In column I, write a formula that can be copied down the column to calculate the penalty based on the following criteria: For accounts with a 90-days past due balance of $50 or more, apply a fee of 12% of the 90-days past due balance. For all other accounts, no penalty is applied. 4. Another penalty scheme being used by vendors is a graduated method based on the total past due balances (column G). In column J, write a formula that can be copied down the column to calculate the penalty based on the following criteria: For accounts with a past due balance of $10,000 or more, apply a penalty of $600. 10 WEEK # 1 - EXERCISES For accounts with a past due balance of less than $10,000 but more than $3,000, apply a penalty of $250. For accounts with a total past due balance of less than or equal to $3,000, do not apply a penalty. 5. Penalties can sometimes be specific to vendor category. In column K, write a formula that can be copied down the column to calculate the penalty based on the following criteria: For vendors in the Labor category, apply a fee of 6% of the total past due balance. For vendors in the Utilities category, apply a fee of 9% of the total past due balance (column G). For vendors in all other categories, apply a fee of 12% of the total past due balance. 6. Format columns H through K to match column G. 7. Add your name and date at the end of the exercise 8. Save and close the W1-EX3-Unpaid-Invoice-Penalties-YourName.xlsx workbook. 11