Ques_1

Kelly’s Boutique has several questions for you that Excel can help
answer. Kelly is planning for the future and would like you to prepare a
present value analysis. Using the file ch7-04 complete a present value
analysis for the following situations. Save the file as ch7-04_student_name
(replacing student_name with your name). Print both a Value view and Formula
view of this completed worksheet. Kelly would like to know the following:

a. How much she would have to pay at the end of each year,
assuming a 7 percent rate of return, to yield $100,000 at the end of 10
years.

b. How much she would have at the end of 10 years if she invested
$65,000 today, earning 5 percent per year.

c. How much she would have at the end of 10 years if she invested
$6,325 at the end of each year, earning 9 percent per year.

d. How much she would have to invest today to have $154,324 in 10
years, earning 12 percent per year.

Ques_2

Kelly has a very fluctuating workforce based on
seasonal demand. She’s ranged from having 10 employees in one month to 32
employees in another month. Some employees are paid a salary, others are paid
hourly. She would like to know more about how these costs behave. Use the
file ch7-05 to complete a cost prediction worksheet. Save the file as
ch7-05_student_name (replacing student_name with your name). The worksheet
should do the following:

a. Calculate variable cost per employee, fixed costs, and a
prediction of payroll cost with 22 employees using the Hi-Lo method.

b. Calculate variable cost per employee, fixed costs, and a
prediction of payroll cost with 22 employees using the Least
Squares/Regression method.

c. Display a chart of payroll/employees with a trend line. (Be
sure to modify each axis so your scatter diagram is better displayed, as you
did earlier in this chapter)

Ques_3

During a recent year Kelly’s Boutique had sales on account of
$6,000,000, collections of $5,800,000, write-offs of $45,000, a beginning
balance in accounts receivable of $500,000, and a beginning balance in the
allowance for uncollectible accounts of $37,000. At year end, $580,000 of
accounts receivable were current, $39,000 were 0-30 days past due, $18,000
were 31-60 days past due, $10,000 were 61-90 days past due, and $8,000 were
over 90 days past due. The company believes .6 percent of sales will not be
collected. They also have experience that suggests that 1 percent of all
current receivables, 8 percent of receivables 0-30 days past due, 20 percent
of receivables 31-60 days past due, 25 percent of receivables 61-90 days past
due, and 50 percent of receivables over 90 days179180past due will not be collected. Using
the file ch7-06, complete the allowance for uncollectible accounts analysis
for both standard methods. Save the file as ch7- 06_student_name (replacing
student_name with your name). Print both a Value view and Formula view of
this completed worksheet.

Ques_1

Kelly’s Boutique has several questions for you that Excel can help
answer. Kelly is planning for the future and would like you to prepare a
present value analysis. Using the file ch7-04 complete a present value
analysis for the following situations. Save the file as ch7-04_student_name
(replacing student_name with your name). Print both a Value view and Formula
view of this completed worksheet. Kelly would like to know the following:

a. How much she would have to pay at the end of each year,
assuming a 7 percent rate of return, to yield $100,000 at the end of 10
years.

b. How much she would have at the end of 10 years if she invested
$65,000 today, earning 5 percent per year.

c. How much she would have at the end of 10 years if she invested
$6,325 at the end of each year, earning 9 percent per year.

d. How much she would have to invest today to have $154,324 in 10
years, earning 12 percent per year.

Ques_2

Kelly has a very fluctuating workforce based on
seasonal demand. She’s ranged from having 10 employees in one month to 32
employees in another month. Some employees are paid a salary, others are paid
hourly. She would like to know more about how these costs behave. Use the
file ch7-05 to complete a cost prediction worksheet. Save the file as
ch7-05_student_name (replacing student_name with your name). The worksheet
should do the following:

a. Calculate variable cost per employee, fixed costs, and a
prediction of payroll cost with 22 employees using the Hi-Lo method.

b. Calculate variable cost per employee, fixed costs, and a
prediction of payroll cost with 22 employees using the Least
Squares/Regression method.

c. Display a chart of payroll/employees with a trend line. (Be
sure to modify each axis so your scatter diagram is better displayed, as you
did earlier in this chapter)

Ques_3

During a recent year Kelly’s Boutique had sales on account of
$6,000,000, collections of $5,800,000, write-offs of $45,000, a beginning
balance in accounts receivable of $500,000, and a beginning balance in the
allowance for uncollectible accounts of $37,000. At year end, $580,000 of
accounts receivable were current, $39,000 were 0-30 days past due, $18,000
were 31-60 days past due, $10,000 were 61-90 days past due, and $8,000 were
over 90 days past due. The company believes .6 percent of sales will not be
collected. They also have experience that suggests that 1 percent of all
current receivables, 8 percent of receivables 0-30 days past due, 20 percent
of receivables 31-60 days past due, 25 percent of receivables 61-90 days past
due, and 50 percent of receivables over 90 days179180past due will not be collected. Using
the file ch7-06, complete the allowance for uncollectible accounts analysis
for both standard methods. Save the file as ch7- 06_student_name (replacing
student_name with your name). Print both a Value view and Formula view of
this completed worksheet.