How to prepare a cash budget managerial accounting

ACCT 241 - 04: Principles of Managerial Accounting

Fall 2014 Semester Project: Dream Big, Inc. Budget

Project Objectives:

Students will prepare a Comprehensive Master Budget and a Budgeted Income Statement for Dream Big, Inc. for the second quarter ending June 30. This includes:

· Sales Budget;

· Production Budget;

· Raw Materials Purchases Budget;

· Direct Labor Budget;

· Manufacturing Overhead Cost Budget;

· Budgeted Cost of Goods Sold;

· Selling and Administrative Expenses Budget;

· Cash Receipts Budget;

· Cash Disbursements Budget;

· Cash Budget; and

· Budgeted Income Statement.

Students will also perform a CVP analysis to determine the break-even point and complete a sensitivity analysis to a sales price change and a change in the cost of materials and labor.

Project Requirements:

I. (30 points) Complete the Dream Big, Inc. Case on an individual basis using the Excel template found in the Project Folder on Blackboard. Enter your answer in the Worksheet tab labeled “Part I Answer.” Use formulas in the cells for calculations (i.e., do not type in a total that you have determined with your calculator.)

II. (10 points) Based on the base case, perform a CVP Analysis to determine the break-even point in units and sales dollars, margin of safety, and operating leverage factor for Dream Big, Inc. for the Quarter Ended June 30. You must show all calculations. Enter your response on the Project Excel file in the Worksheet tab labeled “Part II Answer.”

III. ( 10 points) Determine Sales Revenue & Net Income for the 3-month period ending June 30, and the ending Cash Balance as of June 30 assuming the unit Sales Price is expected to increase (effective April 1) to $22, and the labor rate is expected to increase to $11 per hour (also effective April 1). Reconcile the changes.

To solve this, use the Worksheets provided in the Project Excel file (i.e., the worksheet tabs titled “If SP = $22,” “If DL = $11,” “If SP =$22; and DL = $11”). In each Worksheet, start with the “Base Case” data (what you entered as “Part I Answer”) and then change the budget and/or financial statement data affected by the change to the Sales Price and/or the change to the Direct Labor costs. For example, changing the unit sale price will cause changes to the sales budget, cash receipts budget, and summary cash budget… this will lead to a new Net Income and a new June 30 ending Cash Balance. With changes to Sales and/or Labor costs, items such as inventory values will also change. Complete the chart found on the Project Excel file in the Worksheet labeled “Part III Answer.”

Note: If you have used formulas for all calculations in the Excel spreadsheet, answering this question will be straightforward. When completing the Excel Worksheets, check your work and make sure that the data in your schedules foots (that is, adds up) and cross-foots (that is, adds across) in an accurate and meaningful way. Points will be deducted if the numbers don’t add up.

ACCT 241-04: Principles of Managerial Accounting

Fall 2014 Semester Project: Dream Big, Inc. Case

You have just been hired as a new management trainee by Dream Big, Inc. a small company that manufactures and sells frames made from bamboo. Bamboo is viewed as a sustainable wood and you are excited to have the opportunity to work for a firm which makes “green” products. In the past, the company has done very little in the way of budgeting and at certain times of the year has experienced a shortage of cash. Because you have been well trained in budgeting through your coursework in Managerial Accounting at Kogod School of Business, the management of Dream Big is very pleased that you are on board! You have been asked to prepare a comprehensive budget for the upcoming second quarter in order to show management the benefits that can be gained from an integrated budgeting program. To this end, you have worked with accounting and other areas to gather the information assembled below. You will also be preparing some analyses of the budget data, including Break-Even Analysis and some “What if” Scenarios to help the Dream Big management team better understand their costs so that they are positioned to make better decisions for the company.

Dream Big, Inc. manufactures several styles of bamboo picture frames, but all are sold for the same price -- $20 each. Each frame requires 4 linear feet of bamboo, which costs $1.50 per foot. Each frame takes approximately 30 minutes to build, and the labor rate averages $10.00 per hour.

Dream Big has the following inventory policies:

Ending finished goods inventory should be 40 percent of the next month's sales.

Ending raw materials inventory should be 30 percent of the next month's production.

Expected unit sales (frames) for the upcoming months follow:

March 275

April 250

May 300

June 400

July 375

August 425

Variable manufacturing overhead is incurred at a rate of $0.25 per unit produced. Annual fixed manufacturing overhead is estimated to be $7,200 ($600 per month) for an expected production of 4,000 units for the year. Selling and administrative expenses are estimated at $650 per month plus $0.60 per unit sold.

Dream Big, Inc., had $9,800 cash on hand at April 1. Of its sales, 70 percent is in cash. Of the credit sales, 40 percent is collected during the month of the sale, and 60 percent is collected during the month following the sale.

Of direct material purchases, 80 percent is paid for during the month purchased and 20 percent is paid in the following month. Raw materials purchases for March 1 totaled $2,000. All other operating costs are paid during the month incurred. Monthly fixed manufacturing overhead includes $150 in depreciation.

Required Part I:

Use the Excel template found on Blackboard under Course Documents, Project to prepare a master budget for the three-month period ending June 30. Enter your response in the Worksheet labeled “Part I Answer.” In this problem, income taxes will be ignored. Also, variable product costs will comprise Cost of Goods Sold (COGS) on the Income Statement and Inventory on the Balance Sheet.

1. Prepare the following detailed budgeted for Dream Big, Inc., for the second quarter (April, May, and June):

a. Sales budget

b. Production budget

c. Raw Materials Purchases budget

d. Direct Labor budget

e. Manufacturing Overhead Cost budget

f. Budgeted Cost of Goods Sold

g. Selling and Administrative Expenses budget

2. Prepare a Budgeted Income Statement for the three-month period ending June 30.

3. Prepare a Cash Budget. Include each month (April–June) as well as quarter 2 totals:

a. Cash Receipts budget,

b. Cash Payments budget.

c. Cash budget.