Appendix A – Hands On



Profit Accounting AcademyNadine EpsteinSandi JeromeSandi Jerome Computer Consulting1391NW St Lucie Blvd West PMB 362Port St Lucie, FL 34986360-406-5062 x 706Questions? Email - sandi@To order – This handout may not be reproduced in any form without the written permission of the publisher. This handout was designed to provide accurate and authoritative information about its subject. It is provided with the understanding that it is not intended to render legal, accounting or other professional services. If legal advice or other expertise is needed, the services of a competent professional should be sought. Users are cautioned that this might not be a complete list of all regulations, deadlines, or other required tasks for a business and should only be used as a guide. Users assume all risks for using tasks, tips, and suggestions and should seek professional advice before making any changes to procedures.Welcome to the Profit Accounting Academy!Introduction and hotel/area informationNotes:_________________________________________________________________________________________________________________________________Who are You?Name , Dealership(s) – city/state, Position and how many years?Excel guru or newbie? Autofilter, Pivot tables, Vlookup?DMS system? ADP, R+R (Power/ERA), Dealertrack/Arkona, ASI,Other3267075158115How can we bring down the breakeven number? Parts and service gross profit $____________ Non-variable expenses $_______________Absorption _____ Where does your store need help? 5 key itemsParts and Service – what is your absorption percentage? – What should it be?Technician productivity, sell every hourParts fill rate, pricing matrixReduce non-variable expensesVariable percentage – sales commission analysisGross profit per unitCarry what sells, sell more!The “Why” of Profit Accounting“Mini” deals? Minimum Wage?Unapplied time? Low technician productivity?Obsolete parts? Parts Shortages?High sales commission expense Why do cars stay in stock over 60 days?Why am I out of cash today – everyday!Creating Why Profit Reports for ManagersMondayLast week’s overtime report (this month’s focus) and whyTuesday – Service and Parts10 oldest parts – why did this happen? Can we get rid of?10 oldest repair orders (with hr and pts) – why still open?Lowest productivity technicians – why?10 oldest warranty claims – why?Weds – Inventory day10 oldest new – why? What do we have in stock vs. on order 10 oldest used - why, What is our mix vs what sells?Are we advertising vs. what is in stock?Thursday – Car dealsLowest gross profit – front and back – why? What is common?Deals not in the office – why, reasons (vlookup)Oldest contracts/VR not paid – why – reason (vlookup)Deals per sales person – who is on top? Who is lowest? Why?Profit Accounting RequirementsGet your office organized - time Create your metricsUnderstand Gross Profit – let’s start at the bottom…parts What should Parts Gross be?Homework for Parts ProfitTop 10 Parts customers, GP% and price code matrix setupMost common price code matrix – customer fileSetup of the price code matrix on DMSWhat should your matrix be, discussion with dealer and parts manager? Your metricsJournal review – gross profitParts master, List vs CostList Price __________ Cost _____________ Retail Gross Profit% ______Trade Price __________ Cost _____________ Wholesale Gross Profit% ______Metric - Customer%_____ Metric - Wholesale%How you lose Parts GrossTickets sold at cost or lessNot selling at customer’s matrix priceInternals not at matrixAdjustments to cost of sales accountsJournal vouchersParts purchases – buying at a premium.....More Profit Homework in the Parts DepartmentInternal control – PartsOverride Report – DMS 2542, ROV Additions/Deletes Parts Management ReportInventory AnalysisWhat is your Parts age? Sales vs Receipt dateWhat if most of your inventory is “current” based on Receipts instead of Sales?Accounting balance vs. parts pad – do you compare monthly?Parts Reconcile spreadsheet What are some of the reasons why Parts and Accounting Totals are not the same?____________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________Fill Rate Report – your metric, 80%, 70%? ________If a dealership has a higher fill rate from shelf – how do they make more profit?__________________________________________________________________________________________________________________________________________Filled same day?__________________________________________________________________________________________________________________________________________Service Department Financial vs. Operational ProfitName some reasons why service gross on the Service Advisor's report is different than the DOC, Financial Statement?__________________________________________________________________________________________________________________________________________What is your Gross Profit %? Customer Labor ___%Do you see any problems above?__________________________________________________________________________________________________________________________________________What is your Metric? HomeworkYour Posted Labor Rate* $_____ Tech Cost $_____ Metric for Gross Profit % ____Effective Labor Rate $____ Tech Cost_____ Metric for Gross Profit % ____* Customer Labor Sales setup amountWays to Prevent – Gross lossUnapplied/shop time to own account – not on an RORemove all overtime – and put any in an expense/subUse actual cost in tech setupWork in Process must be a “closed loop” – no lick ‘n stick!Measure – Post - Analyze Technician Productivity/Efficiency/Utilization/OpportunityWhat is best to measure –Productivity/Efficiency/Utilization/Opportunity?__________________________________________________________________________________________________________________________________________Technician Efficiency - can he do jobs faster and more efficient than flag time?Total Flat Rate Hours - Flag__________________Total Actual Hours – RO Time?Technician Productivity - did he produce enough flag hours?Total Flat Rate Hours - Flag__________________Total Available Hours – Clock Hours – Employee TimeTechnician Opportunity - did he take advantages of the opportunity we gave him? Total available hours – Time Clock time________________________Total hours shop was open 174 hours in a month, normallyTechnician Utilization - did we use him??Total actual hours - RO Time__________________Total available hours – Time ClockWhat (Who) is the problem?Tech comes in, but doesn’t punch on repair orders.Tech punches in on jobs, but then spends hours – checking manuals, double check the work, looking for tools and parts, showing the problem to other techs. Tech doesn’t come in for work. Doesn’t get paid for hours, but takes a lot of time off, also training – has 2x the training of others!Tech comes in and sits around and drinking coffee or hides. May punch on jobs, but most of the jobs either don’t have many flag hours, or wastes time. Hours to SellWhy didn’t we get 140 hours yesterday?What you Need for an Hours to Sell ReportAppointments today with estimated hours (3rd party system?)Available hours – (8 hours x 125%)Hours yesterdayMTD hoursDetail by advisor, summary of techsHow can I help?Was there PDI or Internals that could have been written?Did we call customers (move up appointments?)What is our campaign this month? Summer special, brake job, alignments?What you need for the Hours to Sell report to be accurate…Carry over that is ready to dispatch (no longer on hold for parts)Estimated hours entered for appointmentsDo you understand appointments vs. hours to sell? How many appointments can you book per day? ______ Appointments tracked electronically? _______Work in Process/Unbilled – what is that?Repair orders must be closedDo not hold open warranty claims What are my Metrics fixed operations?WorksheetParts and service gross profit $____________ Non-variable expenses $_______________Absorption _____ Parts fill rate % _______List Price __________ Cost _____________ Retail Gross Profit% ______Trade Price __________ Cost _____________ Wholesale Gross Profit% ______Your Posted Labor Rate* $_____ Tech Cost $_____ Metric for Gross Profit % ____Effective Labor Rate $____ Tech Cost_____ Metric for Gross Profit % ____Hours to sell vs sold _______ Technician (circle one) productivity, opportunity, efficiency, utilization _______%Cash is King! The Pain of Profit and GrowthCollections made EasyIs it really a collection issue or a lack-of-paperwork issue?Missing invoicesCan we get the copy to the client easier? Fax, email?Have you discussed the account with the parts manager, wholesale parts manager/representative, advisor, service manager?Do you have the right person calling customers?What did it take to “get” this account?Profit Accounting in Sales - it's time to get involved!Chargebacks– bad for morale!What and Why of Salesperson Productivity – how do we measure?Sales Gross/Expenses – metric and varianceDo they carry the models that sell at the best gross profit?CRM Software___________________What does it provide you with?Sales Gross Profit – 4 places!Gross in manager’s head -“I made $xx on that deal”Written on log, entered on form or in CRM softwareGross calculated by F&I/sales moduleJournal Gross Profit - Financial Statement Gross, True Journal gross - Less Charge backs, writedowns + Plus “funny money” and adjustmentsTop 5 Ways you “lose” GrossWholesale Loss – are you in the business of wholesaling vehicles? – should be zeroOpen Internals, Customer Repair Orders, Purchase Orders - chargebacksPromised items not set up on due bill – or not really promised – or was already on vehicle.Cost item should have gone to BE gross instead of FE grossNothing to reconcile to…Recap and displayed Cost is wrongCost - Open PO and Open RO - HomeworkStock#____ Accounting Cost _______ F&I manager _____ Sales Manager _______Stock#____ Accounting Cost _______ F&I manager _____ Sales Manager _______Stock#____ Accounting Cost _______ F&I manager _____ Sales Manager _______What are my key Sales Metrics?Units “out” – does not include wholesale, fleet?New ___ Used ____New Gross Profit Average $_______ Used Gross Profit Average per retail $______ includes wholesaleTotal F&I $______ Chargebacks $(______) Net per $____Total Expenses?Sales Commissions?Non-Variable?Understanding the BreakevenVariable Expense Metrics - Big 3Advertising $______________ per unit?Floor plan $____________ Rate ____% Credit?Floor plan Balance_____________ * rate = ___________Sales Commission WorksheetSales Commissions _______%Total new and used gross $_____Sales commissions $_______Variable percent ____%Gross Profit $ 1,942.00 Gross Profit? Pack $ 450.00 Pack?Payable Gross $ 1,492.00 Payable Gross?Percentage25%Percentage?Commission $ 373.00 Commission?Percentage19%Percentage?Example of Item Expense________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________Categories of Expenses - Objective - What Category?VariableFixedBudgetAre these Service Expenses a % of Gross Profit?Service Advisor Commissions_______ %Shop policy $_______ are you paying advisors on Internal gross?Outside Services $_______Shop Supplies $_______ Recovery Rate __% of labor Labor sales _____ * 5%Office Management-ChallengesHard to get more staff – authorized, findDifficult to get raises for our current staffStaff has no incentive to learn new skillsNo time to train – no training programs for office staff (techs go to school, sales people get trainers, etc.)No time, no time, no timeStrengths & WeaknessHow can you use your Strengths to overcome your weakness?How can you help your staff and other managers do the same? We all need file clerks…Time, Time, TimeHours that you work per week _______?Free time for ? What do you like to do?Do you work from home? Sat? _______Date that you close the books? _______How many people do you have in the office? _______Number of dealership employees?____Ratio ____ Are you 14:1 or 10:1?How Many People do you Need – are you the same?How many retail deals, wholesales?Service Advisors – ROs per day?Do they write PDI, internals?Do they dispatch, cashier?Salespeople – pay plans – complicated step?Paying employees – 1x month? 2x, weekly?Does your Office StaffRelieve cashiers, phones?Take deposit to bank, retrieve mail?Dealer trades, titlesCan we have someone else do this?More efficient to be done by operations as long as it doesn’t affect item C. - F&I manager submitting contractsRequires a skill set not available in an office person – calling past due accounts Part of pay plan – profit $$$ generating activity – approving overtime – if this is an ‘objective’ then put in pay plans at a higher premiumCan you do a 3-Day Close? Homework- ChecklistDo you need to do that? Look back at A, B, C skillsDo you need to do that?Does it need to be done as part of the close?Factory StatementSchedulesDo you need to do that? Faster way to do that?Easier Way to do that?Use your DMS better – Change or not?Time Management - TasksWhat do you do all day? What percent of your day is spent on each? ___% T – Tasks that you do as part of your job; reconciling accounting, paying the sales tax, checking the bank balance?___%F – Fire drills. I call this any unplanned issue; a customer who lost a check, an employee upset about their pay, someone wrecked their demo or got hurt in the shop, covering for a sick employee, training someone who is new, and the dealer with about any reason… ?___% M – Monkey. This is anyone who came into your office, emailed, texted, phoned to get something, ask you a question, tell you a story, manager meetings, or just chat with you about their weekend. Normally anything that is not a T or F is a Monkey…or the dreaded “Close” - close the books faster!List the Time Wasters for You________________________________________________________________________________________________________________________________________________________________________________________________________________________________________How to become a Task-based OfficeStep 1- Use the?TasksMaster Basic? (free for 3 months) version that we’ll be in our hands on session to determine if you have your tasks properly allocated by skill level. As a suggestion, put in Assigned to the person who is currently doing this and in the Backup, put the person you would like to train. Filter on Backup person to find those that need training and the tasks.Step 2?– Get the?TasksMaster Plus?version. Both the setup and training of your staff might require a consultant (like me!) and I can come to your dealership to reallocate tasks based on skill levelsl, train your staff to use TasksMaster daily, weekly, and monthly, and help them get your “HowTo” files completed. Having the TasksMaster Plus version becomes a documentation, evaluation, archiving and training system for your office staff.Step 3?– Starting using?TasksMaster Plus! A new “A” skill level person can start reading the?HowTo?and?Tips?of B and C tasks to see if they are ready to learn more and move to a B or C level person. You can start paying a bonus – or determine their current pay level by their skill level and the amount of each task they perform. What is the Skill Level of your Staff?Before you can delegate, you need to determine the skill levels of your office personnelAnybody? File, make coffee, put stuff in orderBetter trained and qualified personCAT ; Confidential, Accurate, TimelyA Level How many? ?Names B LevelHow many??Names C LevelHow many??Names Action Plan – Profit AccountingFind a better way to manage your office to get more time to be a Profit Accountant and not a bookkeeperStop putting them asleep with reports“Why” reports to increase profits and their pay…Breakeven, absorption of expenses – item expenses, not detailUse your metrics – not your 20 groupImprove Cash Flow - understand how profit contributes and depletes your cash Breakeven points to profit areas 5 to 15Why reportsAre you and your “Cats” doing too many A and B tasks?Using Technology – vlookup 4 hour floor plan down to 15 minutesPivot table – better reports for your managerAutoFilter – find the “what” for your “why”If – the good and bad!Conditional formatting – help them “see the answer” Survey and Certificate To Learn More…Training – how to get it and use itDMS system ADP, R+R – coached, Internet trainingSuper Excel/Super DOC – Learn standardized reportingSuper Controller Guide, 117 pages – whole dealershipSuper Controller 2 & 3 – 101 pages- Expenses & Employees Taskmaster - Over 300 Tasks – are you doing everything you should? Nadine’s consultingNew! Super Reporting –Operational/Outside and financial reports for you to create – detail on Top 10+ Risk areasNext Profit Accounting Academy ____________________Advanced?______________DealerStar – the newest DMS – looking for GM, KIA, VW, Chrysler and investors. Email sandi@ for a prospectus.Appendix A – Hands OnHands On - Using Excel for ProfitExcel has hundreds of features but today we’ll be covering just the best ones for creating Profit-focused spreadsheets! Open ProfitAcademy.xls file from your pre-workshop material.In our business, the most important features of Excel are; =If formula - HourstoSell tabTip! Quick Access Tool BarAutoFilter - HeatSheetPivot tables NewinStockVLookup - Heatsheet Functions/FormulasDecrease the amount of time you spend in Excel and increase the accuracy of your data and your reports.Absolute vs. Relative, F4. Click on HourstoSell tab and cell G1. Notice the formula=B6*8*$G$1. Click on the cell and press F4 and then copy downConditional formatting vs if statement Conditions (greater than or equal to, less than, does not equal)If Statements, =IF(L6<0,"bad", "good")This means if the amount in cell L6 is less than 0 days old, then put “bad” in cell, else, “good” - using an If statement means you filterAutoFilter Exercise:AutoFilter turns your date into a database and helps you find data in 2 sources. In this example, we have a schedule with all the vehicle receivables/contracts in transit that have been downloaded. We also have another data extraction of the deals (figross.) There is information on that which I'd like to have on my schedule report. Click on HeatSheet tabClick on filter icon - Data, Filter, AutoFilterCustom filters (text, numbers) - Top 10 - what are the top 10 oldest on the schedule?Sum vs. Subtotal formulaWhat is the total due on cash deals __________________?Heat Sheet Exercise (Vlookup)Naming a range to make formulas easier - figross VLookup=VLOOKUP($B5,FIGROSS,10,0)=VLOOKUP($B5,FIGROSS,10,0)This means – look up the Name in B5 in the named range “FIGROSS” and bring back what is in the 10th column. Discussion - what are the various things you can look up between 2 files - Control number? Putting deal number on schedule? Creating a Pivot Table Exercise:Tip! Keyboard shortcutsRow, ValuesValue Field Settings – type and number formatDesigns and stylesStep 1 – Basic pivot tableMODELCount of BALANCESum of BALANCE2Average of BALANCE3CADENZA11399049.08$36,277FORTE698532.17$16,422FORTE 5-DR8156489.97$19,561FORTE KOUP591901.67$18,380OPTIMA9199729.48$22,192OPTIMA HYBRID5137125.12$27,425RIO455358.88$13,840RIO 5 DR343081.53$14,361SEDONA10264162.49$26,416SORENTO11279183.85$25,380SOUL691926.58$15,321SPORTAGE6117266.06$19,544Grand Total841933806.88$23,022Step 2 – Make it fancyFix labelsFormat currencySort gross from high to lowExtra Credit - Hands On - Suggested StockCreate a comparison of what you have in stock compared to what you have sold.Using figrosspivotThe figrosspivot is a pivot table created by the figross download of our deals for the month. We used figross to lookup the bank name and other info for the heat sheet. Now we'd like to "dress up" the figrosspivot tableReport Filter (N/U/O) - filter on just N and New (older versions of Excel cannot do multiple filters and you would have to do a search and replace of the data - replace New with N) and then refresh the pivot table.Fix labels from Average of GROSS PFT to Avg Gross and # of Deals. In the pivot table list, find the field, right click and Value Field settingsFormat currency - using number format in Value Field SettingsSort gross from high to low - Right click on Carline field and Sort based on Avg GrossAdd how many in stock column - =VLOOKUP(A5,instock,2)Enter calc for 2 months (60 days supply) =C5*$E$2Enter calc to determine if amount is stock is high or low Over/Under =D5-E5Create an if statement that shows “low” and “high” =IF(F5>=0,"high","low")Add that column and determine if you are short or over on units.N/U/O(All)- Using the Page/Report Filter?Data?CARLINEAverage of GROS PFTCount of GROS PFTIn StockCADENZA1663.9851411CARAVAN SE4617.431FORTE1909.25510Your ResultThink about…How does the number we have in stock compare with our highest gross profit? What would be a 30 or 60 day supply? Who orders vehicles in your dealership?________________________ What do they use as a guide?______________________What does this show that information from the factory or outside services doesn't?Try these Tips + LLocks Computer - Requires Password to unlock if Screensaver password is turned on. + EOpens Windows Explorer + ROpens the Run Dialog + MMinimizes all windows and takes you to your desktop + FOpens the Search/Find Dialog + TabShows and Cycles through all open windows + TCycles through programs on the taskbarSnipping ToolHands On- Cash Flow StatementBasic – enter amounts manually in both columns. The most common problem is missing an account (if you want to balance!)? Cash Flow Worksheet ????Assets Last Month Last Year ?Current Assets:Cash in BankContract in Transit /Vehicle ReA/R + Factory Receivable New Inventory less floorplanUsed Inventory less floorplanParts/Sublet/WIPPrepaid/Other AssetsRental Units? Total current assetsTotal Fixed Assets Less-Accumulated depreciation Total fixed assets Other Assets Total Assets?Current Liabilities:Accounts PayableOther note payableOther; taxes, payroll, insurance Total current liabilities?Long Term DebtProfit or LossCapital and DividendsRetained Earnings?Total Term Debt and EquityTotal Liabilities and CapitalExtra credit – can you make this easier by “linking” to your GL or trial balance and downloading each month?Download your trial balance or Doc and use vLookup to enter the amounts Challenge – how can you “group” amounts? (use a DOC)How do you make sure you have everything being “looked up?” (use another lookup on GL table.)Hands On BreakevenClick on the Breakeven tab and use the following informationSales Cost of Sales GrossTOTAL FIXED?84.7%937,493 475,469 462,024 TOTAL NEW & USED (PVS)191 3,663,157 3,411,314 251,843 TOTAL F&I AND PROT.PLANS (PVR)?110,713 57,326 53,387 TOTAL VARIABLE L&R??23,670 17,148 6,522 TOTAL VARIABLE??3,797,540 3,485,788 311,752 ?TOTAL EXPENSES (INCL. PRO OF G&A)?609,846 TOPERATING PROFIT OR LOSS?163,930 ONET ADDITIONS & DEDUCTIONS?(17,686)TNET PROFIT OR LOSS BEFORE BONUS & INC. TAX146,244 ABONUSES-EMPLOYEES970 ?BONUSES-OWNERS980 LNET PROFIT OR LOSS BEFORE INCOME TAXES146,244 ?INCOME TAXES9922,622 ?NET PROFIT OR LOSS AFTER INCOME TAXES?123,622 VEH. SLSPLE COMPENSATION & OTHER1147,900 TOTRTLRTLPROFIT/NEWNEWUSEDLOSS82 76 67 123,622 Extra Credit – try doing a BreakEven with your Financial StatementHands On - Office Skill Levels and Organization – TasksMasterBetty Red's Office Betty is a “do it all type of person” - she is currently billing all car deals because she just lost her biller and Ellen doesn't know to bill out deals, Betty is short on cash, so she's pulling the contracts, calling all past due accounts – pretty much stressed out.Her right hand person, Carly is pretty much doing everything else.Betty Red - controllerCCarly Bean – Payroll/AR/APCEllen Jones – BillerAFrank Beam – Inventory clerk, dealer tradesAFreda Andrews – Bookkeeper/Cash salesAJane Smith – TitlesBTom MastersDealerThey hired Ellen and Freda recently, but haven't had the time to train them at all. Freda spends ? her day trying to balance cash sales and getting the deposit ready. Jane has been there a long time, knows a lot, but says that titles take up 100% of her time, in fact she works overtime. They sell 150 cars a month, about ? have trade-ins.Demonstration - Filtering1. How many Tasks are being done by Betty?A___ B____C____2. How many Tasks are being done by Carly?A___ B____C____DiscussionWhat tasks of Betty would you reassign to Ellen, Frank, Freda and Jane? (hint-find any A & B tasks assigned to Betty and Carly)What are the challenges of assigning tasks to Jane vs. Ellen, Frank, or Freda?Assigning to Jane (who thinks she is too busy)?Assigning to Ellen, Frank, Freda? (who are not trained)?Log in with your Name - (case sensitive) and password of acctAssign the tasks to your employees (don't worry if all are not there, we can discuss that later)How many A and B tasks are being done by your C staff?C – Staff Person (you)__________________Current number of tasksA___ B____C____C – Staff Person __________________Current number of tasksA___ B____C____B – Staff Person __________________Current number of tasksA___ B____C____B – Staff Person __________________Current number of tasksA___ B____C____Office PersonSkill Level??????????????Extra Credit on Simple and Complex Floorplan Interest 1. Download the spreadsheet from TaskMaster and study it2. Bring in all the fake "data" in the yellow and purple sections into a new spreadsheet3. Enter the formulas and create the Complex calc Flooplan Interest AccrualSimple method????Floorplan balance beginning of month $ 1,900,152 ?Interest free credit?Floorplan balance end of month $ 2,052,452 ?Units received $ 542,652 ? $ 3,952,604 ?Rate4.75%Average balance $ 1,976,302 ?Annual $ 25,775.97 Rate4.75%?Daily $ 70.62 Annual Interest $ 93,874 ?Days free20Daily $ 257.19 ?Credit $ 1,412 Days this month $ 31 ???Total Expenses $ 7,973 ???Credit* $ (1,412)???Accrual $ 6,561 ????????*Can also be matched with saleComplex method using 2 rates, and downloadTotal interest from Download 7,901.32 ??Per Lender 8,413.64 ??Variance 512.32 Steps;Determine start date for your lender - and enter that in "Received Date in your DMS"Update an unused field in your DMS with date floorplan paid.Make sure you are entering the floorplan amount in a fieldDownload the data from your DMS and put in yellow section of Download tabDownload interest from lender and put purple of lender tab.Can you answer this?Why use a named range for change, end, start date?Imbedded if statements - =IF(F7>change,0,(change-F7))How to handle reductions each month? ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download