The Slick Jim’s Used Cars: Three-Phase Spreadsheet Calculator

NB: We do not resell your papers. Upon ordering, we do an original paper exclusively for you.

NB: All your data is kept safe from the public.

The Business Case

The Melbourne-based Slick Jim’s Used Cars is a six-man operation that obtains stock at auction, advertises these, and offers both warranty and insurance services. As well, all three Salesperson earn a commission from every unit they sell. In total, Slick Jim’s incurs four principal categories of expense for each car sold since warranty repair is farmed out to repair specialists and premiums forwarded to insurers. Each expense varies according to the unit price of the vehicle.

Concerned about an unsatisfactory cash position, Jim has already called in an accountant to review hybrid recordkeeping systems that combine manual and incompatible electronic systems. The latter means that three of the staff whose jobs involve administrative tasks for sales, warranty repairs, insurance and advertising need to spend time transferring data. Consequently, they constantly run the risk of inaccuracies. Disparate systems and paper-based records also mean inefficiencies and opportunity losses when files go missing.

Jim has also seized on the chance offered by the presence of RMIT students to provide the company a system that will more reliably generate reports around gross revenues, advertising costs, a schedule of insurance premiums the staff can use, existing profitability by car category and by sales staff member.

This report describes a recommendation for an integrated system of spreadsheet calculators that will accurately tap existing data files, seamlessly and reliably calculate the variety of reports wanted, and provide Jim the operational analyses he requires as well. Usability must also be enhanced by providing a menu-based interface. The systems must have scope for more data as business operations continue. On Jim’s directive, profitability analysis should include inputs about the vehicle models that are more popular domestically. Lastly, concise and meaningful reports must be generated.

An Analysis of Jim’s Car Profits by Category

Going by gross revenues, prestige-class are the primary contributors to Jim Slick’s business for contributing over one-third (36%) of cash inflows. The second most important vehicle types are sedans (accounting for 20% of total revenue), closely followed by 4-wheel drives (4WD’s) with 16% of gross revenues.

At the other extreme, moving poorly off Jim’s lot are wagons (nothing at all), soft roaders (a puny 1% of gross sales), utility vehicles (just 6% of gross dollars), hatchbacks (6%), people movers (7%) and commercial vehicles (7%).

The prestige cars are the most valuable part of Jim’s business. Though the company sold only ten that month, they went for an average of $54,945, 58% better than the $34,757 typically obtained for the second most pricey vehicle type Jim dealt in, the 4WD’s. That average price difference may have been the reason why Jim pegged commissions at the top rate of 5% for prestige-class vehicles versus 4% for hatches and commercial vehicles. A single percentage point may not seem like much but this meant that a relative handful of prestige cars sold kicked in no less than 44% of the total commissions earned by the sales force that month. This is the reason why prestige cars contribute just 33% of gross profit, after accounting for selling price and sales commissions.

Sedans are the most popular model at Jim’s dealership. A total of 20 moved off the lot in January, over a third (35%) of total units sold. Given that they sell reasonably briskly, the sales commissions are among the lowest at 3%. Because sedans are rather like entry-class models that are popularly-priced (averaging under $15,000), contribution to gross revenue is lower (20%) than share of volume sales. Still, the thrifty commission rate ensures that sedans provide a 24% share of Slick Jim’s gross profits that shows up the much-pricier 4-wheel drives (18% of gross profits).

Whether Jim Should Specialise In Any One Particular Category or Which Categories (If Any) He Should Discontinue Stocking

Slick Jim’s does minuscule business in wagons (none at all in January), soft roaders (just 1), and utility vehicles (3 sold that month). Assuming this reflects a general lack of market interest, Jim may well consider not acquiring any more of these unless he can afford the carrying cost.

In fact, digging a little deeper would enable Jim to fine-tune his operation and improve cash flows. For instance, the solitary soft roader got sold after just 17 days on the lot, the fastest turnover of any category except, of course, the ever-popular sedans. If bolstered by market-preference data, this suggests Jim ought to bid aggressively at auctions to acquire more such vehicles.

