Please just pick any two of the 4 questions and solve it for me. Thanks!

Please just pick any two of the 4 questions and solve it for me. Thanks!

Attachment 1

Attachment 2

Attachment 3

Attachment 4

ATTACHMENT PREVIEW

Excel Problem3:
Cash Budget
Suppose Sprint Incorporated, the long-distance telephone company, is preparing its
cash budget for 2011. The company ended 2010 with \$126 million in cash, and top
management foresees the need for a cash balance of at least \$125 million to pay all
bills as they come due in 2011.
Collections from customers are expected to total \$11,813,000,000 during 2011, and
payments for the cost of services and products should reach \$6,166,000,000.
Operating expense payments are budgeted at \$2,744,000,000.
During 2011, Sprint expects to invest \$2,826,000,000 in new equipment, \$275 million
in the company’s cellular division, and to sell older assets for \$116 million. Debt
payments scheduled for 2011 will total \$597 million. The company forecasts net
income of \$890 million for 2011 and plans to pay \$338 million to its owners. Assume
a 40% income tax rate.
Required:
A. Using Excel, prepare Sprint’s cash budget for 2011. Be sure to use proper form for
your work with appropriate headings and explanations (I suggest you find a suitable
cash budget format and use it).
B. Will the budgeted level of cash receipts leave Sprint with the desired ending cash
balance of \$125 million, or will the company need additional financing?

ATTACHMENT PREVIEW

Excel Problem 6
Objective: Create pivot tables for what-if analysis.
Required
Read the article “Make Excel an Instant Know-It-All” by Roberta Ann Jones in the
March 2004 issue of
Journal of Accountancy

a.
Follow the instructions in the article to create a spreadsheet with pivot tables.
b.
Create a report that shows sales by month for each salesperson.
c.
Assume that Brown and David are in sales group 1 and that the other three
salespeople are in sales group 2. Create a report that shows monthly sales for each
group.
Be sure to use headings and appropriate explanations.

ATTACHMENT PREVIEW

Excel Project 7.
See the article “Dial a Forecast,” by James A. Weisel, in the December 2006 issue of
the
Journal of Accountancy
. The
Journal of Accountancy
is available in print or
online at the AICPA’s Web site:
www.aicpa.org

Required
a.
Create a 12-month cash flow budget in Excel using the following assumptions:

Initial sales of \$5 million, with forecasted monthly growth of 1%

40% of each month’s sales for cash; 30% collected the following
month; 20% collected 2 months later; 8% collected 3 months later; and
2% never collected

Initial cash balance of \$350,000
b.
Add a “spinner” to your spreadsheet that will enable you to easily change forecasted
monthly sales growth to range from 0.5% to 1.5% in increments of 0.1%.
c.
Add a scroll bar to your spreadsheet that will let you modify the amount of initial
sales to vary from \$4 million to \$6 million in increments of \$100,000.
d.
Design appropriate data entry and processing controls to ensure spreadsheet accuracy.

ATTACHMENT PREVIEW

Excel Project. 8
Required
Use Excel regression tools to analyze and forecast future sales.
The article “Forecasting with Excel,” by James A. Weisel in the February 2009 issue
of the
Journal of Accountancy
(available at
www.aicpa.org
) explains how to perform
the following tasks using either Excel 2003 or Excel 2007.
a.
unit sales:
E-mails
Clicks
Unit Sales
150000
100
12000
155000
105
12500
125000
75
10000
130000
150
14000
135000
125
12500
120000
100
10000
125000
125
10900
130000
135
11500
130000
95
10500
100000
75
10750
110000
100
10000
100000
80
9500
140000
130
13500
120000
110
11500
E-mails
Clicks
Unit Sales
130000
125
13000
100000
85
12000
110000
100
9000
120000
135
10000
130000
140
13500
140000
125
13400
130000
115
12750
120000
105
12750
100000
95
10000
130000
145
9000
150000
150
15000
140000
120
12000
125000
100
13500
110000
95
11000
130000
140
13500

Unlock Solution Unlocking…

b.
Create a scattergraph to illustrate the relationship between targeted e-mails and unit
sales. Display the regression equation and the R2 between the two variables on the
chart.
c.
Create a scattergraph to illustrate the relationship between click ads and unit sales.
Display the regression equation and the R2 between the two variables on the chart.
d.
Which variable (targeted e-mails or click ads) has the greater influence on unit sales?
How do you know?
e.
Use the “=Forecast” function on display the forecasted sales for 200,000 targeted e-
mails and for 200 click ads.