Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Tiny Teapots is a small, local manufacturing company who produces and sells one style of teapot. Their product is sold to independent distribution centers and

Tiny Teapots is a small, local manufacturing company who produces and sells one style of teapot. Their product is sold to independent distribution centers and superstores.

All necessary information for Tiny Teapots budgeting process is provided in the Inputs tab in your Excel project template. Please read through all information provided in the inputs tab before beginning the budgeting process. A few pieces of additional information follow.

Tiny Teapots is in need of an open line of credit from a local bank to meet their cash needs, as well as to help improve their business functions. They provided the bank with their 2016 financial statements. After glancing through the financial statements, the banker realized that although Tiny Teapots had been a profitable company, their sales numbers are very volatile. The banker was concerned about the ability of Tiny Teapots to generate sufficient cash flow to pay off any debt that was extended. To alleviate the banks concern, Tiny Teapots has been asked to prepare a quarterly master budget for the upcoming year (2017), including financial statements.

The company must maintain a minimum cash balance of $1,000 each quarter. The cash balance at the beginning of January (2017) was $2,500. If approved, the local bank will allow the company to borrow up to $ 20,000 per quarter. (When preparing a budget, the minimum cash balance must ALWAYS be met even if it forecasts cash needs beyond the credit line available. If the available line of credit is insufficient, then alternative arrangements for financing must be made.)

All borrowing is done at the beginning of a quarter, and all repayments are made at the end of a quarter. Interest is paid for each quarter at the beginning of the next quarter, thus each quarters interest paid is based on the outstanding borrowings the prior quarter. The interest rate is 14% per year [Note that there are 4 quarters in a year]. If there are excess funds above the minimum $1,000 cash at the end of the quarter, these are used to repay the outstanding loans. When computing interest and repayment watch the number of quarters the money was borrowed.

The Company uses variable costing in its budgeted income statement and its balance sheet.

It is your responsibility to:

A. Start with the template that can be found in the Blackboard folder titled Excel Project. Save this file with your name in the title. Example) Excel Project Kayla Sander

B. The tab of the template labeled Input should serve as your input tab. This tab has already been filled in for you with all information necessary for budgeting. In the following ten tabs, you will need to fill in each cell with a light grey shading. Every cell you fill should be ENTIRELY FORMULA DRIVEN. This means that on the following tabs of your workbook, EVERY light grey cell should either reference another cell from the file or should be calculated using a formula which also references the necessary cells. *NO numbers can be typed into cells with the exception of what has already been filled in for you on the input tab!*

*Note: you must manually type in your new sales price value in the Goal Seek tab once you have run this process successfully. It is ok to type the numbers here! When grading, we will run the goal seek process to ensure it works with your file rather than just looking to see if you have the correct value listed on the tab.

C. When you have finished the project, you will need to UPLOAD YOUR COMPLETED FILE IN THE BLACKBOARD DROPBOX provided in the Excel Project folder. Please ensure that you have your name in the file that is uploaded.

HINTS:

The trickiest part of the assignment is computing the borrowings and repayments.You are expected to create logical formulas here. Remember, the borrowings are at the beginning of the quarter and repayments at the end. If you borrow in quarter 1 and repay in quarter 2, there will be 6 months of interest to be paid (beginning of January to end of June). Interest is paid the first of the next quarter.

Interest Expense on your Income Statement needs to not only include the interest paid during the year, but also the interest owed for the 4th quarter borrowings.

Use goal seek to find the selling price that would be needed to eliminate the need for any borrowings (make total borrowings for the year $0). Running this function will change many cells in your spreadsheet. In this case, you want to change the total annual borrowings on the Cash Budget to zero by changing the selling price on the input tab. Once you have successfully found your new selling price, manually enter the price into the GOAL SEEK worksheet, and change the selling price on the input tab back to the original $15.

To find goal seek: click on the Data tab and under Data Tools click on the What-If Analysis

If you begin to see ##### at any time when you are working, it is ok. These just show up when the column is not wide enough. If you simply expand the width of your column these will turn back into numbers.

As you work, check your file against the check figures provided below. This will cut down on wasted time trying to go back and find where you went wrong after completing the entire spreadsheet. If you are meeting the check figures, you should be on the right track!Check figures:

Total Sales: $423,300

Total Collections: $407,675

