Project The Michigan Avenue Boutique Fall 2023 A major function within most businesses is the accounting function. The accounting department provides quantitative information about the finances of the business including recording, measuring, and describing financial information. The department produces financial statements written records of the financial status (or health) of the business. The four primary financial statements produced by this department include: The Balance Sheet, The Income Statement, Statement of Owner's Equity, and the Statement of Cash Flows THE INCOME STATEMENT The income statement shows operating results (revenues minus expenses) for a given time period. It can also be used to budget or project future results. The income statement can also be referred to as the earnings report, operating statement, profit and loss (P\&L) statement. Revenue - the monies earned from the delivery or manufacture of a product or from performing a service. Can also include sales from a product. Expenses - the costs incurred in operating and maintaining a business. Net Income - the amount of money remaining after paying taxes. The Michigan Avenue Boutique Project The objective of this exercise/project is to create a projected monthly budget that takes on the form of an income statement for the Michigan Avenue Boutique. After the budget/income statement is created you will then be asked to make some adjustments in this statement to meet a specific projected financial goal. In creating this budget, some information has been given to you, some you will need to research, and some you need to create (using calculations). PERTINENT INFORMATION/ASSUMPTIONS ABOUT THE MICHIGAN AVENUE BOUTIQUE Revenue/Sales When looking at industry benchmarks (or standards), this store should have annual sales of $475,500 per employee. This means if one person is on the payroll, the store would have to generate annual sales of $475,500 to cover their expenses, and still make a normal profit for the year. All of the company's revenue is generated from sales. Space Requirement The average space required for each employee in this retail store is 200 square feet. This takes into consideration working office space, utility areas, restrooms, break room, aisles, exits and all that is needed to run a business. Payroll This company has seven (7) employees. President, who is also the Director of Marketing (Director of Marketing) Vice President who is also the Controller (Controller) A Senior Sales Representative (Sales Representative II) A Junior Sales Representative (Sales Representative l) Purchasing Agent - Production Scheduler Manager Il An Administrative Assistant A Receptionist You must do some salary research to determine what dollar amount you what they are. be on the same worksheet. All formulas used in the income statement should only contain cell references and not numbers. i.e., =B7Z45, not =915. Chart Creation Create a pie chart showing the distribution of all expenses for May. You should focus on the 4 largest expenses. Consolidate the rest into a group called Other Expenses. To do this, you must bring your data to a different place on the worksheet and arrange the data in a manner convenient for creating the chart. Place the resulting chart on its own chart sheet - do not embed it on the budget worksheet. Be sure to format the chart properly. Goal Seek Exercise What should our monthly sales be if we want our Net Income to Sales \% to be 8.45%. To answer this question, you will use the Goal Seek Command. ****Make a copy of the income worksheet. Rename the sheet Goal Seek. On the Goal Seek sheet, remove the formula you used to calculate Revenue and replace with the value (number) that represents revenue. (HINT: Try the Paste Special command) The Goal Seek tool can be found on the DATA ribbon, What If Analysis command. Using the Goal Seek tool, determine what Total Revenue should be in order to have a Net Income to Sales % be 8.45%. Set cell is the cell that contains the net income to sales \% calculation To Value is 8.45% By Changing Cell is the revenue cell (which should not contain a formula) If done correctly, your percentage will be less than 8.45% Before submitting, review your work. Make sure all numbers are properly formatted. Sample Income Statement Format