Over the Rhine Brewery (ORB) is conducting a cash flow analysis for the coming year. The first quarter of the calendar year is typically the slowest time for ORB. For each month of the first quarter (January, February, and March), the estimate for the sales of beverages and food is $60,000 per month. Using the first quarter as the base period, ORB has analyzed historical data and has established quarterly multipliers for each of the other three quarters of the year to estimate monthly sales in those quarters. The multiplier for months in the first quarter is 1.0 because this is the base period. The other multipliers are 1.5 for the second quarter, 3.2 for the third quarter, and 2.8 for the fourth quarter. So, for example, the estimate for beverage and food sales in the month of November, since November is in the fourth quarter, would be (2.8)($60,000) = $168,000. ORB also sells merchandise such as hats and t-shirts. In a given month, revenue from merchandise is estimated to be about 8% of the sales from beverage and food. Material cost is 23% of total sales revenue and labor cost is 45% of total sales revenue. ORB pays $3,000 per month for rent and $1,300 per month for utilities. ORB borrowed money through a small-business loan during its startup and currently makes $1,200 monthly loan payments (70%

of the payment is interest and 30% is principal). The interest of the loan is tax deductible, but the principal portion of the loan is not. ORB’s tax rate is 23.6%. Assume ORB begins with a zero cash balance at the beginning of the year.

a. Build an influence diagram for ORB’s monthly cash flow problem for the coming year.

b. Use the influence diagram from part

(a) to construct a spreadsheet model. What is the ending total cash balance at the end of the year?

c. ORB would like to increase the sales of merchandise. Use Goal Seek to find the percentage of food and beverage sales that revenue from merchandise would have to be to provide an end-of-year cash balance of $400,000.