Excel Assignment

Assignment 5

Fall 2014

This is an assignment dealing with beginning and intermediate level Excel. In this assignment you will develop a cash budget for a new product. This assignment consists of two separate parts that will be submitted and graded separately.

General Problem Description

The Ajax Company wishes to develop a new product for sale. They have developed a prototype of the product and believe that there is substantial market potential for the new product. They have also done a preliminary profitability budget. This profitability budget is on the spreadsheet provided with this assignment. However, before they engineer the final product and start producing it, they wish to determine not just the profitability of bringing the product to market, but also the cash flow that will be needed for the project. While profitability is important in the long term, they feel cash flow is of critical importance, especially in the shorter term. For this assignment you are to expand the profitability spreadsheets and add a spreadsheet that projects cash flows for possible scenarios with respect to producing and selling this new product.

Profitability Budget (Already done and included with spreadsheet provided)

Ajax engineers, sales staff and accountants have initial estimates of the quantities they will be able to sell, the selling price and the fixed and variable costs involved with the product. Since these estimates are primarily informed guesses, all of this information is parameterized in the spreadsheet that is provided. This parameterization allows management to do sensitivity analysis where they can change some of the assumptions and see how that affects profitability. When you are constructing the spreadsheet, you should note that sales are in whole units (no fractions). That is, fractional units are either rounded up or down determined by the size of the fraction. Also since these are rough estimates, the results should be in whole dollars (no cents).

This spreadsheet has the following estimates and their initial values:

Assignment 5 - Part 1

The cash flow of a business is almost always different than the reported profits of the same business. While profits are an important long term measure of business success, if you don’t have the cash to pay the bills in the short term, you will be in very serious trouble. That is why it is necessary to do both profitability planning and cash flow planning.

For the first part of this assignment you will develop a cash budget for the new product discussed above. You will use the following assumptions:

· Assume that initially there is no cash available for the project (i.e. beginning cash balance of zero.)

· The present production facility has plenty of floor space to produce the new product, but there is specialized equipment needed to be purchased and installed. The purchase price and installation costs total $650,000. This amount needs to be paid the month before production begins (that is, in month -1).

· Production of the product will be in the month prior to the month in which it is sold. That means that production starts in month 0 and the number of units produced will be the amount sold in the next month (month 1).

· Raw materials for production must be purchased the month prior to the month in which the goods are produced. That is, the raw materials for the first production run must be purchased in month -1 since production starts in month 0.

· Labor to produce the goods is paid the same month the goods are produced.

· Advertising is always prepaid. Advertising for the good will start in month 1, the first month of sales. That means that the advertising for month 1 must be paid in month 0.

· The sales costs are paid the same month they are incurred.

· Sales revenue is collected the month after the sale.

I am providing a template for you to use to develop the cash flow worksheet. There are three tabs on the spreadsheet. The first tab contains the problem parameters and a summary of the calculated income for the year and the ending cash balance. The second sheet contains the detailed calculations for each month of the first year for the profitability budget. This budget has already been completed. Be sure that you totally understand how these numbers have been derived. Note that this profitability budget has been developed so that changing any parameter on the first page results in the calculations on the second sheet being changed. The third tab is the cash budget that you must complete. It must also be parameterized so that any changes to the parameters in the first sheet will be automatically calculated in the cash budget.

Once you have developed your spreadsheet, you need to analyze your results. First you will answer questions concerning the results of your calculations and then you will do some sensitivity analysis. The answers to the questions and the sensitivity analysis should be presented in an MSWord document that presents your results and also provides analysis of the situation.

The questions about the cash budget using the initial figures follow:

Question 1: Looking only at the profitability budget does producing and selling this product look like a good business decision?

Question 2: Now when you include the cash budget, comment on the soundness of producing and selling this new good. Is this still a profitable venture? What is the major concern and how could a company handle it?

In the following you are asked to do sensitivity analysis using the spreadsheet you developed. Consider the calculations using the initial parameters as your baseline estimate. Following are the situations you should analyze:

Situation 1: The sales staff thinks that if we double the advertising budget ($100,000 per month instead of $50,000), then initial sales will increase by 15% (1,150 instead of 1,000) and that sales will then grow by 2% a month instead of 1% a month. How will this affect income and cash flow in the first year when compared to the baseline calculations? Would you recommend the change? What other factors should be considered? Explain in detail.

Situation 2: The production engineers think that they can do some of the production on existing machinery and hence could reduce the investment and installation of new machinery from $650,000 to $450,000. This will also decrease the cost of raw materials per unit from $450 per unit to $440 per unit. However, it will increase the labor cost per unit from $200 per unit to $212 per unit. This change will not affect any of the other costs, the units sold or the selling price per unit. Ignoring the suggested changes in situation 1 above (that is starting with the initial estimates) how will this affect net income and cash flow for the year when compared to the baseline calculations? Would you recommend the change? What other factors should be considered? Explain in detail.

Submit both your Excel workbook and your MSWord document electronically by submitting it in the appropriate assignment in Blackboard. You must also bring a printed copy of your assignment (only the MSWord document) to class on Thursday, October 16, 2014. I will be collecting them at the beginning of the class. Remember! Late submissions are not accepted for grading especially in this situation since I will be posting the correct answers after class.

1