Question

Project Objectives:

Use Microsoft Excel’s charts and formulas to analyze a company’s financial statement and sales performance;

Write business report based on data analysis and incorporate Excel charts into the report;

Introduction to Dirt Bikes USA

Dirt-Bikes USA is a small company headquartered in Carbondale Colorado that manufactures and sells its own brand of off-road motorcycles. It was founded in 1993 to product dirt bikes that could be customized for racing and off-road recreational riding using the best quality components and parts from all over the world. The company has continued to grow and now faces a new set of challenges and opportunities. You have been asked to serve as a consultant to apply your information systems knowledge to help Dirt-Bikes solve some of the problems it is encountering.

Company History and Background

Dirt-Bikes USA was founded in 1993 by Carl Schmidt and Steven McFadden, two young but experienced bikers with engineering backgrounds who saw that dirt bikes were becoming very popular in the United States as both sporting and racing motorcycles. They developed frames for dirt bikes that were more suited to off-road handling and started using these frames to build their own dirt bike models using motorcycle engines manufactured by other companies, such as Honda and Rotax Motors of Austria. Riding on one of their customized dirt bikes, Steven finished first in the famous Barstow to Las Vegas race. There was so much interest in Carl and Steve’s bikes that they decided to open a production facility that could manufacture large numbers of their dirt bikes for the retail market. They opened a small production facility in Carbondale which has since expanded to house 120 workers involved in production, design, and engineering and a corporate sales and administrative staff of close to 20 employees. Over the years Dirt-Bikes USA has enhanced and expanded its product line to include dirt bike models optimized for racing and for off-road recreational use. Its racing models have placed well-and often placed first-- in the many dirt bike races staged throughout the United States, including the Barstow-Las Vegas race and competitions at Daytona Bike Week.

Organization Chart and Employees

Dirt-Bikes USA is still privately owned with Carl serving as CEO and Steven as President and Chief Operating Officer. About 120 employees work in design, engineering and production, including 3 full-time product designers and 3 engineers. In addition to a 4-person Parts department, Dirt Bikes USA maintains a ten-person service department to service warranties and customer problems with parts and motorcycle performance. Five employees work in Dirt-Bikes’

shipping and receiving department. Dirt-Bikes’ sales staff consists of a marketing manager and 5 sales representatives, two for the West coast and Western United States, one for the Midwest, one for the Northeast and one for the South. The corporate administrative staff consists of a controller, one accountant, one administrative assistant, two human resources staff members, three secretaries, and two information systems specialists to support systems servicing all of the business functional areas. The following shows Dirt-Bikes’ organization chart

CEO and COO

Production

Administration

Sales & Marketing

Parts

Shipping & Receiving

Manufacturing

The company maintains a very friendly family atmosphere, encouraging teamwork, attention to detail and quality, and continual learning and innovation. Employees, distributors, and retail customers are urged to contribute ideas on how to improve Dirt-Bikes’ products and service.

Products and Services

Dirt Bikes’ founders realized that the most popular dirt bikes were foreign brands and wanted to capitalize on their proximity to the dirt bikes racing circuit and market in the United States. Carl and Steve hoped they could develop bikes that performed and looked better than the competition by using the best custom parts available. Dirt-Bikes USA does not hesitate to use quality components from all over the world. The engines for Dirt-Bikes are Rotax engines from Austria and tires are from Dunlop, but many of their parts, such as shock absorbers, front wheel forks, exhaust pipes, and headlights, are from the United States. Dirt-Bikes USA makes its own frames, shaping them to give them the unique spirited style for which the company is noted. The company’s parts and service business account for about 15% of its total revenue.

Manufacturing and selling dirt bikes is a complex business. Dirt bike racing has many forms, including racing specifically for different size bikes, for short distances, long distance, and even for up to six days. Enduro bikes are for cross-country racing and motocross bikes are specially designed for racing in an enclosed dirt course that can consist of a variety of terrains; uphill, downhill, corners, jumps, and so forth. Dirt-Bikes USA currently produces four models: the Enduro 250, the Enduro 550, the Moto 300 and the Moto 450. The two Enduros are endurance racers, while the Motos are for motocross racing. All four are very modern, with such technology as both kick and electric starters, steering stabilizers, and liquid cooling. The large majority of these bikes are sold in the United States for between $3,250 and $9000 retail. (The Enduro 250 retails for $3250, the Enduro 550 retails for $7600, the Moto 300 retails for $4295 and the Moto 450 retails for $8995.) Dirt-Bikes USA has appealed primarily to serious trail and Enduro riders, although it is making inroads into the motocross market.

Service

Design & Engineering

Sales and Marketing

