# Project #84958 - financial modeling

### Problem 1

XYZ Corporation is facing pressure from increasing costs for its products as well as demands from employees for more competitive wages. Management wants you to develop a cash budget model that it can use to analyze the impact of various assumptions on the projected month-end cash positions for June through September. (A cash budget model lays out the cash inflows and outflows for each month to arrive at numbers of interest to a business manager such as the projected month-end cash positions.)

You are building your model in the month of February. But because you are not given all information needed to do full cash flow projections for April and May, your full projections will start with the month of June. Do projections through the month of October.

XYZ’s sales are projected to be as follows: April: \$153,000, May: \$146,000, June: \$142,000, July: \$210,000, August: \$240,000, Sept.: \$225,000, Oct.: \$300,000. The company’s sales are 75% in cash and 25% on credit collected the next month. The company purchases its products at 50% of sales and pays half of it in the month after purchase and the other half in the month after that. Purchases are made one month prior to sales. The current wage rate is 20% of sales, lease payments are \$30,000 per month, quarterly interest payments (on existing long-term debt) of \$10,000 are due in June and September, a dividend payout of \$100,000 is scheduled for July, and tax payments in June and September are estimated to be \$9,000 and \$17,000, respectively.

Assume that at the end of May the company will have no short-term debt or investment and a cash balance of \$50,000. For your projections this is given input data that cannot be changed based on anything.

Also assume that all transactions take place on the last day of the month and the cash balance at the beginning of any month is the same as that at the end of the previous month.

Submit three printouts for the following 3 versions of the model using the templates provided. Although it should not be necessary, you can add additional properly labeled intermediate calculation lines to the templates. But you must do so below row 41 or to the right of column I. You cannot delete or move any of the labeled lines already shown in the templates and your model must calculate and show projections for all of these lines. The printout you submit must look like the template (i.e., do not include in your print out any rows or columns you add.)

For parts (b) and (c) also include a description of the logic you used in your model. The description must be concise and easy to follow, in the form of diagrams (such as decision trees) or concise algebraic equations, and cannot be verbal descriptions of your logic.

(a)   Create a model to project the month-end cash balances for June to Sept. and show what these balances will be under the above assumptions.

(b)  Management wants to maintain a minimum cash balance of \$15,000, using short-term debt if necessary. However, if at the end of any month the cash level is projected to be above that level, the excess should be used to pay off any outstanding short-term debt. The company will have to pay interest (payable monthly) at an annual rate of 8% on any short-term debt. Modify your model in (a) to incorporate these refinements. Include a description of the logic you used in your model. The description must be concise and easy to follow, in the form of diagrams (such as decision trees) or concise algebraic equations, and cannot be verbal descriptions of your logic.

Show your formulas for the following cells G30, G32, G34, G36 using the method discussed in the class. Also make sure that your print out shows the row and column numbers. Otherwise one cannot follow your formulas.

(c)   Management further wants to invest any cash balance above the target \$15,000 level earning 6% annual interest income (paid monthly). (Any excess cash should first be used to pay off outstanding short-term debt. Similarly any investment should be drawn down before incurring any short-term debt.) Modify your model in (b) to incorporate these refinements. Include a description of the logic you used in your model. The description must be concise and easy to follow, in the form of diagrams (such as decision trees) or concise algebraic equations, and cannot be verbal descriptions of your logic.

Show your formulas for the following cells G31, G34, G36, G38, G39 using the method discussed in the class. Also make sure that your print out shows the row and column numbers. Otherwise one cannot follow your formulas.

### Problem 2: Fruits and Veg

The data file for this problem (Fruits and Veg. data file) shows the sales of a wholesaler to a retailer of cases (in the quantity column) of various fruits, veggies and herbs.

Create a model to calculate the total revenue of the wholesaler given the sales quantities and prices as shown and incorporating the additional discount information given below. (The same discount rate applies to the entire sale quantity of each item.)

Make your model as versatile as possible. The wholesaler should be able to use your model to calculate the revenue for other sales quantities, prices, and discount rates as well. Do not “hard code” any data. All data and parameters for the problem should be organized in one or more tables so that the user can easily change them. Your model should look up these values from the table(s).

• For fruits, quantities under 5 cases get no discount, quantities from 5 to 20 cases get a 10% discount and quantities above 20 cases get a 15% discount.
• For Herbs, quantities under 10 cases get no discount, quantities from 10 cases to 15 cases get 3% discount, and quantities above 15 cases get a 6% discount.
• For vegetables, except for Asparagus, 5 cases and above get a 12% discount. Asparagus gets a discount of 12% only on sales of 20 cases or more.
• None of the discounts apply if the product is on sale this week. The sale price is 25% off the normal prices shown. This week’s sale items are Strawberry, Lettuce, and Tomatoes. Use an array to store the names of the items on sale for the week and your program should get the information on what items are on sale from this array.

Hints: Use IF or lookup functions wherever possible.

Show representative formulas in all cells where you use logical or lookup functions (using the method for displaying formulas discussed in the class). Also make sure that your print out shows the column and row numbers so that one can read and understand your formulas.

 Subject Business Due By (Pacific Time) 10/05/2015 09:00 pm
TutorRating
pallavi

Chat Now!

out of 1971 reviews
amosmm

Chat Now!

out of 766 reviews
PhyzKyd

Chat Now!

out of 1164 reviews
rajdeep77

Chat Now!

out of 721 reviews
sctys

Chat Now!

out of 1600 reviews

Chat Now!

out of 770 reviews
topnotcher

Chat Now!

out of 766 reviews
XXXIAO

Chat Now!

out of 680 reviews