Need help

In Chapter 6

,

we

a

re learning

how to

prepare

a master budget for a manufacturing corporation.

For this exercise, you will

create a master budget in Excel.

The exercise will reinforce your

understanding of the elements of the master budget and will give you the opportunity to

improve your proficiency in setting up worksheets in Excel. Excel is a handy tool for budgeting

since it allows you to make changes to

revenue and cost inputs and immediately see how those

changes impact the overall budget (sensitivity analysis).

You will be able to use your knowledge

of financial statements from Intermediate I to complete budgeted (pro forma)

financial

statements for the company

.

You are preparing the 2020 master budget for

Gadget

s, Inc.

Read the background information

and review the projections for revenues and costs provided in this handout. Using this data,

set

up and

prepare the master budget in Excel.

As

part of the budgeting process, you will be asked

to eva

luate the cash needs of this new company and determine the necessity to

borrow against

a line of credit to meet operating cash requirements.

When you have completed the or

iginal budget, there are three exercises to co

mplete

that

require

using sensitivity analysis to determine how changes in budgetary assumptions will

affect projections of net income

and cash flows.

You have approximately 4 weeks to complete this assignment. To help you manage your time,

there are two interim

milestone due dates before the final submission is due.

Even with the

interim deadlines, I recommend that you schedule your time carefully and allow enough time

early to dig into the project and understand the requirements. The interim due dates are:

T

he Operational Budget is due on

O

ctober 27

.

The Financial Budget is due on

November

3

.

The

f

inal

Master Budget

assignment is due on

November

1

7

.

I am available to answer any questions you have throughout the assignment. I am happy to

review and check

your work as frequently as you need and I will provide feedback on how to

correct errors.

This is an individual assignment.

You will have opportunities

to discuss the assignment in class,

but your final submissions must be your own original work.

O

n the last page of this handout is the detailed rubric I will use to assign points earned for this

assignment.

2

GADGET

S, INC.

Gadget

s, Inc.

incorporated and will begin operations on

January

1

,

20

20

. Its primary business is

the manufacture and sale of

gadget

s. Because cash resources are limited,

the company

anticipates the need to have access to capital during the first year of operations and seeks to

establish a line of credit with a local bank.

The bank requires a complete operating and cash

budget and pro

-

forma financial statements for 20

20

as part of the loan application.

The following

information and

data are to be used in preparing the budget.

1)

Gadget

s, Inc. is a

closely

-

held

c

orporation

. The original owners

will invest

an initial

$150,000

(

assume the initial cash funding occurs o

n January 1, 20

20

)

to establish the

corporation and are the only shareholders of the company.

2)

Production equipment

totaling $

36

,000

will be purchased on January 2,

20

20

and put into

immediate use. The equipment has an

expected useful

life of five years, with no salvage

value.

It will be depreciated using s

traight

-

l

ine depreciation

method.

3)

The sole product is the Standard

Gadget

.

E

ach

unit

requires four

pounds of

raw

material

.

The cost of the raw material is

$0.50 per pound.

It takes six minutes of direct labor to

produce one

gadget

.

Direct labor employees are paid $1

8

.00 per hour.

4)

Monthly fixed and variable o

verhead and selling & administrative (S&A) expenses are

estimated below.

Fixed

m

anufacturing

o

verhead is allocated on the basis of

d

irect

l

abor

hours.

VARIABLE

FIXED

Manufacturing

o

verhead (including depreciation)

30% of

direct lab

or costs

$20

,000

Selling & Ad

ministrative Expenses

$0.30

per unit

sold

$ 6,000

5)

Gadget

s

will sell for $

8

.0

0 each. January sales are expected to be 8

,

000 units. Demand is

expected to increase by 400 units per month until a level of

12,000 units per month is

reached.

6)

Gadget

s, Inc.s i

nventory policy

establishes the following required monthly ending inventory

levels

:

Finished Goods:

Maintain an ending

inventory equal to one

-

half of the following

month's expected sales.

Raw

Materials:

Maintain an end

ing inventory quantity equal to three

-

fourths of the

materials needed for the next month's production.

Work

-

in

-

Process:

Assume the month

-

end inventory is zero.

3

7)

All sales

are made

to

established credit customers with credit terms of net 90 days. No

sales discounts are offered for early payment.

Twen

ty percent (2

0%) of payments for credit

sales are expected to be received in the

first month subsequent to the mo

nth of sale.

Another s

ix

ty percent (6

0%) of payments are expected to be received

in the second month

after the sale.

The remaining twenty percent (20%) of payments are expected to be

received

in the third month subsequent to the sale.

(

As an example, i

f January sales are

$10,000, the company expects to receive payments of $2,000, $6,000 and $2,000 in

February, March and April, respectively.)