Dirt-Bikes USA does not sell directly to retail customers, relying on a network of 40 distributors concentrated in the Western and Midwestern United States. A small percentage of Dirt Bikes are sold in Europe using independent distributors that sell other brands of dirt bikes and motorcycles as well as Dirt Bikes. Dirt Bikes’ motorcycles, parts, and service, including warranty repairs, can only be obtained through an authorized Dirt-Bikes dealer. All motorcycle and spare parts sales, shipping and set-up must be handled by a certified dealer. If a potential customer lives more than 50 miles from the nearest authorized Dirt-Bikes dealer, the customer can purchase a Dirt Bike or Dirt Bike parts through a certified independent motorcycle dealer. Retail customers can purchase spare parts directly from Dirt-Bikes only by verifying that they live more than 50 miles from an authorized Dirt Bikes dealer.

Dirt-Bikes’ marketing department works closely with Dirt-Bikes’ distributors. One of its key responsibilities is to aggressively promote Dirt-Bikes at dirt bike racing and other events. Many Dirt-Bikes employees are dirt bike racing enthusiasts themselves. Several are official company racers representing the company in dirt bike racing competition. Dirt-Bikes recently established a Dirt-Bikes USA Owners’ Group to promote stronger relationships with customers and to make it easier for them to share their Dirt Bikes USA experiences. Dirt-Bikes USA also advertises in magazines devoted to motorcycle racing and dirt bikes. It uses a small public relations firm to place articles about new company products or racing victories in these magazines. Dirt-Bikes USA also pays for ads in these publications.

Selected Financial and Sales Data

The company has provided you the following datasets for you to review:

· Income statements from 2011-2013

· Summary balance sheet data from 2011-2013

· Annual sales of each Dirt Bikes model between 2009 and 2013

· Total domestic vs. international motorcycle sales between 2009 and 2013

The income statement and balance sheet are the primary financial statements used by management to determine how well a firm is performing. The income statement, also called an operating statement or profit and loss statement, shows the income and expenses of a firm over a period of time, such as a year, a quarter, or a month. The gross profit represents the difference between the firm’s revenue (or sales) and the cost of goods sold. The gross margin is calculated by dividing gross profit by revenues (or sales). Net profit (or loss) is calculated by subtracting all other expenses, including operating expenses and income taxes from gross profit. Operating expenses are all business costs (such as expenditures for sales and marketing, general and administrative expenditures, and depreciation) other than those included in the cost of goods

sold. Net margins are calculated by dividing net profit (or loss) by revenues (or sales).
A balance sheet provides a snapshot of a company’s financial assets and liabilities on a given date, usually the close of an accounting period. It lists what material and intangible assets the business owns and what money the business owes either to its creditors (liabilities) or to its owners (shareholders’ equity, also known as net worth). We have included here are only the

most important pieces of balance sheet data for you to review. At any given time a business’s assets equals the sum of its liabilities plus its net worth. Current assets include cash, securities, accounts receivable, or other investments that are likely to be converted into cash within one year. Current liabilities are debts that are due within one year. Long-term debt consists of liabilities that are not due until after a year or more. If too much debt has been used to finance the firm’s operations, problems may arise in meeting future interest payments and repaying outstanding loans.

By examining a series of financial statements one can identify and analyze trends in the financial strength of a business. When examining Dirt-Bikes’ income statement and balance sheet data, pay special attention to the company’s three-year trends in revenue (sales), costs of goods sold, gross margins, operating expenses, and net income (or loss). Pay attention to whether the company’s short and long-term liabilities are growing and whether they exceed assets. If a company has more current assets than current liabilities, it is a sign that it probably has enough working capital to fund investments in new equipment or information systems.

The two other spreadsheets present motorcycle shipment data between 2009 and 2013, which can be used to gauge motorcycle sales. When examining these spreadsheets, pay attention to the trends in sales. This includes the sales trends for each product Dirt-Bikes USA sells, overall sales trends, and the proportion of international to domestic sales.

Dirt-Bikes' income statements from 2011-2013 (For 2012 and 2011, the numbers in BLUE cells are purposely left blank, you need to calculate them using Excel formulas and functions)

	Consolidated Satements of Income (in thousands)
	2013 2012 2011
Revenue
Net sales	60,144 64,063 61,529
Cost of goods sold	45,835 43,155 41,072
Gross profit/(loss)	14,309
Gross margin	23.8%
Operating expenses
Sales and marketing	4,733 4,537 3,944
Engineering and product development	3,141 2,992 2,339
General and administrative	1,913 1,601 1,392
Total operating expenses	9,787
Operating income/loss	4,522
Other income/expense
Interest income/expense	1,747 175 80
Other income/(expense)	(6,254) (2,914) (3,080)
Income before provision for income taxes	15
Income taxes	1,459 1,729 535
Net income/(loss)	(1,444)
Net margin	-2%

Dirt-Bikes' summary balance sheets from 2011-2013 ((For 2012 and 2011, the numbers in BLUE cells are purposely left blank, you need to calculate them using Excel formulas and functions)

	Summary Balance Sheet Data (in thousands)
