Motomar Project

· For Step 1, you are to focus on the semi-fixed expenses and what is odd about the figures, not only their pattern. Think about whether the figures are right or wrong. What is it about the individual numbers that is not “right”?

· For Step 2, there are 5 expenses that have an oddity about them which doesn’t make sense. Similar to Step 1, what is it about the individual numbers that is not “right”? Expenses can go up. They can go down. And they can go up and down. When thinking about this, do not interject your own personal opinion about what the figures should be or why you think the figures are the way they are. Provide what is “wrong” with the figures and/or an unusual pattern.

· For Step 3, all of the figures are incorrect.

· For Step 4, see the attached Excel spreadsheet. It is an example of what needs to be done for each of the expenses in order to complete the FC, VC and R-sq columns in Table 6. Table 6 should be inserted in the Word document. Then answer the questions being asked for the step

The Excel spreadsheet has most of the work done for you.

· There is a “60 Months” worksheet that has the 60 months of data already entered. There is also a “Sample” worksheet that an example of how to calculate the R-sq.

· There is a “PLOT – SALARY” worksheet that shows how the FC, VC and R-sq figures are calculated for Salary.

· There is also a “high&low” worksheet for help with the high/low method in Step 3.

Complete and include the Excel spreadsheet. You will need to create new worksheets for each of the other expenses following the example to calculate the figures needed for Table 6.

· You are to include a completed Excel spreadsheet as support for the figures in the table for Step 4. Rework the spreadsheet following the instructions in the study unit. If you have any questions about what needs to be done, contact me. You included the Excel file; however, the file does not show the work necessary to support the figures presented in Step 4. So how did you arrive at those figures? No work to support the figures – no credit.

· For Step 5, rework this step based upon the corrected steps for the project.

· See any additional comments that were made.

Please rework and resubmit the project for grading. If you have any questions, feel free to telephone me toll free at 1.888.427.1000, Monday thru Friday from 9am-4pm EST.

Jim Burcicki

Senior Instructor

Motomart Case Study

Step 1: Analysis of Motomart 5-Year Income Statement

An analysis of Motomart Comprehensive Income Statement shows that the semi-fixed expenses indicate an increasing trend and this is probably because of the rising net variable revenue. Even so, the company’s total fixed expense is increasing at a faster pace compared to the rise in net variable revenues. This, therefore, is an indication that the semi fixed cost is mainly made up of fixed cost; otherwise the increment pace in semi fixed expenses would be equal to lower than the increment in the net variable revenues. The operating profit, on the other hand, has been declining from 1984 to 1988. It is approximately 43 percent in 1984 and falls to -196 percent in 1988, which is 5 years from 1984. The operating loss in the year 1988 is, unarguably, very much.

Note: 1984 is the base year

Step 2: Patterns in the Expense Items

The organization’s semi fixed expenses ought to have increased with the rise in the net variable revenues between 1984 and 1988. However, this is not the case as there are five expense items that have failed to increase but decline or portray mixed trend over the years.

For instance, the vacation item increased only between 1984 and 1985 by 26,105 dollars and decline by 7,237 409 dollars from 1985 to 1986, the value decreased further by 409 dollars between 1986 and 1987. The value decreased further by 791 dollars between 1987 and 1988.The reason for this unusual trend is that number of units have increased or decreased, and the other reason may be due to high and lose control on variable cost the variable cost per unit has either increased or decreased.

Freight expense showed mixed trend as it only increased by 11,706 dollars between 1984 and 1985.And as well as increased by 541 dollars between 1985 and 1986 respectively. The freight expenses, however, decreased by 797 dollars from 1986 to 1987. The value decreased by 1,068 dollars between 1987 and 1988. The reason for this unusual trend is that number of units have increased or decreased, and the other reason may be due to high and lose control on variable cost the variable cost per unit has either increased or decreased.

Motor vehicle expense showed mixed trend as there was a slight increase of 805 dollars between 1984 and 1985 and a steady decline thereafter, of 54 dollars in 1986, decline of 3,294 dollars in 1987 and decline of 887 in 1988. The reason for this unusual trend is that number of units have increased or variable cost per unit has increased in the early year and due to reduced number of units produced and sold, the cost control has reduced variable cost per unit.

