USA: +1(669)289-8683 
Sign in
Prepare a cash budget for a merchandising firm.
• Analyze several options to improve cash flow.
• Compare cash flow and net income trends.
• Modify the worksheet to include an additional time period.
• Create a line chart comparing cash receipts and disbursements.
On January 1, Candy Cup, Inc. begins business. The company has $6,000 cash on hand and is
attempting to project cash receipts and disbursements through April 30. On May 1, a note
payable of $4,000 will be due. This amount was borrowed on January 1 to carry the company
through its first four months of operations. The unit purchase cost of the company’s single product, a Candy Cup, is $6. The unit sales price is $14.50. Projected purchases and sales in units for the first four months are:
Purchases Sales
January 1,200 600
February 1,300 1,200
March 1,400 1,400
April 1,700 1,500
Sales terms call for a 2% discount if paid within the same month that the sale occurred. It is expected that 50% of the billings will be collected within the discount period, 25% by the end of the month after purchase, 20% in the following month, and 5% will be uncollectible.
All purchases are payable within 15 days. Thus, approximately 50% of the purchases in a month are due and payable in the next month.
Total fixed marketing and administrative expenses for each month include cash expenses of $2,500 and depreciation on equipment of $1,000. Variable marketing and administrative expenses total $3 per unit sold. All marketing and administrative expenses are paid as incurred.
REQUIRED
1. You have been asked to prepare a cash budget for the next four months to see if the loan
can be repaid. Review the worksheet CASHBUD that follows these requirements. The
problem data have already been entered in the Data Section of the worksheet.
2. Start the spreadsheet program and open the file CASHBUD from the www.thomsonedu.com/accounting/smith website. Enter the eight formulas where indicated on the worksheet. Enter your name in cell A1. Save the file as CASHBUD2. worksheet when done. Also print your formulas. Check figure: Ending cash balance for
April (cell E54), $3,614.
3. Can the $4,000 note be repaid on May 1? Explain.
4. How do the other months look? Are any problems coming? Explain.
WHAT-IF ANALYSIS
The following four suggestions have been made to improve the company’s cash position. Evaluate the effect on cash flow for each of the four suggestions. After evaluating each suggestion, enter the projected cash balances in the spaces provided. Consider each suggestion separately. Reset cells to their initial values after each new suggestion.
a. Seek agreement with suppliers to extend the credit period from 15 to 30 days. This would mean that all current monthly purchases would be paid for in the following month.
b. Raise the unit price from $14.50 to $15.50. A price increase will reduce unit sales by 10% each month. Purchases will also be reduced by 10%.
c. Put the companys two salespeople on straight commission. This would reduce fixed marketing and administrative costs to $750 per month and raise variable marketing and administrative costs to $4 per unit.
d. Increase the cash discount from 2% to 4%. It is anticipated that this would increase the percentage of customers paying within the discount period to 85%, and those paying the month after the discount period would drop to 8%. Five percent would pay in the following month and 2% would still be uncollectible.
PROJECTED ENDING CASH BALANCES
January February March April
a. $ 5963 $ 3364 $ 4901 $ 8714
b. $ 2741 $ 546 $ 2405 $ 5969
c. $ 3513 $ 1164 $ 2751 $ 5914
d. $ 5347 $ 6642 $ 10233 $ 14175
Question: What are your recommendations for Candy Cup, Inc.? Consider potential impact on profits as well as cash balances.
Do:
Reset cells to their initial values. Candy Cup, Inc. is considering undertaking a $1,200 per month advertising campaign to promote the Candy Cup as an exclusive, high-fashion item for the home. What price (dollars and cents) would Candy have to charge for each cup to be able to pay for the campaign, pay back the $4,000 note, and have $5,000 left over at the end of April?
Sales Price
CHART ANALYSIS
Reset the Data Section to its initial values and click the Chart sheet tab. A chart appears that plots the relationship between monthly unit sales, unit purchases, and ending cash balance. What happens to ending cash balance if January purchases are reduced to 1,100 units? Enter 1,100 in cell F21 on the Chart sheet and then check the chart. What about 1,000 units? 900 units?800 units? Enter your observations below. Is this a possible solution to the company’s potential cash problem?
TICKLERS
Worksheet. Extend the CASHBUD2 worksheet to include the month of May. May sales are expected to be 1,500 units and purchases are to be 1,400 units. Also show the repayment of the loan on May 1. Set the print commands to have the worksheet print on a single page. Save the
completed worksheet as CASHBUDT. Print the new budget.
Chart. Use CASHBUD2 to create a line chart plotting total monthly receipts and monthly disbursements over the four-month period. Complete the Chart Tickler Table and use it as a basis for preparing the chart. Put your name somewhere on the chart. Save the file again as CASHBUD2. Print the chart.
