ABC Liquors Excel Forecast Sales Assignment

Sales by Store by Product Class

Store 4 led in the amount of spirits sales last year, after selling 111 items. Store 1, on the other hand, led in the wine sales, with 103 items sold that year. Therefore, the company should ensure that Store 4 is prioritized while distributing supplies of spirits and the same case for Store 1. They can consequently maximize the sales there new by investing more resources in Spirits’ marketing campaigns around store 4 and of wine around store 1.

This also presents an excellent opportunity to discover why a particular class of product is more prevalent in one market than other markets. For example, spirits are generally more expensive than wine therefore with an average price of $22.91 as compared to the $15 that whine sell at on average. Therefore, the management can try to lower spirits prices in store one in an attempt to increase their demand.

Sales by Vendor to Identify the Top 5 and Bottom 5 Vendors

Top 5 Vendors

Bottom 5 Vendors

The top five vendors make almost half the sales (43.43 percent). This is an implication of their value to the ABC Liquors at large the management can prioritize these stores before in terms of allocation of resources to optimize their productivity. It is also important for the management to identify and understand the cause of this disparity in terms of the productivity of between these vendors’ performance and the rest of the chain.

By determining whether these factors are internal or external and whether the management has control over them, it is good to ensure that the rest of ABC Liquors catches up. On the other hand, Drastic changes should be carried on the bottom five vendors, as management can use them for experimenting on changes before implementing them on the rest of ABC Liquors, as they present minimal risk to the survivor of the business.

Sales by Employee by ABC Liquors Excel Forecast Sales Store

Read Also: Project Selection Methods: NPV, IRR, & Payback Period

The employee who sold the most product is Jerry Lawler, who works in-store 1. He sold 4389 times valued at $50960.11 and was followed closely by Rebekah Mraz 4338 items worth $48385.62. On the other hand, Tim Smith was the poorest performing employee, who sold 1350 items worth $10121.5 in total.

The margin between the best and poorest performing employee is significant, something that can be attributed to the store’s general performance, as the best performing employee worked in store 1 while the worst performing one, Tim Smith and Mike McCarthy both work in store three. There is also a significant correlation between the number of items sold and the value of sales as employee with the most sales value had also sold more items.

Net Income by ABC Liquors Excel Forecast Sales by Month

A Histogram of Profit Margins for each Product Class

The most common profit margin was 35.54 percent and 40.54 percent for the wine, with 778 items sold in total, while least laid between 45.54 percent and 20.54 percent, with only four entries. Therefore, it is clear that the wines that generate the most sales yield moderate profits while the business yields the most profit margin from the least popular among customers.

The most popular spirits yield between 19.52 percent and 24.52 percent in sales with 531 entries while the least popular spirits yield between 9.52 percent and 14.52 percent, with 164 entries. It is therefore clear that the spirits that yield more profit margin for the business are also the most popular among customers. This can also be proof that the demand for spirits are price inelastic, thus the business can raise the process of spirits without the risk of a significant fall in sales.

Benfords Analysis on the Total Sales Value of each Record

Frank Benford suggested that people are committing fraud and fixing numbers with an attempt to commit fraud, they are more likely to limit themselves to a given set of digits. They are also likely to choose numbers closer to 1, and this reduces to nine. The yellow line depicted in the figure above, represents the line of behavior of Bedford’s numbers, while bars represent the most common first digits as used in the sales revenue. By superimposing the two graphs, it is clear that the first digits are consistent with Benford’s prediction of fraud. This method, therefore, processes that fraud was committed.

Relationships between Employees and Vendors

Particular vendors seem to have relationships with the employees; for example, Diageo North America Inc And Jim Beam Brands Company appear to have comprised a significant proportion of the various employee sales. There is, therefore, a need for the mode of operation of the vendors within ABC Liquors to be monitored to ensure that they do not influence the employee. There may be fraud being committed.

Gap Analysis on Transaction ID

Gap analysis can be conducted on the transaction IDs, which are usually chronologically ordered in ABC Liquors. One can, therefore, monitors whether there is a significant difference between the number of transactions made and the movement of the transaction ID. Thirty-two thousand three hundred eighty transactions were recorded in the data set, while the transaction ID starts at 100001 and ends at 132404, recording 32 404 orders according to the allocation of the transaction ID. Thus 24 transactions cannot be accounted for, showing that fraudulent activities may be happening.

Break-even Analysis by ABC Liquors Excel Forecast Sales Store

In order to break even, Store 1 would have to sell 24569 items, Store two would have to sell 19289 items, store 3 would have to sell 2302 items, store 4 would be 10023 items, while store 5 12391items. Given that store one is the best performing store in the chain, its also have a higher break even point, while store three the worst performer has the least break even point. It is therefore clear the stores where more resources have been invested seem to perform better.

Sales Forecast for the First Quarter of Next Year for the Combined Stores

The projection of the performance of the store within the next quarter is as follows: for store 1 34792, 34637, 33794 for the first three months of the next year, respectively. While 29347, 28756, and 27451 respectively, store 2, 2037, 2081, and 2043 for store 3, store 5 is expected to sell 14700, 14104 13537. On the other hand, Sore fire is expected to sell 6044.136, 5669.75, and 5413.446 for the first three months of the next year, respectively. It is therefore clear the best performing, store 5 is expected to perform much better the next year. Thus, the management can rally its resources in marketing and investment into the store to ensure that this happens.

Best Decision for Management to Make

The three projects have positive net present values earning that they are all viable to be carried. However, the management is more likely to yield more benefit by investing into the Michigan project as it is expected to yield the highest economic benefit within the next 5 years. The Michigan project is the most prospective for the management as it has the highest Net present value of 70,904.25.

The next viable project is Rhodes Island, expected to yield, $56,687.60 and Vermont comes last with a net present value of $52,414.91. Therefore, it is important to note a significant difference between the best and second-best project, meaning that the manager should focus on the Michigan project with no doubt about its productivity. It is also important not that the discount rate las less influence on a project’s productivity. Therefore, the manager should focus less on government regulations and taxes and more on investing more in a project and ensuring that it has viable sash flows.

New Sales Tax Calculation for Each Transaction

This change would affect the margin of the products sold but the business differently, with some having a positive and others having a negative difference. However, it is essential to note that the flat tax will affect expensive products that came a cheap one. Therefore, the business can mitigate the loss suffered by ensuring that its sells more luxurious products than the cheaper ones.

Given that spirits have more inelastic demand, the increase in taxes will not significantly affect their demand. The wine will, however, be more affected by the increase. The is, however, many uncertainties that will come with the loss it is therefore essential for the ABC Liquor management to influence the lobbyists into working to the rejection of this law.

ABC Liquors Excel Forecast Sales on Each Vendor at a Time

The company is expected to profit from 8962.01 from its business with J Gallo Winery afters flat $ 3 increase in its prices and a loss of -488.97 while it continues with its interaction with Banfi Products Corp. Therefore, the company can maintain these vendors as the cost incurred for the business to maintain its products outweighs the benefit of foots traffic, thus increasing the chances of more sales. However, it is important to confirm whether these products can be obtained from other vendors to avoid losses. The company can also try to reduce the overheads spent on Banfi Products Corp if they choose to continue increasing the prices of its products.

Read Also: NPV Marketing Strategy Memo