Question
In: Accounting
Spring 2020 Spreadsheet Project Name: Lexie's Wool Sweaters Projected Budgeting Data Sales & Collections October 2020...
| Spring 2020 Spreadsheet Project | ||||||
| Name: | ||||||
| Lexie's Wool Sweaters | ||||||
| Projected Budgeting Data | ||||||
| Sales & Collections | ||||||
|
October 2020 |
November 2020 |
December 2020 |
January 2021 |
February 2021 |
||
| Sales in Units (Sweaters) | 30,000 | 34,000 | 55,000 | 47,000 | 32,000 | |
| Selling Price per Sweater | $ 100.00 | |||||
| Cash Sales Collected in the Month of Sale | 30% | |||||
| Credit Sales Collected in the Month of Sale | 50% | |||||
| Credit Sales Collected in the Following Month | 20% | |||||
| Ending FG Inventory Requirement | 3% | of next months unit sweater sales | ||||
| Ending FG Inventory, September 30 , 2020 | 1,500 | sweaters | ||||
| Product Input Expenses | ||||||
| Direct Materials | ||||||
| Ending RM Inventory, September 30, 2020 | 8265.60 | yards | ||||
| Yards of Wool Required per Sweater | 4 | yards per sweater | ||||
| Raw Materials Cost per Yard of Wool | $ 3.50 | per yard | ||||
| Ending RM Inventory Requirement | 7% | of next months sweater production needs | ||||
| Wool Purchases Paid for in the Month of Purchase | 85% | |||||
| Wool Purchases Paid for in the Month following the Purchase | 15% | |||||
| Direct Labor | ||||||
| Number of Workers Required for the Making of Each Sweater | 5 | workers | ||||
| Labor Hours Required per Worker per Unit of FG (Sweater) | 0.5 | hours | ||||
| Labor Cost per Hour | $ 15.00 | per hour | ||||
| Manufacturing Overhead | ||||||
| Variable Manufacturing Overhead | $ 11.75 | per sweater | ||||
| Fixed Manufacturing Overhead | $ 30,200.00 | per month (Oct.) | $ 30,750.00 | per month (Nov. & beyond) | ||
| Noncash Fixed Manufacturing Overhead (included in above) | $ 10,250.00 | per month (Oct.) | $ 15,750.00 | per month (Nov. & beyond) | ||
| Selling & Administrative Expenses | ||||||
| Variable S&A | $ 7.37 | per unit sold | ||||
| Fixed S&A | $ 23,900.00 | per month | ||||
| Noncash Fixed S&A (included in above) | $ (10,750.00) | per month | ||||
| Factory Update & Cash Flow | ||||||
| Factory Update (PP&E) | $ 400,500.00 | paid on October 31, 2020 | ||||
| Principle Borrowed on October 1, 2020 | $ 300,000.00 | |||||
| Principle Repaid on November 30, 2020 | $ 300,000.00 | |||||
| Interest Payment on Borrowings in October & November | $ 9,000.00 |
per month (paid in following month) |
||||
Create a Schedule of Cash Collections in Excel using formulas only
Solutions
Expert Solution
As requested, only cash collections schedule is prepared using excel formulas:
Excel formulas:
ekkarill92 answered 2 years agoRelated Solutions
Spring 2020 Spreadsheet Project Name: Lexie's Wool Sweaters Projected Budgeting Data Sales & Collections October 2020...
Spring 2020 Spreadsheet Project
Name:
Lexie's Wool Sweaters
Projected Budgeting Data
Sales & Collections
October
2020
November
2020
December
2020
January
2021
February
2021
Sales in Units (Sweaters)
30,000
34,000
55,000
47,000
32,000
Selling Price per Sweater
$ 100.00
Cash Sales Collected in the Month of Sale
30%
Credit Sales Collected in the Month of Sale
50%
Credit Sales Collected in the Following Month
20%
Ending FG Inventory Requirement
3%
of next months unit sweater sales
Ending FG Inventory, September...
Capital Budgeting Analysis A firm is planning a new project that is projected to yield cash...
Capital Budgeting Analysis
A firm is planning a new project that is projected to yield cash
flows of -$515,000 in Year 1, $586,000 per year in Years 2 through
3, and $678,000 in Years 4 through 6, and $728,000 in Years 7
through 10. This investment will cost the company $2,780,000 today
(initial outlay). We assume that the firm's cost of capital is
9.65%.
(1) Draw a timeline to show the cash
flows of the project.
(2) Compute payback period,...
% - XYZ Company provides you with the following data projected for 2018: Projected sales are...
% - XYZ Company provides you with the following data projected
for 2018:
Projected sales are 30,000 units. Inventory at Dec 31,2017 was
4,000 manufactured units. The desired ending for Dec 31, 2018 is
8,000 manufactured units.
a) 5% - Provide a production budget for the number of units that
need to be produced.
b) 6% - Assume for each unit manufactured, four pounds of raw
materials are needed. Assume the desired ending inventory became
10,000 manufactured units. What effect...
Below is the data for the spreadsheet for a project selection model: Project NPV (return) cash...
Below is the data for the spreadsheet for a project selection
model:
Project
NPV (return) cash investment
personnel requirements
1
75,000
30,000
10
2
50,000
45,000
5
3
25,000
75,000
10
4
85,000
30,000
4
5
50,000
45,000
8
6
25,000
75,000
7
7
95,000
45,000
6
8
25,000
60,000
1
9
75,000
30,000
2
10
65,000
45,000
2
You have $250,000 to invest and 36 people. Maximize NPV by
approving/rejecting projects.
Multiple Choice #8: How many projects of the...
Process Cost Excel Project Create a new Excel spreadsheet and name it “Last name_PC”. You project...
Process Cost Excel Project
Create a new Excel spreadsheet and name it “Last name_PC”. You
project is to create a model for a production cost report using the
weighted average method for the month of May. Following
good Excel design techniques, you should have an input area in
which you put the department information for the month, and an
output area that calculates the production cost report. As always,
you should have only formulas or references in your output area....
Process Cost Excel Project Create a new Excel spreadsheet and name it “Last name_PC”. You project...
Process Cost Excel Project
Create a new Excel spreadsheet and name it “Last name_PC”. You
project is to create a model for a production cost report using the
weighted average method for the month of May. Following
good Excel design techniques, you should have an input area in
which you put the department information for the month, and an
output area that calculates the production cost report. As always,
you should have only formulas or references in your output area....
Process Cost Excel Project Create a new Excel spreadsheet and name it “Last name_PC”. You project...
Process Cost Excel Project
Create a new Excel spreadsheet and name it “Last name_PC”. You
project is to create a model for a production cost report using the
weighted average method for the month of May. Following
good Excel design techniques, you should have an input area in
which you put the department information for the month, and an
output area that calculates the production cost report. As always,
you should have only formulas or references in your output area....
Patricia’s housekeeping company has the following data projected for June. Beginning cash balance $24,500 Cash collections...
Patricia’s housekeeping company has the following data projected
for June. Beginning cash balance $24,500 Cash collections from
customers 36,200 Cash disbursements 54,300 Patricia wants to
maintain a cash balance of $10,000 or more. Any amount below this
can be borrowed from a local bank as needed in increments of
$1,000. All borrowings are made at month end. In Patricia's cash
budget for June, how much money will Patricia need to borrow at
month end? A. $6,000 B. $5,000 C. $7,000...
Graham Potato Company has projected sales of $13,200 in September, $16,000 in October, $23,200 in November,...
Graham Potato Company has projected sales of $13,200 in
September, $16,000 in October, $23,200 in November, and $19,200 in
December. Of the company's sales, 25 percent are paid for by cash
and 75 percent are sold on credit. Experience shows that 40 percent
of accounts receivable are paid in the month after the sale, while
the remaining 60 percent are paid two months after. Determine
collections for November and December.
Also assume Graham’s cash payments for November and December...
Graham Potato Company has projected sales of $20,400 in September, $22,000 in October, $30,400 in November,...
Graham Potato Company has projected sales of $20,400 in
September, $22,000 in October, $30,400 in November, and $26,400 in
December. Of the company's sales, 30 percent are paid for by cash
and 70 percent are sold on credit. Experience shows that 40 percent
of accounts receivable are paid in the month after the sale, while
the remaining 60 percent are paid two months after. Determine
collections for November and December.
Also assume Graham’s cash payments for November and December...
ADVERTISEMENT
ADVERTISEMENT
Latest Questions
- What are the options that might be considered in relation to introducing flexible work practices? What...
- Question 2 : Aggregate Scores: Write a program that opens 2 files, quiz.txt, and hw.txt. The...
- Define the term symbiosis specifically as it applies in a biological context. Compare and contrast parasitism,...
- A research team conducted a study showing that approximately 20% of all businessmen who wear ties...
- Research the internet for companies that have executed data science projects. Pick one company and describe...
- Explain two theories or frameworks for understanding how Western Europe emerged out of 1,000 years of...
- Two cannons are mounted as shown in the drawing and rigged to fire simultaneously. They are...
ADVERTISEMENT