Questions/Problems For the problems below, write Excel formula in cells C7 to F34 of worksheet PFIS' (104 formula in total to fill in) using as much absolute addressing and copy & paste as possible WITHOUT editing/correcting the formula. This means: Don't edit/correct the formula after the formula are copied & pasted. The reasons are:- There are many different ways to write the Excel formula in cells C7 to F34 and obtain the same calculated values, but certain formula with appropriate absolute addressing and copy & paste will enable less typing/editing, and thus saves typing time! The purpose of using Excel is to be more efficient saves editing time!- - In PFIS' worksheet, how many formula you typed/edited (at most)? How many formula vou copied and pasted from other formula? Background of company Mr. Maxi, the current owner of Maxi's Grocery Mart, wants to renovate the grocery mart's existing building. Financing the renovation requires a loan from a local bank. Before proceeding with the loan application, Mr. Maxi needs several pro forma financial statements prepared. The pro forma income statements are to be prepared in Microsoft Excel. Please refer to worksheet PFIS. This case encourages students to use their general business/accounting background, and spreadsheet knowledge to prepare a pro forma income statement for Mr. Maxi. + Details of the worksheet "PFIS' It is now end of year 2003 and the total sales for year 2003 is $3500000.00 (in cells A40 and B40), and the pro forma income statements need to forecast the net income for years 2004 to 2006 based on year 2003's figures. The following assumptions are made (see cells A42 to F55):4 . Tax rate for each year is 35% (cell F46). Sales for Deli' are 5% of total sales for each year (cell B43). Sales for Dairy' are 20% of total sales for each year (cell B44). Sales for Canned Goods' are 15.5% of total sales for each year (cell B45). . Sales for 'Frozen Foods' are 18% of total sales for each year (cell B46). Sales for Meats' are 21% of total sales for each year (cell B47). Sales for Produce are 12.5% of total sales for each year (cell B48)." Sales for Dry Goods' are 8% of total sales for each year (cell B49). . 0 Cost of Goods sold (CGS) for Deli' is 50% of deli sales each year (cell C43). - Cost of Goods sold (CGS) for Dairy' is 50% of dairy sales each year (cell C44).- Cost of Goods sold (CGS) for Canned Goods' is 75% of canned goods sales each year (cell C45). Cost of Goods sold (CGS) for Frozen foods' is 65% of frozen food sales each year (cell 046). Cost of Goods sold (CGS) for Meats is 50% of meat sales each year (cell C47). Cost of Goods sold (CGS) for Produce is 65% of produce sales each year (cell C48). Cost of Goods sold (CGS) for Dry goods is 66% of dry good sales each year (cell C49). Operating expense for sales & marketing is 5.5% of total sales each year (cell B52). Operating expense for general & administrative is 8.75% of total sales each year (cell B53). Depreciation is $20,000.00 per year (cell B54). Salaries information Mr. Leory employs a store manager, an assistant manger and 15 full-time employees (cell F55). The manager and assistant manager are paid yearly salaries (cells F52, F53), and the 15 employees are paid an hourly wage, $10 per hour (cell B55). Each employee works 40 hours per week, 50 weeks a year. Row 29 contains the formula for the yearly total salaries for all the managers and employees. Mr. Leory, Maxi also works in the mart, and he takes 15% of gross profit (cell F51) as salary if the gross profit is positive. Note: Mr. Leory Maxi's salary is classified as common costs in row 30. Maxi's Grocery Mart needs to pay income taxes (Row 33) only if income (before taxes) is positive. Growth information (Note: Growth rate applies to previous year's figures, and applies to everything except wages, common costs, tax and depreciation.)" Growth rate for 2004 is 7.25% (cell F43). Growth rate for 2005 is 7.75% (cell F44). Growth rate for 2006 is 8.25% (cell F45). A B E F 1 2 3 D Maxi's Grocery Mart Pro Forma Income Statement 2003 2004 2005 2006 ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? 4 5 6 Sales 7 Deli 8 Dairy 9 Canned Goods 10 Frozen Foods 11 Meats 12 Produce 13 Dry Goods 14 Total Sales 15 Cost of Goods Sold 16 Deli 17 Dairy 18 Canned Goods 19 Frozen Foods 20 Meats 21 Produce 22 Dry Goods 23 Total Cost of Goods Sold 24 Gross Profit 25 Operating Expenses 26 Sales and Marketing 27 General and Administrative 28 Depreciation 29 Wages 30 Common Costs 31 Total Operating Expenses 32 Income Before Taxes 33 Income Taxes 34 Net Income 35 ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? Assumptions 22 Dry Goods 23 Total Cost of Goods Sold 24 Gross Profit 25 Operating Expenses 26 Sales and Marketing 127 General and Administrative 28 Depreciation 29 Wages 30 Common Costs 31 Total Operating Expenses 32 Income Before Taxes 33 Income Taxes 34 Net Income 35 36 37 38 39 40 Total Sales for 2003 41 42 Store Items 43 Deli 44 Dairy 45 Canned Goods 46 Frozen Foods 47 Meats 48 Produce 49 Dry Goods 50 51 Operating Expenses 52 Sales and Marketing 53 General and Administrative 54 Depreciation Expense 55 Wage $3,500,000.00 Rates % of Sales 5.00% 20.00% 15.50% 18.00% 21.00% 12.50% 8.00% 100.00% CGS 50.00% 50.00% 75.00% 65.00% 50.00% 65.00% 66.00% 2004 Growth 2005 Growth 2006 Growth Tax 7.25% 7.75% 8.25% 35.00% Salaries Leory 15.00% Manager $50,000.00 Asst. Manager $36,000.00 5.50% 8.75% $20,000.00 $10.00 No. of Employees 15 3 Questions/Problems For the problems below, write Excel formula in cells C7 to F34 of worksheet PFIS' (104 formula in total to fill in) using as much absolute addressing and copy & paste as possible WITHOUT editing/correcting the formula. This means: Don't edit/correct the formula after the formula are copied & pasted. The reasons are:- There are many different ways to write the Excel formula in cells C7 to F34 and obtain the same calculated values, but certain formula with appropriate absolute addressing and copy & paste will enable less typing/editing, and thus saves typing time! The purpose of using Excel is to be more efficient saves editing time!- - In PFIS' worksheet, how many formula you typed/edited (at most)? How many formula vou copied and pasted from other formula? Background of company Mr. Maxi, the current owner of Maxi's Grocery Mart, wants to renovate the grocery mart's existing building. Financing the renovation requires a loan from a local bank. Before proceeding with the loan application, Mr. Maxi needs several pro forma financial statements prepared. The pro forma income statements are to be prepared in Microsoft Excel. Please refer to worksheet PFIS. This case encourages students to use their general business/accounting background, and spreadsheet knowledge to prepare a pro forma income statement for Mr. Maxi. + Details of the worksheet "PFIS' It is now end of year 2003 and the total sales for year 2003 is $3500000.00 (in cells A40 and B40), and the pro forma income statements need to forecast the net income for years 2004 to 2006 based on year 2003's figures. The following assumptions are made (see cells A42 to F55):4 . Tax rate for each year is 35% (cell F46). Sales for Deli' are 5% of total sales for each year (cell B43). Sales for Dairy' are 20% of total sales for each year (cell B44). Sales for Canned Goods' are 15.5% of total sales for each year (cell B45). . Sales for 'Frozen Foods' are 18% of total sales for each year (cell B46). Sales for Meats' are 21% of total sales for each year (cell B47). Sales for Produce are 12.5% of total sales for each year (cell B48)." Sales for Dry Goods' are 8% of total sales for each year (cell B49). . 0 Cost of Goods sold (CGS) for Deli' is 50% of deli sales each year (cell C43). - Cost of Goods sold (CGS) for Dairy' is 50% of dairy sales each year (cell C44).- Cost of Goods sold (CGS) for Canned Goods' is 75% of canned goods sales each year (cell C45). Cost of Goods sold (CGS) for Frozen foods' is 65% of frozen food sales each year (cell 046). Cost of Goods sold (CGS) for Meats is 50% of meat sales each year (cell C47). Cost of Goods sold (CGS) for Produce is 65% of produce sales each year (cell C48). Cost of Goods sold (CGS) for Dry goods is 66% of dry good sales each year (cell C49). Operating expense for sales & marketing is 5.5% of total sales each year (cell B52). Operating expense for general & administrative is 8.75% of total sales each year (cell B53). Depreciation is $20,000.00 per year (cell B54). Salaries information Mr. Leory employs a store manager, an assistant manger and 15 full-time employees (cell F55). The manager and assistant manager are paid yearly salaries (cells F52, F53), and the 15 employees are paid an hourly wage, $10 per hour (cell B55). Each employee works 40 hours per week, 50 weeks a year. Row 29 contains the formula for the yearly total salaries for all the managers and employees. Mr. Leory, Maxi also works in the mart, and he takes 15% of gross profit (cell F51) as salary if the gross profit is positive. Note: Mr. Leory Maxi's salary is classified as common costs in row 30. Maxi's Grocery Mart needs to pay income taxes (Row 33) only if income (before taxes) is positive. Growth information (Note: Growth rate applies to previous year's figures, and applies to everything except wages, common costs, tax and depreciation.)" Growth rate for 2004 is 7.25% (cell F43). Growth rate for 2005 is 7.75% (cell F44). Growth rate for 2006 is 8.25% (cell F45). A B E F 1 2 3 D Maxi's Grocery Mart Pro Forma Income Statement 2003 2004 2005 2006 ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? 4 5 6 Sales 7 Deli 8 Dairy 9 Canned Goods 10 Frozen Foods 11 Meats 12 Produce 13 Dry Goods 14 Total Sales 15 Cost of Goods Sold 16 Deli 17 Dairy 18 Canned Goods 19 Frozen Foods 20 Meats 21 Produce 22 Dry Goods 23 Total Cost of Goods Sold 24 Gross Profit 25 Operating Expenses 26 Sales and Marketing 27 General and Administrative 28 Depreciation 29 Wages 30 Common Costs 31 Total Operating Expenses 32 Income Before Taxes 33 Income Taxes 34 Net Income 35 ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? Assumptions 22 Dry Goods 23 Total Cost of Goods Sold 24 Gross Profit 25 Operating Expenses 26 Sales and Marketing 127 General and Administrative 28 Depreciation 29 Wages 30 Common Costs 31 Total Operating Expenses 32 Income Before Taxes 33 Income Taxes 34 Net Income 35 36 37 38 39 40 Total Sales for 2003 41 42 Store Items 43 Deli 44 Dairy 45 Canned Goods 46 Frozen Foods 47 Meats 48 Produce 49 Dry Goods 50 51 Operating Expenses 52 Sales and Marketing 53 General and Administrative 54 Depreciation Expense 55 Wage $3,500,000.00 Rates % of Sales 5.00% 20.00% 15.50% 18.00% 21.00% 12.50% 8.00% 100.00% CGS 50.00% 50.00% 75.00% 65.00% 50.00% 65.00% 66.00% 2004 Growth 2005 Growth 2006 Growth Tax 7.25% 7.75% 8.25% 35.00% Salaries Leory 15.00% Manager $50,000.00 Asst. Manager $36,000.00 5.50% 8.75% $20,000.00 $10.00 No. of Employees 15 3