Total Production in Units: 28,652

4th Quarter Raw Materials Purchases Cost: $39,461

2nd Quarter Direct Labor Cost: $34,150

3rd Quarter Overhead Disbursements: $13,530

Total S&A Disbursements: $67,495

Accumulated Borrowings at End of 3rd Quarter: $11,842

4th Quarter Ending Cash Balance: $7,199

Net Income: $37,883

Total Assets: $168,467

Goal Seek Selling Price: $16.85

image text in transcribed

Sales Budget

Year

Quarter

Unit Sales

Unit Selling Price

Total Sales

2017

1st

3,520

$15

$52,800

2017

2nd

6,500

$15

$97,500

2017

3rd

8,700

$15

$130,500

2017

4th

9,500

$15

$142,500

28,220

$15

$423,300

2) Cash Collection Budget

Cash Collection Budget

1st Qtr

2nd Qtr

3rd Qtr

4th Qtr

Year

Current Quarter

$39,600

(52,800*75%)

73125

(97,500*75%)

$97,875

(130,500*75%)

$106,875

(142,500*75%)

Prior Quarter

$20,000

13200

(52,800*25%)

$24,375

(97,500*25%)

$32,625

(130,500*25%)

Total

$59,600

$86,325

$122,250

$139,500

$407,675

3) Production Budget

Production Budget

2017

2018

1st Qtr

2nd Qtr

3rd Qtr

4th Qtr

Year

1st Qtr

2nd Qtr

Sales in units

3520

6500

8700

9500

28220

6400

9200

Plus: Desired Finished Goods Ending Inventory (15% of next quarter's sales)

975

1305

1425

960

4665

1380

Total Needs

4495

7805

10125

10460

32885

7780

9200

Less Beginning Finished Goods Inventory

528

975

1305

1425

4233

960

1380

Units to be produced

3967

6830

8820

9035

28652

6820

4) Raw materials budget

Raw materials budget

2017

2018

1st Qtr

2nd Qtr

3rd Qtr

4th Qtr

Year

1st Qtr

2nd Qtr

Budgeted Production Units

3,967

6,830

8,820

9,035

28,652

6,820

Raw material needed per unit

1

1

1

1

1

1

Total Raw material needs for production

3,967

6,830

8,820

9,035

28,652

6,820

Add: Budgeted ending direct material Inventory (12% of next month production need)

820

1,058

1,084

818

$3,781

Total Direct material needed

4,787

7,888

9,904

9,853

32,433

Less: Beginning Direct material Inventory

476

820

1,058

1,084

$3,438

Budgeted Direct material required purchases units

4,311

7,069

8,846

8,769

28,994

Cost per unit

$4.5

$4.5

$4.5

$4.5

$4.5

Budgeted Purchases Cost

$19,398

$31,810

$39,806

$39,461

$130,475

The question needed help on is the remaining empty:

image text in transcribedimage text in transcribedimage text in transcribedimage text in transcribedimage text in transcribedimage text in transcribed

Budget Case Input Section Balance Sheet As of December 31, 2016 Qtr 4th Qtr t Qtr Assets Liabilities and Equities Qtr 6,500 8,700 9,5006,400 9,200 Cash 500 Accounts payable Accounts Receivable Raw materials inventory Finished goods inventory Property, Plant and equipment, net $ 20,000 Notes Payable S 2,142 Interest payable S 5,808 Dividend payable $1,000 $10,000 100,000 Common Stock Retained earnings Accounts receivable, December 31, 201620,000 tal a 15% of next Raw materials Inventory: Raw materials needed per teapot tion needs Accounts payable December 31, 2016 $ 10.00 per hour $3.00 per direct labor hour $ 1,000 per quarter Noncash fixed (included in above) Selling & Admin Expenses: $ 1,500 per quarter Noncash fxed (included in above) $ 2,500 $ 1,000 14% per year 1st Quarter 1,000 Declared every December (Paid every Jan $ 15,000 in June (2'e Quarte Equipment purchase Quarter

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Auditors Guide To IT Auditing Software Demo

Authors: Richard E. Cascarino

2nd Edition

1118147618, 978-1118147610

More Books

Students also viewed these Accounting questions

Question

Identify the two factors in the two-factor theory of emotion.

Answered: 1 week ago