Accounting Information for Decision Making

Tasks

This is an individual assignment and must be your own work.  You are to complete problem #9-42 on pages 398-401 in your textbook, perform a sensitivity analysis using data provided in this handout, and prepare a business memorandum on budgeting. 

Included below are the detail requirements for:  1) preparing the master budget for the original data case (as presented in the textbook problem), 2) performing a sensitivity analysis, 3) developing the spreadsheets, 

4) writing the business memorandum, and 5) organizing your work. 

Original data case

The information given in the problem is the original data case.  You are to prepare the various components of the master budget including the budgeted income statement, statement of retained earnings, and the balance sheet.  

Sensitivity analysis

You will perform a sensitivity analysis by changing assumptions and then observing the impact on your master budget schedules and financial statements. In each of the three independent cases that follow, you are to make the required changes in the data, observe the results, and explain them in your business memorandum.

Case 1:  What would happen if credit sales were 70% of total sales, rather than 75% and inventory at end of each month equal to 40% of next month’s projected cost of goods sold?

Case 2:   What would happen if 12%, rather than 10%, of the credit sales are collected during the month of sale AND 45%, rather than 40%, of each month’s purchases are paid during the month of purchase? 

Case 3:   What would happen if the equipment purchases required a $225,000 investment, rather than $250,000?

Excel spreadsheets

Your spreadsheets for this budget assignment will be parameter-driven only to the extent needed to accommodate the three cases above.  For example, make sure that you include in you parameter section the credit sales percent,  the percent of sales collected in the month of sale, the percent of sales collected during the following month, etc.  For all other data NOT affected by the three cases above, you will enter them directly into the various schedules, rather than being included in the parameters section.

Also, where a schedule utilizes data from another schedule, you will need to reference those data fields, rather than entering them directly. For example, the summary cash budget will reference the cash receipts data from the cash receipts schedule, rather than entering the data directly.  Your spreadsheet printouts must include grid lines and row/column labels. 

Business memorandum

Prepare a business memorandum for Beth Davies-Lowry, the President of Global Electronics Company.  Your memorandum will be 1 page, single-spaced, font size 12 and must address the following:  

• Scope and objective of the budget you prepared
• Significance of the sensitivity analysis and your interpretation of the results
• What management should do if there are major changes in the assumptions upon which the budget is based.  These changes may relate to the economy, competition, government regulations, etc.
• What is your recommendation

Organization

Your completed assignment will be organized as follows:

• Business memorandum
• Spreadsheet in normal view based on the original data case and the three cases.

Please note that you must hand in paper copies of your spreadsheets for the three cases. You will hand in paper copies of your spreadsheets in the normal and a summary of the results for the three cases.