At December 31	2013	2012	2011
Current assets
Cash and cash equivalents	6,994	7,197	6,891
Accounts reveivable	13,083	12,981	12,872
Inventories	6,315	5,931	5,843
Total current assets	26,392
Property plant, and equipment	36,920	34,515	32,002
Other assets	1,765	1,903	1,834
Total assets	65,077
Current liabilities
Accounts payable	8,943	8,694	7,592
Accrued expenses and other liabilities	10,877	9,382	8,654
Total current liabilities	19,820
Long-term debt	9,772	9,338	8,890
Total liabilities	29,592
Shareholders' equity	35,485
Total liabilities + shareholders' equity	65,077

Dirt-Bikes motorcycles sales by model between 2009 and 2013 (For 2010 to 2013, the numbers in BLUE cells are purposely left blank, you need to calculate them using Excel formulas and functions)

	Sales by Model (in thousands of dollars)
Model	2009	2010	2011	2012	2013
Enduro 250	1201	1663	2291	2312	2195
Enduro 550	2832	3290	3759	4078	3647
Moto 300	1755	1932	2454	2615	2627
Moto 450	463	598	661	773	823
TOTAL	6251

Dirt-Bikes' domestic vs. international motorcycles sales between 2009 and 2013. (For 2010 to 2013, the numbers in BLUE cells are purposely left blank, you need to calculate them using Excel formulas and functions)

	Domestic vs. International Sales (in thousands of dollars)
	2009	2010	2011	2012	2013
Domestic	5723	6843	8254	8889	8530
International	528	640	911	889	762
TOTAL	6251
% International	8.4%

Project Requirements

Enter the datasets (4 tables) into Microsoft Excel as four worksheets in one file (workbook). Finish calculating the remaining “BLUE” cells. All “BLUE” cells in the table must use Excel formulas/functions to calculate.

Use Excel to create graphs of Dirt-Bikes’ sales history from 2009 to 2013 and its domestic versus international sales from 2009 to 2013. Select the type of graph that is most appropriate for presenting the data you are analyzing.

Use Excel to create graphs showing trends in selected pieces of Dirt-Bikes’ income statement and balance sheet data. (You may want to rearrange the historical ordering of the data if you decide to do this.)

Prepare an analysis report to company’s top management that answers these questions:

· What are Dirt-Bikes’ best- and worst-performing products? What is the proportion of domestic to international sales? Have international sales grown relative to domestic sales?

· Are sales (revenues) growing steadily, and, if so, at what rate? What is the cost of goods sold compared to revenues? Is it increasing or decreasing? Are the firm’s gross and net margins increasing or decreasing? Are the firm’s operating expenses increasing or decreasing? Is the firm heavily in debt? Does it have assets to pay for expenses and to finance the development of new products and information systems?

Answer 1

Hi,

Please find calculation below.

Year	2013	2012	2011
Revenue
Netsales	60,144	64,063	61,529
Cost of Goods sold	45,835	43,155	41,072
Gross profit/(Loss)	14,309	20,908	20,457
Gross Margin	23.8%	32.6%	33.2%
Operating expenses
Sales & Marketing	4,733	4,537	3,944
Engineering and product development	3,141	2,992	2,339
General and administrative	1,913	1,601	1,392
Total operating expenses	9,787	9,130	7,675
Operating income/loss	4,522	11,778	12,782
Other income/ expenses
Interest income/expense	1,747	175	80
Other income/(expense)	-6,254	-2,914	-3,080
Income before provision for income taxes	15	9,039	9,782
Income taxes	1,459	1,729	535
Net income/(loss)	(1,444)	7,310	9,247
Net margin	-2%	11%	15%

2)	Summary Balance Sheet Data (in thousands)
At December 31	2013	2012	2011
Current assets
Cash and cash equivalents	6,994	7,197	6,891
Accounts reveivable	13,083	12,981	12,872
Inventories	6,315	5,931	5,843
Total current assets	26,392	26,109	25,606
Property plant, and equipment	36,920	34,515	32,002
Other assets	1,765	1,903	1,834
Total assets	65,077	62,527	59,442
Current liabilities
Accounts payable	8,943	8,694	7,592
Accrued expenses and other liabilities	10,877	9,382	8,654
Total current liabilities	19,820	18,076	16,246
Long-term debt	9,772	9,338	8,890
Total liabilities	29,592	27,414	25,136
Shareholders' equity	35,485	35,113	34,306
Total liabilities + shareholders' equity	65,077	62,527	59,442

3)

	Sales by model (in thousand of dollars)
	2009	2010	2011	2012	2013
Enduro 250	1,201	1,663	2,291	2,312	2,195
Enduro 550	2,832	3,290	3,759	4,078	3,647
Moto 300	1,755	1,932	2,454	2,615	2,627
Moto 450	463	598	661	773	823
TOTAL	6,251	7,483	9,165	9,778	9,292

4)

	Domestic vs. International Sales (in thousands of dollars)
	2009	2010	2011	2012	2013
Domestic	5,723	6,843	8,254	8,889	8,530
International	528	640	911	889	762
TOTAL	6,251	7,483	9,165	9,778	9,292
% International	8.45%	8.55%	9.94%	9.09%	8.20%

On the basis of this,

The profitabiltity has decline significantly for 2013.

Endura 550 is the best product as of now for the company