8)

Gadget

s, Inc. has a credit arrangement with its raw materials vendor and purchases all

materials on a

ccount with a basic invoice term of net 60 days.

The company receives NO

cash discounts for

early

payment of invoices.

Gadgets pays 60% of its raw materials

purchases in

the month of purchase. The remaining 40% is

paid in the

first month

subs

equent to the purchase.

9)

A

ll other operating expenses will be paid in the month incurred.

10)

The income tax rate is

24

%. Estimated tax payments are made to the US Treasury on the

last day of each quarter. Each quarterly payment

is

$5,000. Any balance due at year

-

end

is

recorded

as a liability on the balance sheet. If tax

es have been overpaid, the estimated

refund is recorded as

a receivable on the balance sheet.

11)

The company requires a minimum ending monthly cash balance of $2,00

0 which must be

reflected in the cash budget.

12)

The proposed funding agreement with the bank is a line of credit of up to $100,000,

effective January 1,

20

20

. The annual interest rate on the line of credit is

4

%, payable on

the last day of e

ach month on the outstanding loan balance.

In any month

that

the cash budget indicates

ending

monthly cash balance less than the

required minimum cash

balance;

the company will borrow against the line of credit

an

amount sufficient to bring the ending c

ash balance up to the required $2,000 minimum. If

the ending monthly cash balance is greater than the $2,000 required minimum, the excess

cash will be used to repay the outstanding balance.

Borrowings and repayments

throughout the year will be budgeted

i

n increments of $100

. However

,

the company will

budget for a final December payment that

will reduce the l

ine of credit balance t

o zero.

Borrowing and repayments will occur on the last day of the month (not affect

ing the month

end calculation of interes

t

payment due that day.)

4

ASSIGNMENT DETAILS

:

Create the

20

20

monthly master budgets for

Gadget

s, Inc. in Microsoft Excel.

The elements of

the master budget include both the operating budget and the financial budget:

Operating budget

all schedules by month and in total for the year

Sales

Revenue budget

Production budget

Ending inventory budget

Direct materials budget

Direct labor costs budget

Manufacturing

o

verhead

b

udget

Cost of goods sold budget

Selling and

a

dministrative

e

xpenses

b

udget

Pro

-

forma income

statement for the year ended December 31,

20

20

Financial budget

Capital expenditures, by month and in total for the year.

Cash budget

, by month and in total for the year.

Pro

-

forma balance sheet at December 31,

20

20

Pro

-

forma statement of owners equity for the year ended December 31, 2020

Pro

-

forma statement of cash flows for the year ended December 31,

20

20

1.

Prepare individual monthly budgets but monthly

financial statements are not

required.

2.

I created a

workbook

template as a suggested starting point. You may modify the

workbook in any way to accommodate your assignment

(or you can create your own

unique worksheet)

.

Refer to the

handout on working with spreadsheets for additional

information on s

preadsheet desig

n and

functions

you may find useful.

Label the budget

carefully for units and dollars. U

se the dollar or comma format

with two decimal places

for dollars ($1,500.00 or 1,500.00) and use no

decimals for units (9,000).

3.

The workbook template has columns fo

r January and February 20

2

1

only

to

accommodate the calculations required for

December

20

20

. Do not complete

budgeting for these months. Use the 20

21

columns

only as a source of

data

for sales,

inventory and production quantities

.

4.

The cash budget must reflect the

actual amounts of

cash

overhead costs.

5.

The cash budget includes a

financing

section to accommodate the calculation of planned

borrowing transactions.

a)

Borrowing

:

In calculating

the

month

-

end

amount to borrow,

it must be

an

amount sufficient to cover any

cash defici

t plus any amount due as interest for

that month. Every month must end with a cash balance above the

$2

,

000

minimum.

b)

Repayments

:

Loan repayments are budgeted to be paid

only if (1) there is a cash

surplus at

the end of the month, (2) the amount of the cash surplus exceeds any

interest due that month, and (3) there is any balance due on the loan. The cash

surplus must first be used to pay interest due, and then any remaining surplus

can be used for repayment o

f the loan principal.

5

c)

Loan Balance

:

Include a line in the cash budget to

calculate the running principal

balance of the line of credit.

The loan balance at the end of any month is equal

to the loan balance at the end of the previous month, plus any n

ew borrowing in

the current month, or minus any repayment in the current month.

d)

Interest Payments

: All borrowing and repayments take place on the last day of

the month. Therefore, any interest to be paid in the current month is based on

the loan balanc

e at the end of the

previous

month.

e)

Ending Cash Balance

: The ending cash balance for each month is total cash

receipts

, minus total cash expenditures, plus the amount of

net financing.