CASE Maxi's Grocery Mart Spreadsheet Case Difficulty Rating: ** 4 SKILLS CHECK You should review the following areas: SPREADSHEET SKILLS Absolute Cell Reference Cell Formatting Chart Formula IF Function Page Break Protecting Cells Relative Cell Reference SUM Function Worksheet Formatting CASE BACKGROUND Since its opening almost 50 years ago, Maxi's Grocery Mart has continued to grow and evolve with the times. The family-owned business has survived many ups and downs and is currently experiencing a modest growth in business. Leroy Feronti, the current owner, wants to expand his family's business by renovating the grocery mart building. While Mr. Feronti has some personal funds available, he will need to procure a loan from the local bank. Before approaching the local bank, he would like to prepare and review several pro forma financial statements. If Mr. Feronti decides to go forward with the renovation, he will use the pro forma financial statements as part of his loan application. Mr. Feronti wants the pro forma income statement prepared first, and he asks you to prepare it for him. Preparation of the pro forma income statement requires you to design a worksheet with input and information sections, properly format the worksheet, construct simple formulas, perform what-if analysis, and generate a chart. CASE SCENARIO Maxi's Grocery Mart is a family-owned business that has been in operation since the 1950s. Although Leroy Feronti is very active with his business, he does employ a store manager, assistant manager, and 17 full-time employees. The store manager and assistant manager are paid a salary, and the employees are paid an hourly wage. Each employee works 40 hours a week, 50 weeks a year. 19 CASE 4: Maxi's Grocery Mart : Figure 1: Maxi's Food Mart Income Statement Outline Maxi's Food Mart Pro Forma Income Statement 2008 2009 2010 2011 Sales Deli Dairy Canned Goods Frozen Foods Meals Produce Dry Goods Video Sales Total Sales Assume 5 percent of total sales each year. Assume 19 percent of total sales cach year. Assume 10 percent of total sales each year. Assume 22 percent of total sales each year. Assume 21 percent of total sales each year. Assume 12.5 percent of total sales each year. Assume 9 percent of total sales each year. Assume 1.5 percent of total sales each year. Assume $3,750,000.00 in total sales for 2008 Cost of Goods Sold Deli Dairy Canned Goods Frozen Foods Meats Produce Dry Goods Video Sales Total Cost of Goods Sold Gross Profit Assume 50 percent of deli sales each year. Assume 50 percent of dairy sales each year. Assume 75 percent of canned good sales each year. Assume 65 percent of frozen food sales each year. Assume 50 percent of meat sales each year. Assume 65 percent of produce sales each year. Assume 66 percent of dry good sales each year. Assume 30 percent of video sales each year. Operating Expenses Sales and Marketing General and Administrative Depreciation Assume 5.5 percent of total sales each year, Assume 8.75 percent of total sales each year. Assume $20,000 per year. Includes the employees' wages, store manager's salary. and assistant manager's salary Mr. Foronti's salary. Wages Common Costs Total Operating Expenses Income Before Taxes Income Taxes Net Income 20 CASE 4: Maxi's Grocery Mart Figure 2: Assumptions and Additional Information Maxi's Food Mart Assumptions and Additional Information Growth and Tax Ratos Salary 2009 Growth: 6.25 percent Mr. Feronti: 12 percent of gross profit 2010 Growth: 7.75 percent Store Manager: $57,000 2011 Growth: 8.25 percent Assistant Manager $12,000 Tax Rate: 35 percent Employee Hourly Wage: $13.00 Having recently assumed ownership of the business from his parents, Mr. Feronti feels that one of the keys to the business's continued success is the renovation of the grocery mart building. Renovating the existing building will cost approximately $450,000. Mr. Feronti must borrow $300,000 from the local bank and will use income generated from the grocery mart to repay the loan. Mr. Feronti asks you to prepare a set of pro forma financial statements for him. He will use these statements to analyze his business. If he decides to pursue the renovation project, he will use the pro forma statements as part of his loan application. Mr. Feronti asks you to use the income statement outline shown in Figure 1 and use the grocery marts 2008 sales as the base period. You will use the 2008 sales to estimate Mr. Feronti's sales, cost of goods sold, expenses, taxes, and net income for the next three years. When preparing the pro forma income statement, several assumptions and additional information are necessary. Figure 2 provides these assumptions and additional information. Design Specifications As Mr. Feronti will use the pro forma income statement as part of his loan application, he requests that it have a consistent, professional, and well-organized appearance. Mr. Feronti specifically requests that you include an appropriate header and apply proper formatting to the cells and worksheet. Using Figures 1 and 2 as guides, you decide that the worksheet requires both input and information sections. Figure 1 provides an outline and guidelines for constructing the information section and Figure 2 provides the necessary data for the input section. By creating separate sections, it is easy for Mr. Feronti to not only view the input data to his income statement, but also, if necessary, change the parameters, thus facilitating his decision-making activities. The information section contains the pro forma income statement, and this section provides Mr. Feronti with information about his projected sales, cost of goods sold, operating expenses, and net income for years 2009 - 2011. The information section uses the grocery mart's 2008 sales as the basis for these projections. You make sure that, where 21 CASE 4: Maxi's Grocery Mart appropriate, the information section formulas reference the cell values contained in the input section As you study Figure 1, you realize that Mr. Feronti wants his store item sales, cost of goods sold, and operating expenses expressed as a percentage of total sales. To facilitate Mr. Feronti's analysis, you place the total sales value in the input section, along with the other assumptions. By doing this, your formulas in the information section can reference the actual total sales figure. As you study Figure 2, you notice that Mr. Feronti's salary is 12 percent of gross profit. Since Mr. Feronti only draws his salary if the grocery mart makes a profit, you must build this logic into the income statement. You do so by using the IF function. To keep the information section's formulas from accidentally being updated, you protect the cells in the information section. Mr. Feronti wants the input and information sections printed on separate pages. For each section's printout, he wants the results printed on a single page. The printouts should utilize a portrait orientation and be centered horizontally and vertically. Information Specifications Mr. Feronti needs information to support his decision making about the upcoming renovation to Maxi's Grocery Mart. Using the newly constructed pro forma income statement, provide Mr. Feronti with the information that he needs. (Before answering each of the following questions, reset your worksheet to its original values.) 1. What impact will sales growths of 9 percent in 2009, 9.5 percent in 2010, and 10 percent in 2011 have on the grocery mart's net income? 2. What impact will sales growths of 4 percent in 2009, 5 percent in 2010, and 5.5 in 2011 have on Mr. Feronti's net income? 3. Mr. Feronti wants a chart that compares the store items based on their 2008 sales. He asks you to select an appropriate chart type and then prepare the chart. 4. If Mr. Feronti decreases his salary to 8 percent and increases the employees' hourly wages to $15, what impact will this have on the grocery mart's net income? 5. Assume Mr. Feronti has 19 employees instead of 17. What impact will two additional employees have on the business's net income? Implementation Concerns The preparation of this case requires you to apply basic spreadsheet construction concepts. Since Mr. Feronti will change the input values during his decision-making activities, you should have a separate input section for the input values. Keep in mind that the formulas in the information section will reference the input cells. You should use absolute and relative cell references, as opposed to constant values. 22 CASE 4: Maxi's Grocery Mart Test Your Design After creating the pro forma income statement worksheet, you should test your design. Perform the following steps. 1. Assume sales in 2008 were $2.5 million, instead of $3.75 million. Now, assume sales in 2008 were $7 million. What impact, if any, do these changes have? Are there any significant changes in the sales, expenses, or net income? (Other than the changes specified in this question, use the original case values.) 2. Make the following changes to the percent of sales for the following items. The deli accounts for 4 percent of sales; dairy items account for 18 percent of sales; canned goods account for 18 percent; frozen food items account for 20 percent; and meats account for 17 percent. 3. Make the following salary changes. Mr. Feronti takes home 16 percent of the gross profit, the store manager makes $60,000, and the assistant manager makes $48,000. How will these changes impact the grocery mart's net income? 4. Reset your sales percentages and salaries back to their original values and then make the following changes. Assume a discount chain is opening a grocery store in a neighboring town. Mr. Feronti thinks this may cause his sales to decrease. He thinks his growth may decrease in 2009 by 12 percent, 2010 by 10 percent and 2011 by 5 percent. Would you still recommend renovating the grocery mart? Why or why not? CASE DELIVERABLES In order to satisfactorily complete this case, you should build the worksheet as described in the case scenario and then prepare both written and oral presentations. Unless otherwise specified, submit the following deliverables to your professor. 1. A written report discussing any assumptions you have made about the case and the key elements of the case. Additionally, what features did you add to make the worksheet more functional? User friendly? (Please note that these assumptions cannot violate any of the requirements specified above and must be approved by your professor.) 2. A printout of each worksheet and chart. 3. A printout of the worksheet's formulas. 4. An electronic, working copy of your worksheet that meets the criteria mentioned in the case scenario and specifications sections. 5. Results for each question posed above. (A memo to your instructor discussing these results should also be provided.) 23 Van Wipp part of any book may be reproduced or transmitted by any means without the publisher's prior permission. Use (other than qualified fair in violation of the law or Terms of Service is prohibited. Violators will be prosecuted to the full extent of the law. CASE 4: Maxi's Grocery Mart 6. As mentioned above, you should prepare an oral presentation. (Your instructor will establish the time allocated for your presentation.) You should use a presentation package and discuss the key features of your worksheet. Also, discuss how the worksheet is beneficial for Mr. Feronti. What additional information should be included in the worksheet to make it more useful? CASE Maxi's Grocery Mart Spreadsheet Case Difficulty Rating: ** 4 SKILLS CHECK You should review the following areas: SPREADSHEET SKILLS Absolute Cell Reference Cell Formatting Chart Formula IF Function Page Break Protecting Cells Relative Cell Reference SUM Function Worksheet Formatting CASE BACKGROUND Since its opening almost 50 years ago, Maxi's Grocery Mart has continued to grow and evolve with the times. The family-owned business has survived many ups and downs and is currently experiencing a modest growth in business. Leroy Feronti, the current owner, wants to expand his family's business by renovating the grocery mart building. While Mr. Feronti has some personal funds available, he will need to procure a loan from the local bank. Before approaching the local bank, he would like to prepare and review several pro forma financial statements. If Mr. Feronti decides to go forward with the renovation, he will use the pro forma financial statements as part of his loan application. Mr. Feronti wants the pro forma income statement prepared first, and he asks you to prepare it for him. Preparation of the pro forma income statement requires you to design a worksheet with input and information sections, properly format the worksheet, construct simple formulas, perform what-if analysis, and generate a chart. CASE SCENARIO Maxi's Grocery Mart is a family-owned business that has been in operation since the 1950s. Although Leroy Feronti is very active with his business, he does employ a store manager, assistant manager, and 17 full-time employees. The store manager and assistant manager are paid a salary, and the employees are paid an hourly wage. Each employee works 40 hours a week, 50 weeks a year. 19 CASE 4: Maxi's Grocery Mart : Figure 1: Maxi's Food Mart Income Statement Outline Maxi's Food Mart Pro Forma Income Statement 2008 2009 2010 2011 Sales Deli Dairy Canned Goods Frozen Foods Meals Produce Dry Goods Video Sales Total Sales Assume 5 percent of total sales each year. Assume 19 percent of total sales cach year. Assume 10 percent of total sales each year. Assume 22 percent of total sales each year. Assume 21 percent of total sales each year. Assume 12.5 percent of total sales each year. Assume 9 percent of total sales each year. Assume 1.5 percent of total sales each year. Assume $3,750,000.00 in total sales for 2008 Cost of Goods Sold Deli Dairy Canned Goods Frozen Foods Meats Produce Dry Goods Video Sales Total Cost of Goods Sold Gross Profit Assume 50 percent of deli sales each year. Assume 50 percent of dairy sales each year. Assume 75 percent of canned good sales each year. Assume 65 percent of frozen food sales each year. Assume 50 percent of meat sales each year. Assume 65 percent of produce sales each year. Assume 66 percent of dry good sales each year. Assume 30 percent of video sales each year. Operating Expenses Sales and Marketing General and Administrative Depreciation Assume 5.5 percent of total sales each year, Assume 8.75 percent of total sales each year. Assume $20,000 per year. Includes the employees' wages, store manager's salary. and assistant manager's salary Mr. Foronti's salary. Wages Common Costs Total Operating Expenses Income Before Taxes Income Taxes Net Income 20 CASE 4: Maxi's Grocery Mart Figure 2: Assumptions and Additional Information Maxi's Food Mart Assumptions and Additional Information Growth and Tax Ratos Salary 2009 Growth: 6.25 percent Mr. Feronti: 12 percent of gross profit 2010 Growth: 7.75 percent Store Manager: $57,000 2011 Growth: 8.25 percent Assistant Manager $12,000 Tax Rate: 35 percent Employee Hourly Wage: $13.00 Having recently assumed ownership of the business from his parents, Mr. Feronti feels that one of the keys to the business's continued success is the renovation of the grocery mart building. Renovating the existing building will cost approximately $450,000. Mr. Feronti must borrow $300,000 from the local bank and will use income generated from the grocery mart to repay the loan. Mr. Feronti asks you to prepare a set of pro forma financial statements for him. He will use these statements to analyze his business. If he decides to pursue the renovation project, he will use the pro forma statements as part of his loan application. Mr. Feronti asks you to use the income statement outline shown in Figure 1 and use the grocery marts 2008 sales as the base period. You will use the 2008 sales to estimate Mr. Feronti's sales, cost of goods sold, expenses, taxes, and net income for the next three years. When preparing the pro forma income statement, several assumptions and additional information are necessary. Figure 2 provides these assumptions and additional information. Design Specifications As Mr. Feronti will use the pro forma income statement as part of his loan application, he requests that it have a consistent, professional, and well-organized appearance. Mr. Feronti specifically requests that you include an appropriate header and apply proper formatting to the cells and worksheet. Using Figures 1 and 2 as guides, you decide that the worksheet requires both input and information sections. Figure 1 provides an outline and guidelines for constructing the information section and Figure 2 provides the necessary data for the input section. By creating separate sections, it is easy for Mr. Feronti to not only view the input data to his income statement, but also, if necessary, change the parameters, thus facilitating his decision-making activities. The information section contains the pro forma income statement, and this section provides Mr. Feronti with information about his projected sales, cost of goods sold, operating expenses, and net income for years 2009 - 2011. The information section uses the grocery mart's 2008 sales as the basis for these projections. You make sure that, where 21 CASE 4: Maxi's Grocery Mart appropriate, the information section formulas reference the cell values contained in the input section As you study Figure 1, you realize that Mr. Feronti wants his store item sales, cost of goods sold, and operating expenses expressed as a percentage of total sales. To facilitate Mr. Feronti's analysis, you place the total sales value in the input section, along with the other assumptions. By doing this, your formulas in the information section can reference the actual total sales figure. As you study Figure 2, you notice that Mr. Feronti's salary is 12 percent of gross profit. Since Mr. Feronti only draws his salary if the grocery mart makes a profit, you must build this logic into the income statement. You do so by using the IF function. To keep the information section's formulas from accidentally being updated, you protect the cells in the information section. Mr. Feronti wants the input and information sections printed on separate pages. For each section's printout, he wants the results printed on a single page. The printouts should utilize a portrait orientation and be centered horizontally and vertically. Information Specifications Mr. Feronti needs information to support his decision making about the upcoming renovation to Maxi's Grocery Mart. Using the newly constructed pro forma income statement, provide Mr. Feronti with the information that he needs. (Before answering each of the following questions, reset your worksheet to its original values.) 1. What impact will sales growths of 9 percent in 2009, 9.5 percent in 2010, and 10 percent in 2011 have on the grocery mart's net income? 2. What impact will sales growths of 4 percent in 2009, 5 percent in 2010, and 5.5 in 2011 have on Mr. Feronti's net income? 3. Mr. Feronti wants a chart that compares the store items based on their 2008 sales. He asks you to select an appropriate chart type and then prepare the chart. 4. If Mr. Feronti decreases his salary to 8 percent and increases the employees' hourly wages to $15, what impact will this have on the grocery mart's net income? 5. Assume Mr. Feronti has 19 employees instead of 17. What impact will two additional employees have on the business's net income? Implementation Concerns The preparation of this case requires you to apply basic spreadsheet construction concepts. Since Mr. Feronti will change the input values during his decision-making activities, you should have a separate input section for the input values. Keep in mind that the formulas in the information section will reference the input cells. You should use absolute and relative cell references, as opposed to constant values. 22 CASE 4: Maxi's Grocery Mart Test Your Design After creating the pro forma income statement worksheet, you should test your design. Perform the following steps. 1. Assume sales in 2008 were $2.5 million, instead of $3.75 million. Now, assume sales in 2008 were $7 million. What impact, if any, do these changes have? Are there any significant changes in the sales, expenses, or net income? (Other than the changes specified in this question, use the original case values.) 2. Make the following changes to the percent of sales for the following items. The deli accounts for 4 percent of sales; dairy items account for 18 percent of sales; canned goods account for 18 percent; frozen food items account for 20 percent; and meats account for 17 percent. 3. Make the following salary changes. Mr. Feronti takes home 16 percent of the gross profit, the store manager makes $60,000, and the assistant manager makes $48,000. How will these changes impact the grocery mart's net income? 4. Reset your sales percentages and salaries back to their original values and then make the following changes. Assume a discount chain is opening a grocery store in a neighboring town. Mr. Feronti thinks this may cause his sales to decrease. He thinks his growth may decrease in 2009 by 12 percent, 2010 by 10 percent and 2011 by 5 percent. Would you still recommend renovating the grocery mart? Why or why not? CASE DELIVERABLES In order to satisfactorily complete this case, you should build the worksheet as described in the case scenario and then prepare both written and oral presentations. Unless otherwise specified, submit the following deliverables to your professor. 1. A written report discussing any assumptions you have made about the case and the key elements of the case. Additionally, what features did you add to make the worksheet more functional? User friendly? (Please note that these assumptions cannot violate any of the requirements specified above and must be approved by your professor.) 2. A printout of each worksheet and chart. 3. A printout of the worksheet's formulas. 4. An electronic, working copy of your worksheet that meets the criteria mentioned in the case scenario and specifications sections. 5. Results for each question posed above. (A memo to your instructor discussing these results should also be provided.) 23 Van Wipp part of any book may be reproduced or transmitted by any means without the publisher's prior permission. Use (other than qualified fair in violation of the law or Terms of Service is prohibited. Violators will be prosecuted to the full extent of the law. CASE 4: Maxi's Grocery Mart 6. As mentioned above, you should prepare an oral presentation. (Your instructor will establish the time allocated for your presentation.) You should use a presentation package and discuss the key features of your worksheet. Also, discuss how the worksheet is beneficial for Mr. Feronti. What additional information should be included in the worksheet to make it more useful