The demonstrators’ expense decreased from 1984 to 1985 of value 5,496 dollars. And hitting a decrease of value of 3,456 dollars in 1986.With increase of 2,679 dollars from 1986 to 1987.There is a drastic decrease of 3,485 dollars in 1988.The most unusual trend is seen in this category of expense, the unusual increase and decrease may be due to some uncontrollable factors like inflation trends in variable cost per unit.

The floor planning expense showed mixed results with a slight increase of 22,582 dollars between 1984 and 1985. However, the values decreased between 1985 and 1986 to about 24,912 dollars.

Them a decrease of a 120,072 dollars from 1986 to 1987.Then a major increase of 148,915 dollars from 1987 to 1988. The reason for this unusual trend is that number of units have increased or decreased, and the other reason may be due to high and lose control on variable cost the variable cost per unit has either increased or decreased.

Step 3: High-Low Activity

The high-low activity points of each semi fixed expense item can be found by considering the high as well as the low points of each activity. The high value of a semi fixed expense, according to Hansen, Mowen and Guan, (2007) is for that particular period. However, the value of the low point is for the previous year. It is very difficult to have accurate forecast when the high and low activities do not match the high-low expense measures. The synchronization of the high -low points method of the semi fixed expense item with the high –low points of the New Retail Vehicles to derive the fixed and variable expense is not justifiable as shown below

The other option for determining the fixed as well as variable elements of semi fixed expense is by considering the total amount of semi fixed costs (Macintosh & Quattrone, 2010). For demonstration, the 1988 figures will act as the high point while the 1984 values will act as the low point. Using the linear equation formula Y= a + bx, where a is the fixed cost and b the variable cost the variable expense accounts for nearly 70 percent of the net variable revenues with fixed cost amounting to -847,245 dollars.

Variable expense = 988,287/1,412,779 =70 percent

Fixed cost = 1,172,933-2,885,969*0.7 = -847,245.3

Therefore, the fixed and variable cost elements of semi fixed expense should be found through statistical method rather the high-low method

Step 4: Cost Equations

1. What problems did you encounter?

The data was very big and most of the variable costs are negative, which is practically impossible. The new retail vehicle is used as the base, and this is not a true representative of the organizations activity. In fact, some other base like units of output ought to have been used

2. Are the R-squared measures high or low?

The R squared values are very low and in some cases it is zero. This is a justification that the new retail vehicle is not the most appropriate base for computing the organization’s activity

3. Are the slopes negative or positive?

Most of the slopes are negative

4. Are your conclusions consistent with those from the high-low effort?

No, this is due to the fact that whereas the high low formula is leading to negative fixed cost the regression techniques is showing negative variable costs

Step 5: Summary

The organization’s data can indeed be used to come up with a reliable financial forecast. This is due to the fact that it is common for organization’s to have poor financial records during the year. With the help of a qualified accountant the organization can develop a reliable financial forecast. Cataldo (nd) assert that most businesses focus on reducing the financial record keeping costs bearing in mind that the salary of a bookkeeper is much less than that of a certified public accountant. These businesses then seek the services of accounting firms, at the end of the financial year, to adjust the journal entries for the 12 months of the year.

It is important to note that the experts hired to determine the organization’s profitability prospects for relocating to the new site found that it was impossible to develop a financial forecast. The alternative forecast that was developed did not support the organization’s desire of relocating closer to the exiting dealer. In particular, the experts found that the market could not support two automobile dealerships (Cataldo, nd). Even if the organization used a very large database to prepare the financial forecast that supports its relocation to existing dealer it would have failed because as the weaker business of the two Motomart would lose business to the other dealer.

Personally, I would not rely on this forecast because in the first place the use of new retail vehicle as the base year does not provide a clear picture of fixed as well as variable components of semi fixed expense. Whichever method is employed, whether it is regression technique or high-low method, it is appropriate to use another base like labor hours used or the number of units produced to give a clear forecast.