Jim should drop utility vehicles. Acquisition price is third highest (after 4 wheel drives and prestige cars), his gross margin is comparatively low at around $3,000 per unit, and turnover is the worst of all vehicle types, no less than ten months. After deducting commission, Jim makes a gross profit of just $5,500 per unit, surely not enough to pay for ten months of carrying cost.

By the same token, people movers have to go since each one ties up working capital for about 270 days or nine months.

A case can be made for putting more advertising support behind hatch-class vehicles since stocking carry stands at a relatively speedy 80 days and gross margin is at least satisfactory. In fact, much the same idea might be explored to reduce the half-year carry characteristic of the profitable prestige and 4WD lines.

A useful way to optimize advertising expense is to look at such outlays in relation to the income they generate. The sales and media expenditure for January reveal that Jim spends just 1 percent his hatchback revenue dollars on advertising. Bidding to acquire more of these and ramping up ad spend could well generate more positive cash flows.

At the same time, the amounts spent on slow-moving classes like soft roaders, utility vehicles and commercial types could perhaps be dialled back and spent more profitably on boosting prestige and 4 wheel drive business.

Identification of the Most Popular Cars in the Categories Which Jim Should Continue To Stock

Australian Bureau of Statistics data show that one in six registered vehicles were commercial vehicles. Within the 80% of the market accounted for by passenger cars, AussieMove.com suggests that over two-thirds are semi-automatic or automatic types. As well, there is brisk demand for sports cars/convertibles and “ute”/pick-up in the second-hand market. All these are cues that Jim should consider re-focusing acquisition and advertising resources towards the profitable prestige (European) makes, the fast-moving sedans (like the Holden Commodore but also, increasingly, Japanese makes), sports cars/convertibles and “ute”/pick-up’s. As well, he should veer away from outmoded manual-transmission models.

Ad Expenditures

Prestige and 4WD’s drive half the gross revenue at Jim Slick’s. In both cases, it is obvious from the gap between gross profit and net margin after deducting ad spend that the company is spending disproportionately more to get customer traffic and attention. By comparison, sedan and hatch ad spend is in line with gross revenue.

Cross and Net Profit

Spending disproportionately more for advertising is, by itself, not a bad thing since the 4WDs and prestige makes are superior in unit profitability anyway. What may be fruitfully explored, however, is having a media budget for sedans at all since institutional advertising will attract customers to this popular car class anyway and even perhaps drive incidental traffic for the less-popular items.

Recap: How the Spreadsheet Additions Bring New Functionality and Help the Business

The work done at Jim’s behest is at once strategic and advances the operating efficiency of the organisation. The strategic benefit has to do with improving cash position, observing which Jim felt vague unease that there were matters that needed attending to but which he could not pinpoint or prioritise.

Taking just a one-month sample, the spreadsheet solutions revealed that, among the four categories of expense covered by the case, vehicle acquisition and direct selling expenses merited attention. The fairly rapid turnover of sedans needs leveraging into higher margins, perhaps with better selling prices or less intensive advertising effort. There is also an opportunity to bring commission rates for high-margin prestige cars in line with that of 4WD’s so as to maximise gross profits from this important segment.

If the rapid turnover of the single soft roader sold that month is anything to go by, Jim should bid aggressively for more such models. After all, greatly-reduced stocking cost, compared to the hundreds of days other models take to move off the lot, bears its own advantage for strengthening cash position.

In turn, a separate analysis of advertising expenses converted a fairly complex, three-step process of media planning into a campaign-at-a-glance status board. By getting a head start on automating the process of deciding on placement frequency, on medium choice, and on ad material type for each car category, the advertising calculator ensured that costly deviations from standard ‘best practice’ would be brought to Jim’s attention and prevent wasted marketing expense.

In Phase 3, finally, the recommended solution for quickly and easily completing an insurance quote by converting manual calculations of three premium components to an automated premium calculator requiring only the selling price as input is a harbinger of improving the efficiency of the operation. Clearly, converting document-based processes to a robust spreadsheet database not only preserves the completeness of insurance information but also hints at the possibilities for integrating the administrative side of warranty repair procedures.

Click Here To Order Now!