Acctg 202
Project 3
This template is to be completed following the instructions detailed in the "Project 3 - Instructions" file. Do not change the
lines or spacing in this spreadsheet. All input areas are outlined in blue. Do not enter data in any other cells.
Statement of Responsibility
We, the students listed below, have all worked together on all parts of the project hereby submitted. All work has been
performed exclusively by us and only us. We have not compared answers or discussed this project with anyone other
than our instructor or GA's. We are aware of the penalty for academic dishonesty stated in the course syllabus.
Last three digits of
Student ID
List names below:
Total the numbers above
Enter the last 3 digits of your total for (A). If > 500, stop here and enter this amount below for hours to be worked.
If total is < 500, add 500 to the number and enter in ( C). Enter this amount for the number of hours to be worked.
Part A - You MUST use formulas for subtotals and totals
(A)
(B)
(C)
Arachne, Inc.
Budgeted Income Statement
For the Year Ended December 31, 2017
Hours To Be Worked from (B) or ( C) above
Revenue
Variable expenses
Total variable expenses
Contribution margin
Fixed expenses
Total fixed expenses
Operating income
1. How many hours of work will it take to break even?
2. What is your margin of safety in hours?
3. What is the percentage increase in total contribution margin if hours worked increase 20%?
4.Calculate the the company's operating leverage factor.
5. What is the percentage increase in operating income if hours to be worked increase 20%?
Per hour
Total
Per hour
Total
Part B - You MUST use formulas for totals and subtotals
Arachne, Inc.
Cash Budget
For the Year Ended December 31, 2017
Beginning cash balance
Cash receipts from
$
Total cash available
Cash payments for
Total budgeted disbursements
Ending Cash Balance
Part C (see instruction file page 2 for sample)
You MUST use formulas for subtotals and totals
Arachne, Inc.
Budgeted Balance Sheet
December 31, 2017
Assets:
Current Assets (List in order of liquidity)
Total Current Assets
Long-term Assets
Total Long-term Assets
Total Assets
Liabilities:
Current Liabilities
Total Current Liabilities
Total Liabilities
Shareholder's equity:
Total Shareholder's Equity
Total Liabilities and Shareholder's Equity
1. Will you be able to repay your parents for the computer and software early on January 1, 2018?
2. Will you be able to repay your parents for the computer and software on the due date of January 31, 2018?
Part D - Details described the Instruction File
5,000
1. What is your contribution margin per hour now?
2. What is your break-even point now?
3. What is your total revenue now?
4. What are your total variable costs now?
5. What are your total fixed costs now?
6. What is your total operating income now?
7. In order to maximize profits, what price per hour should you charge? ($45 or $50 per hour)
Part E - Details described in Instruction File
1. What kind of costs are the fees from Virtual Office Company? ( fixed, variable, or mixed)
2. Look at your original budgeted income statement. Which of the expenses are relevant to this outsourcing decision?
3. If you outsource to Virtual Office Company, how much will they charge you in total for the year?
4. Should you outsource your assistant? (Yes, No, Doesn't Matter)
5. What qualitative factors should you consider in making your outsourcing decision?
Part F - Details described in instruction file (Be sure to label variances as F or U) Do not enter answers in shaded cells.
1. Calculate the volume variance for sales revenue (show your work):
2. Calculate the flexible budget variance for sales revenue (show your work):
Part G - Details described in instruction file
1. Calculate net annual cash inflow expected if the server is purchased. (show your work)
Net Annual Cash Inflow
2. a) First, calculate the present value of the future cash flows (show your work):
2. b) Next, calculate the Net Present Value (NPV) (show your work):
Net Present Value (NPV)
3.a) Should you buy the server? (Yes or No)
3.b) Why or why not?
ASSETS
=
LIABILITIES
+
STOCKHOLDER'S EQUITY
Record the expected adjusting entries in the T-Accounts for the following:
Accounts
Common
Cash
Payable
Stock
5,000
5,000
Accounts
Receivable
Wages
Payable
Office
Supplies
Notes
Payable
Retained
Earnings
Dividends
4,800
Prepaid
Insurance
Equipment
4,800
Accumulated
Depreciation
KHOLDER'S EQUITY
Sales
Wages
Expense
Insurance
Expense
Advertising
Expense
Supplies
Expense
Depreciation
Expense
Accounting 202
Project 3
Instructions: This document contains the instructions and information you will use to complete Project 3.
Accompanying this document is an Excel worksheet that contains the template for Project 3.
Background: You decide to earn some money to finance your education by starting a business that designs Web
pages. You will begin business on January 1, 2017, and run the business for the next 12 months. On your Excel
spreadsheet, calculate the number of budgeted hours you will work for clients during the year by adding the last
three digits of your student ID numbers of all group members. Only use the last three digits of this sum. If the
number is less than 500, add 500 to the number. (If the last three digits of your ID number happen to be 000,
use the first three digits of your ID number instead.) This number will be the expected number of billable hours
to be worked in the coming budget year. You are going to prepare a budgeted income statement, cash budget,
and budgeted balance sheet for the coming year based on the assumptions listed below.
BUDGET ASSUMPTIONS FOR THE COMING YEAR:
1. Before the business begins, you will take $5,000 cash from your savings account to invest in the
business. You will protect yourself by organizing as a corporation, with yourself as the only stockholder.
2. Before the business begins, it will buy a computer, printer, and software package that together cost
$4,800. Your parents have agreed to lend the business $4,800 cash so that the business can pay for the
equipment and software. The business plans to repay your parents on January 31, 2018. Your parents do not
expect any interest but require you to sign a promissory note.
The beginning balance sheet for your business is presented on the following page:
1
Arachne, Inc.
Balance Sheet
January 1, 2017
Assets
Current Assets
Cash
Long-term Assets
Equipment
Less Accumulated Depreciation
$5,000
4,800
0
4,800
$9,800
Total Assets
Liabilities and Stockholder’s Equity
Current Liabilities
Long-term Liabilities
Note Payable
Total Liabilities
Stockholder’s Equity
Common Stock
Retained Earnings
Total Liabilities
Total Liabilities and Stockholder’s Equity
$0
4,800
$4,800
$5,000
0
$5,000
$9,800
3. Starting January 1, 2017, the business will charge $50 per hour for design work and bill clients as each job is
completed. You expect that by December 31, 2017, the end of the fiscal year, 80% of clients will have paid the
business in cash. You expect that the rest will pay by January 31, 2018.
4. The business will hire an assistant to help with general office work. The assistant will earn $18 per hour and
will work exactly the same hours that you work. The assistant will be paid for each month's work on the fifth
day of the month following the month when the work is done. For the sake of simplicity, treat the assistant as
an independent contractor rather than an employee. This means that you do not have to worry about
withholding or matching payroll taxes. Assume that hours worked in December, 2017 are expected to total 10%
(round to nearest whole hour) of the total hours for the year.
5. The business will purchase office supplies for cash of $2,500 in January, 2017
6. On February 1, the business will pay cash of $2,160 for a one-year liability insurance policy.
7. Since this is a new business, you think that it is important to advertise. The business will buy $450 of
newspaper advertising each month. All payments for advertising costs will be made in the following
month. (For example, January advertising expense will be paid for in February).
8. A dividend of $3,000 will be declared and paid before year end.
Record SUMMARY budgeted transactions for the entire year based on the above assumptions in the TAccounts below. Round all amounts to the nearest whole dollar. The T-Accounts will not be turned in, but
should be completed to assist you in completing the budgeted financial statements.
2
ASSETS
=
LIABILITIES
+
STOCKHOLDER’S EQUITY
Record the expected adjusting entries in the T-Accounts for the following:
Cash
Accounts Payable
Common Stock
5000
5000
Sales
Wages Expense
Accounts
Receivable
Retained
Earnings
Wages Payable
Insurance
Expense
Advertising
Expense
Office Supplies
Notes Payable
4800
Dividends
Supplies Expense
Prepaid
Insurance
Depreciation
Expense
Equipment
4800
Accumulated
Depreciation
3
A1) It is expected that the equipment and software will be used for four years, after which time they will be
worthless. The business uses the straight-line method of depreciation.
A2) It is expected that office supplies will be used up at the rate of $2.00 per hour worked, with the remaining
amount still on hand at year-end.
A3) Record the insurance that has been used up during the year.
Instructions for Excel Worksheet:
Part A: In the Excel spreadsheet, complete the Budgeted Income Statement (you may not need to fill in all lines
provided) and questions 1. – 5.
Part B: Review chapter 9 and your notes from class. In the Excel spreadsheet, complete the Cash Budget (you
may not need to fill in all lines provided).
Part C: Review Chapter 9 and your notes from class. In the Excel spreadsheet, complete the Budgeted Balance
Sheet in good form. Complete questions 1. and 2. in this section.
Part D: Review Chapter 7 and 10 and your notes form class. Assume that you have now decided to charge
clients $45 per hour instead of $50 per hour. Demand for your services will increase 10% over the hours you
used in your original budget. All the other assumptions above remain the same. Answer questions 1. – 7. in the
Excel spreadsheet.
Part E: Review Chapters 7 and 8 and your notes from class. Ignore the information used in Part D above and go
back to the original assumptions on page 1. Now assume that Virtual Office Company could replace your
assistant with their own office service. They will charge you $700 per month plus 10% of your revenue. Answer
questions 1. – 5. in the Excel spreadsheet.
Part F: Review Chapter 10 and your notes from class. Ignore the information provided in Part D and E above
and go back to the original assumptions on page 1. Your original budgeted income statement is your “master”
budget. Assume now that actual hours worked were 500 and the actual price charged per hour was $53.
Answer questions 1. and 2. in the Excel spreadsheet.
Part G: Review Chapter 12 and your notes from class. In the Excel spreadsheet, answer questions 1. – 3.
1. You are considering the purchase of a Web server that costs $16,000. Your parents have offered to provide
another interest-free loan. You plan to use this server for five years and then sell it for the expected residual
value of $4,000. This server will allow your clients to post their Web pages and is expected to generate annual
incremental operating income equal to 25% of the operating income from Part A. (Round to the nearest dollar.)
(The expected incremental operating income includes the depreciation expense on the new server only.) In the
Excel spreadsheet, calculate the annual net cash inflow expected from the server. Show your work.
2. Assuming that every year for the next five years that you will use the server, you will earn the same net cash
inflows that you calculated above. Using the tables from your textbook and an 14% required rate of return,
calculate the present value of the future cash inflows. Next, calculate the net present value of the proposed
investment. Label your calculations clearly.
3. Based on your calculation of net present value, should you buy the server?
4

Purchase answer to see full
attachment