homework

BUS 503 Accounting Information for Decision-Making

Computer Assignment #2 - Budgeting

Fall 2014

Objectives

The objectives of this assignment are to: a) enhance your understanding of the budgeting process; b) develop your skills in performing a sensitivity analysis utilizing computer spreadsheets; and c) improve your ability to interpret the results and communicate them in writing and verbally.

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 65% 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 15%, rather than 10%, of the credit sales are collected during the month of sale AND 50%, 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 $230,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.

BUS 503 Accounting Information For Decision-Making

Computer Assignment #2

Fall 2014

Page 2 of 2

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.

Grading

This assignment is worth 15% of your course grade. Your grade will be determined as follows:

Business memorandum:

-Content and development 30 points

-Organization 10 points

-Language use and format 10 points

Spreadsheets 50 points

Total possible points 100 points

Due dates

Your business memorandum and spreadsheets are due at 12.35 p.m. on Tuesday, October 7, 2014. On that day please bring one paper copy to class for the professor. No late work will be accepted.